MySQL行级锁案例详解

前期数据准备：一张数据表（test 表）

test表

test表原始数据

为了演示不同进程对同一个数据库的并发操作，此处开了两个cmd会话窗口（黑色cmd窗口为session1，白色cmd窗口为session2）来模拟不同线程。

说明：在 MySQL 命令行的默认设置下，进行DML操作时，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，来禁止使用当前会话的自动提交。

一、行锁演示

① 开启手动提交


② session1更新但不提交

session2更新同一条记录被阻塞

session2更新其他记录不会阻塞

③ session1提交更新

session2解除阻塞

④ 此时，我们再在session1中修改 id=4 这条记录，发现session1阻塞了。

别忘了，session2完成update操作还没提交呢！相当于现在是session2锁住了这条记录。

session2提交更新

session1解除阻塞

⑤ 但是，当我们在session2中查test表的数据的时候，却神奇地发现 id=4 这条记录的 money 还是等于 ‘401’

其实，又是因为刚刚session1的第二次修改还没完成提交呢哈哈哈~

session1提交更新

这时，session1和session2两个会话查出来 id=4 这条记录的数据就是 money= ‘402’ 了


这里说明一下，有时session2查出来的money还是等于’401’，这是因为MySQL有缓存的原因，需要在select之后再手动commit一下，或者重新开一个会话。

二、索引失效使行锁变表锁

test表原始数据

① 在session1中将 money=‘100’ 这条记录的 id 修改为 111

在session2中将 money=‘300’ 这条记录的 id 修改为 333

会发现居然堵塞了，不是修改的不同记录吗？为什么还会阻塞呢？

原因：test表没有为字段money建立索引，因此此条SQL语句没有用到索引（索引失效），行锁升级为表锁，把test表整张表都锁住了，因此session2更新test表会发生阻塞。

② session1完成提交

session2解除阻塞

三、间隙锁

什么是间隙锁？

当我们使用范围条件而不是相等条件检索数据，并请求共享锁或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做 间隙（GAP)，InnoDB也会对这个“间隙”加锁，这种锁机制就是间隙锁（Next-Key锁）。

test表原始数据（可以看到，原始数据是没有 id=2 这条记录的）

① 在session1中将 id 在 (1, 6) 区间的记录的 money 修改为原来的10倍（虽然字段 money 是varchar类型，但是MySQL底层会进行自动类型转换）

② 在session2中插入一条 id=2 的记录，发现阻塞

③ session1完成提交

session2解除阻塞

总结：因为Query执行在过程中通过范围查找的话，MySQL会锁定整个范围内所有的索引键值，即使这个键值并不存在，造成在锁定的时候无法插入在锁定键值范围内的任何数据，在某些场景下这可能会对性能产生很大的危害。

四、行锁分析

通过检查 lnnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况

各个状态量的说明如下:

Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time：从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg：每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits：系统启动后到现在总共等待的次数

五、优化建议

• 尽可能让所有数据检索都通过索引来完成，避免索引失效造成行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围。
• 尽可能较少使用范围查找，避免间隙锁。
• 尽可能控制事务大小，减少锁定资源量和时间长度。
• 尽可能降低事务隔离级别。