MySQL 事务锁

MySQL 锁的基本类型

读写锁

• 读锁也叫共享锁(S)，相互不堵塞（其他事务可以读，但不可以写）
• 写锁也叫排它锁(X)，一个写锁会阻塞其他的读锁和写锁（其他事务不能读，不能写）

锁粒度

表锁

开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
一次会将整张表锁定，表级锁定引擎主要是 MyISAM、Memory、CSV 等非事务性引擎

应用场景

第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

第二种情况：多表查询。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

查询锁定夺

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺：

MySQL [sakila]> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 100   |
| Table_locks_waited    | 0     |
+-----------------------+-------+

如果 table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况。

行锁

开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。
主要引擎 Innodb、NDB Cluster 存储引擎，Innodb默认采用行锁
共享锁：select * from tableName where ... + lock in share more
排他锁：select * from tableName where ... + for update

分析行锁定

查看数据库存储的引擎类型 SHOW ENGINES
通过检查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          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+

innodb_row_lock_current_waits: 当前正在等待锁定的数量
innodb_row_lock_time: 从系统启动到现在锁定总时间长度；非常重要的参数，
innodb_row_lock_time_avg: 每次等待所花平均时间；非常重要的参数，
innodb_row_lock_time_max: 从系统启动到现在等待最常的一次所花的时间；
innodb_row_lock_waits: 系统启动后到现在总共等待的次数；非常重要的参数。直接决定优化的方向和策略。

行锁的优化

尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。  
尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。  
尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。  
尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

页锁

开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般 页级锁定主要是BerkeleyDB 存储引擎。

查看加锁情况

show open tables; 1表示加锁，0表示未加锁。

mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock     | myisam_lock |      1 |           0 |
+----------+-------------+--------+-------------+

总结

1. InnoDB 支持表锁和行锁，使用索引作为检索条件修改数据时采用行锁，否则采用表锁。
2. InnoDB 自动给修改操作加锁，给查询操作不自动加锁
3. 行锁可能因为未使用索引而升级为表锁，所以除了检查索引是否创建的同时，也需要通过explain执行计划查询索引是否被实际使用。
4. 行锁相对于表锁来说，优势在于高并发场景下表现更突出，毕竟锁的粒度小。
5. 当表的大部分数据需要被修改，或者是多表复杂关联查询时，建议使用表锁优于行锁。
6. 为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库的并发处理能力和性能。

参考地址：
https://zhuanlan.zhihu.com/p/...
https://segmentfault.com/a/11...
https://www.cnblogs.com/jpfss...
https://segmentfault.com/a/11...
https://segmentfault.com/a/11...

---