mysql 事务涉及锁吗_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 |

+----------+-------------+--------+-------------+

总结

InnoDB 支持表锁和行锁，使用索引作为检索条件修改数据时采用行锁，否则采用表锁。

InnoDB 自动给修改操作加锁，给查询操作不自动加锁

行锁可能因为未使用索引而升级为表锁，所以除了检查索引是否创建的同时，也需要通过explain执行计划查询索引是否被实际使用。

行锁相对于表锁来说，优势在于高并发场景下表现更突出，毕竟锁的粒度小。

当表的大部分数据需要被修改，或者是多表复杂关联查询时，建议使用表锁优于行锁。

为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库的并发处理能力和性能。