MySQL的LockTables和UnlockTables

最近对存储过程那一佗东西,总感到有点不踏实。总感觉容易导致脏数据出现,但又苦于自己是新手,刚开始学数据库,连一个insert语句都写不好,又怎么好去提解决方法呢?

幸好逮到一个机会,有较多时间去验证存储过程的数据安全性问题。

测试例子,随意写的,只说明情况。

CREATE FUNCTION `func_set_top_score`(t_appid INT,t_user_id INT, t_score DOUBLE) RETURNS INT(11)

BEGIN

    DECLARE m_exists_score DOUBLE DEFAULT 0;

    DECLARE m_id BIGINT DEFAULT 0;

    SELECT `score` ,`id` INTO m_exists_score,m_id FROM `user_score` WHERE `appid`=t_appid;

    SLEEP(10);//增加该值,以提升脏数据的几率

    IF m_exists_score <> 0 THEN

        IF m_exists_score < t_score THEN

            UPDATE `user_score` SET `score`=t_score,’id’=user_id WHERE `appid`=t_appid;

        END IF;

    ELSE

        INSERT IGNORE INTO `user_score` (`id`, `score`, `appid `) VALUES (t_user_id, t_score,t_appid);

    END IF;    

    RETURN @errno_success;

END$$

由于多进程的抢夺CPU,会导致某APP存放的分数,不一定是最高分。

解决办法是琐表:lock table和 unlock table;

为了更好的使用该琐表功能,进行以下测试,分别创建A终端和B终端,并连接上相同的数据。

1.A终端上,执行表的读琐,并依次执行insert和update动作。

2.B终端上执行select\insert\update动作。

3.A终端上再执行unlock动作。

4.B终端的数据更新成功。

5.A终端在琐表情况下,能否访问其它表?

读琐总结:

1.所有线程或进程都可以Select该琐定的数据表,但所有线程包括琐表的线程或进程,都不能够更新数据,直到琐表的线程调用unlock tables为止。

2.在lock tables和unlock tables之间,发生的更新操作,都会被挂起且进入阻塞队列,等待解琐和完成操作。

3.在lock tables和unlock tables之间,琐表的线程,不能访问其它表,包括select\update\insert操作。

 

1.表的写琐。

1.A终端发起写琐表,它的select/update/delete/insert操作均正常

2.B终端数据

3.A终端解琐

4.B终端返回数据。

写琐总结:

1.写琐触发后,只有琐表线程可以执行select/update/insert动作,其它线程或进程的所有动作都会被挂起,直到解琐为止。

2.在lock tables和unlock tables之间,琐表的线程,不能访问其它表,包括select\update\insert操作,这点与读琐一致。

 

如果我们要求某些表执行读操作,某些表执行写操作呢?答案是可以的,但写操作只能对写琐的表进行。

 

读写琐是否为递归琐?

答案:不是,连续两次调用lock table的效果是和lock table->unlock table->lock table的效果是一样的,如下。

 

琐表和解琐之间,为什么不能操作其它表?

个人猜测,主要是防止死琐问题,其次是避免操作其它表所造成的无谓性能浪费。

 

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注