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...

阅读 787
79 声望
1 粉丝
0 条评论
79 声望
1 粉丝
文章目录
宣传栏