MySQL数据库排他锁与共享锁

有运维或运维开发方面的需求,可以联系博主QQ 452336092或Email:admin#centos.bz(收费)

导语:“简单印象”头条号每天将不定时发布一篇文章,文章内容大多为原创性技术相关或技术人的另一面生活,欢迎大家收藏文章或点击右上角的“关注”,支持我的头条号,也可以直接访问我的个人博客(http://www.thanks.live)查看最新撰写的文章。同时,也非常高兴能看到大家在文章底部评论区讨论、指正文章的不当之处,分享中可以寻找到技术人独有的快乐~~~
我们先针对mysql数据库的排他锁、共享锁给出下面一个结论:

结论:

(1)共享锁【S锁】:又称读锁,若事务T是最早对数据对象A加上S锁的事务,则事务T可以读A也能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。 共享锁使用方式:SELECT … LOCK IN SHARE MODE;
(2)排他锁【X锁】:又称写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再修改A,但可以读取A。排他锁使用方式:SELECT … FOR UPDATE;

验证结论:

目标表(表名:test)结构及初始数据
上述,我们创建了一个测试表,表名为test,其中,id、a、b、c为该表的字段,id为表自增字段。为了防止数据库自动提交,特别强调,需要设置 set autocommit=0。同时,为了模拟两个session同时操作同一数据集,我们需要开启两个操作窗口进行整个试验。

两个session会话,设置autocommit =0
上述,我们创建了两个窗口,1.mysql 和 2.mysql。下面验证共享锁部分的结论:
(1)在1.mysql中执行:select * from test where id=1 lock in share mode;
(2)在2.mysql中执行:select * from test where id=1 for update; 此时执行失败
(3)在2.mysql中执行:select * from test where id=1 lock in share mode; 执行成功

(1)(2)(3)
以上结果表明:若事务T是最早对数据对象A加上S锁的事务,其他事务只能再对A加S锁,而不能加X锁
(4)在1.mysql中执行,select * from test where id=1; 执行成功
(5)在2.mysql中执行,select * from test where id=1; 执行成功
(6)在1.mysql中执行,update test set a=’字段a-行1-1.mysql修改’ where id=1;执行成功
(7)在2.mysql中执行,update test set a=’字段a-行1-2.mysql修改’ where id=1;执行失败,产生死锁
(8)在1.mysql中执行,commit;提交事务T成功,字段a的值修改为:字段a-行1-1.mysql修改

(4)(5)(6)(7)(8)
(9)在2.mysql中执行,update test set a=’字段a-行1-2.mysql修改’ where id=1;执行成功
(10)在2.mysql中执行,commit; 提交事务成功,字段a的值修改为:字段a-行1-2.mysql修改

(9)(10)
以上结果表明:若事务T是最早对数据对象A加上S锁的事务,其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。如果其他事务同时对数据添加S锁,并写与事务T相同的数据集,很可能会导致死锁发生。
关于排他锁的结论部分验证,读者可以按相同验证思维得证,这里就不再阐述。

特别提醒:

(1)因为排他锁、共享锁属于行级锁,所以,本文基于MySQL中的InnoDB引擎
(2)在验证的过程中,需设置 set autocommit =0 关闭自动提交
(3)只有执行了commit或rollback后,才认为一个事务结束
(4)假设id为主键,则:(lock in share mode同下)

例1: (明确指定主键,并且有此行记录,row lock)

SELECT * FROM test WHERE id=1 FOR UPDATE;
SELECT * FROM test WHERE id=1 and a=’字段a-行1′ FOR UPDATE;

例2: (明确指定主键,若查无此行记录,无lock)

SELECT * FROM test WHERE id=’-1′ FOR UPDATE;

例3: (无主键,table lock)

SELECT * FROM test WHERE a=’test’ FOR UPDATE;

例4: (主键不明确,table lock)

SELECT * FROM test WHERE id<>2 FOR UPDATE;

例5: (主键不明确,table lock)

SELECT * FROM test WHERE id LIKE ‘%3%’ FOR UPDATE;

原文出处:phpyuan -> https://www.phpyuan.com/233033.html

打赏

如果此文对你有所帮助,请随意打赏鼓励作者^_^