MySQL数据库排他锁与共享锁（innodb共享锁和排他锁）

导语：“简单印象”头条号每天将不定时发布一篇文章，文章内容大多为原创性技术相关或技术人的另一面生活，欢迎大家收藏文章或点击右上角的“关注”，支持我的头条号，也可以直接访问我的个人博客（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;