聊聊MySQL数据库排他锁与共享锁

我们先针对mysql数据库的排他锁、共享锁给出下面一个结论：

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

• 排他锁【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修改

  （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）行锁是针对索引（主键索引、唯一索引或普通索引）加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

（5）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。

（6）innodb自动使用间隙锁的条件：必须在RR级别下；检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

（7）假设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: (明确指定索引，若查无此行记录，Next-Key lock，间隙锁)
  

          SELECT * FROM test WHERE id='100' 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;

▼

往期精彩回顾

▼

基于Window+Vagrant+VirtualBox搭建开发环境

这可能是你在使用vagrant+virtualBox过程中遇到的一个巨坑

谈谈互联网后端基础设施

·END·

聊聊架构

青春有限·艺无止境

微信号：ArchNote

更多精彩，点击下方“阅读原文”查看。