MySQL 行锁、间隙锁分析

行锁

特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度最高。

以dept表为例 演示行锁。

首先把mysql自动提交事务给关了。

set autocommit=0;

更新同一行数据

更新不同行数据

当前会话读该行数据，不影响其他会话修改该行数据

无索引或索引失效 行锁升级为表锁

左边的更新语句未使用索引，扫描了全表随后定位到某一行来更新。所以致使右边的会话无法更新成功。

如何手动锁定一行

select xxx from table where xx for update;

举例：锁定id=3的行

select * from dept where id=3 for update;

对当前读操作加互斥锁，其他会话不能更新/修改或删除该行数据(因为修改操作会默认加互斥锁)，只能进行当前读操作

结论

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。

但是InnoDB行锁在我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

行锁分析

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

show status like 'innodb_row_lock%';

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 312907 |
| Innodb_row_lock_time_avg      | 31290  |
| Innodb_row_lock_time_max      | 51355  |
| Innodb_row_lock_waits         | 10     |
+-------------------------------+--------+

Innodb_row_lock_current_waits 当前正在等待锁定的数量

Innodb_row_lock_time 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg 每次等待所花平均时间

Innodb_row_lock_time_max 从系统启动到现在等待最长的一次所花的时间

Innodb_row_lock_waits 系统启动后到现在总共等待的次数

比较重要的是 Innodb_row_lock_time、Innodb_row_lock_time_avg、Innodb_row_lock_waits

尤其是当等待次数很高，每次等待时长也不小的时候，我们就要分析系统中为什么会有这么多的等待，要着手进行优化了。

优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引时行锁升级为表锁

合理设计索引，尽量缩小锁的范围，避免间隙锁；尽量控制事务大小，减少锁定资源量和时间长度。