mysql的锁定机制_MYSQL锁的机制详解

锁是计算机协调多个进程和线程并发访问某一资源的机制&＃xff0c;在数据库中除了传统的计算机资源&＃xff0c;CPU,内存,IO以外&＃xff0c;数据也是一种供许多用户共享的资源&＃xff0c;如何保证数据并发访问的一致性、有效性是所有数据库必须要解决的问题&＃xff0c;锁冲突也是影响数据库并发性能的一个重要参数。

OPTIMIZE TABLE 语句可以整理空间碎片&＃xff0c;收回因为删除记录而产生的中间空洞。

一、分类

对InnoDB锁按颗粒度进行划分&＃xff0c; 可分为表锁和行锁 :表锁&＃xff1a;

• 意向锁(Intention Lock) 。

• 自增锁(AUTO-INC Lock) 。

行锁&＃xff1a;

• 共享锁与排他锁(Shared and Exclusive Lock) 。

• 记录锁(Record Lock) 。

• 间隙锁(Gap Lock) 。

• 记录锁与间隙锁的组合(Next-Key Lock) 。

• 插入意向锁(Insert Intention Lock) 。

二、详解

1.行锁

1.1 .共享锁与排他锁

行共享锁(S) 与排他锁(X) 较好理解&＃xff0c; S锁与X锁互相冲突。

• 当读取一行记录时&＃xff0c; 为了防止别人修改&＃xff0c; 则需要添加S锁。

• 当修改一行记录时&＃xff0c; 为了防止别人同时进行修改&＃xff0c; 则需要添加X锁。

这里需要知道MySQL具有MVCC特性&＃xff0c; 所以&＃xff0c; 在通常情况下&＃xff0c; 普通的查询属于非锁定读&＃xff0c; 不会添加任何锁(即一致性读) 。 还有一种是锁定读(即当前读)&＃xff1b;

例如&＃xff1a;

• SELECT… FOR SHARE(MySQL 8.0中新增的方式&＃xff0c; 在以前版本中上锁方式为 SELECT ... LOCK IN SHARE MODE)&＃xff0c; 添加S锁&＃xff0c; 其他事务可以读但修改会被阻塞。

• SELECT… FOR UPDATE&＃xff0c; 添加X锁&＃xff0c; 其他事务修改或者执行SELECT … FOR SHARE都会被阻塞。1.2&＃xff0e; 记录锁在MySQL中记录锁都是添加在索引上的&＃xff0c; 即使表中没有索引&＃xff0c;

也会在默认创建的聚集索引上添加记录锁。

1.3. 间隙锁间隙锁的锁定范围是索引记录之间的间隙&＃xff0c; 或者第一条或最后一条索引记录之前的间隙&＃xff0c; 间隙锁是针对事务隔离级别为可重复读或以上级别的。

例如一个事务执行SELECT *FROM t WHERE c1 > 10 AND c1 <20 FOR UPDATE ;&＃xff0c; 那么当插入c1&＃61;15时就会被阻塞&＃xff1b;否则&＃xff0c; 再次查询得到的结果就与第一次不一致。

1.4&＃xff0e; 记录锁与间隙锁的组合Next-Key Lock是记录锁与间隙锁的组合&＃xff0c; 也就是索引记录本身加上之前的间隙。 间隙锁保证在REPEATABLE-READ级别下不会出现幻读现象&＃xff0c;

防止在同一个事务内得到的结果不一致。 间隙锁在执行show engine innodb 时输出结果如下( 后面会有详细解释) &＃xff1a;

1.5&＃xff0e; 插入意向锁插入意向锁是针对INSERT操作设置的一种特殊的间隙锁&＃xff0c; 主要是为了优化INSERT操作的并发能力。 这个锁表示插入的意图&＃xff0c; 即插入具有相同索引间隙的多个事务&＃xff0c;

如果插入的值不同&＃xff0c; 则不需要互相等待。假设存在值为4和7的索引记录&＃xff0c; 现在分别尝试插入值为5和6的事务&＃xff0c; 在获取插入行上的排他锁之前&＃xff0c;

会添加插入意向锁锁定4和7之间的间隙&＃xff0c; 但是不会互相阻塞&＃xff0c; 因为插入的行是不冲突的。这里需要注意的是&＃xff0c; 插入意向锁之间是不冲突的&＃xff0c; 但是插入意向锁可能和其他锁是冲突的&＃xff0c;

比如Next-Key Lock。查看行锁的竞争情况&＃xff1a;

MariaDB [(none)]> show status like &＃39;innodb_row_lock%&＃39;;&＃43;-------------------------------&＃43;-------&＃43;

| Variable_name | Value |

&＃43;-------------------------------&＃43;-------&＃43;

| Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 36922 |

| Innodb_row_lock_time_avg | 3 |

| Innodb_row_lock_time_max | 17 |

| Innodb_row_lock_waits | 11646 |

&＃43;-------------------------------&＃43;-------&＃43;

5 rows in set (0.00 sec)

如果innodb_row_lock_waits 和innodb_lock_time_avg 的值较高&＃xff0c;说明锁竞争比较严重。

可以通过如下命令查看锁情况&＃xff1a;

select * from innodb_locks \G

也可以打开InnoDB Monitors 锁冲突的情况&＃xff1a;

create table innodb_monitor(a int) engine&＃61;innodb;

show engine innodb status \G;

注意&＃xff1a;设置监视器以后&＃xff0c;show engine innodb status可以查看到详细的锁信息&＃xff0c;而且没15秒会向日志文件中记录监控内容&＃xff0c;所以会使得日子文件变得非常大&＃xff0c;要记得使用之后关闭监视器。

2.表锁(不会出现死锁)

MYSQL的MYISAM存储引擎只支持表锁&＃xff0c;这也是mysql开始几个版本中唯一支持的锁类型&＃xff0c;表锁依然是使用最为广泛的锁类型。

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

mysql> show status like &＃39;table%&＃39;;&＃43;----------------------------&＃43;-------&＃43;

| Variable_name | Value |

&＃43;----------------------------&＃43;-------&＃43;

| Table_locks_immediate | 117 |

| Table_locks_waited | 0 |

| Table_open_cache_hits | 0 |

| Table_open_cache_misses | 0 |

| Table_open_cache_overflows | 0 |

&＃43;----------------------------&＃43;-------&＃43;

如果Table_locks_waited的值比较大&＃xff0c;那说明表的锁竞争比较严重。

表锁的锁模式&＃xff1a;

mysql的表锁有两种模式&＃xff1a;表共享锁和表独占锁&＃xff1b;

1. MYISAM对表的读操作&＃xff0c;不会阻塞其他用户对同一表的读请求&＃xff0c;但是会阻塞对同一表的写请求。

2. MYISAM对表的写操作&＃xff0c;会阻塞其他用户对同一表的读写操作。

所以当一个线程获得对一个表的写锁之后&＃xff0c;只有持有锁的线程可以对表进行更新操作&＃xff0c;其他线程的读写操作都会等待&＃xff0c;知道锁被释放。

加锁&＃xff1a;

通常我们在查询或者修改记录时&＃xff0c;mysql会自动加读锁或者写锁。

手动加锁&＃xff1a;

lock tables table1 read local, table2 read local;

lock table table1 write;

unlock tables;

2.1 表锁之意向锁

意向锁在MySQL中是表级别锁&＃xff0c; 表示将来要对表添加什么类型的锁( IX/IS) 。

• SELECT… FOR SHARE&＃xff0c; 添加意向共享锁( IS) 。

• SELECT … FOR UPDATE&＃xff0c; 添加意向排他锁( IX) 。

在获取表中某行的共享锁之前&＃xff0c; 首先必须获取表的IS锁。 在获取表中某行的独占锁之前&＃xff0c; 首先必须获取表的IX锁。 意向锁和行锁之间的冲突及兼容列表如表20-1所示。

意向锁不会阻止除表锁请求(例如&＃xff0c; 执行LOCK table … WRITE语句) 之外的锁。 可以这样理解&＃xff1a; 在申请表锁(执行LOCK table语句) 时表记录不能存在锁&＃xff0c;

在没有意向锁的情况下&＃xff0c; 就需要扫描表中每一条记录&＃xff0c; 查看记录上是否存在锁&＃xff1b; 但是有了意向锁之后&＃xff0c; 只要判断表上是否存在意向锁即可&＃xff0c; 如果有意向锁存在&＃xff0c;

则说明表中某行记录已被锁定或者将要被锁定&＃xff0c; 表锁的申请语句(LOCK table) 会等待&＃xff0c; 意向锁设计提高了效率。

2.2表锁之自增锁

2&＃xff0e; 自增锁

自增锁是插入到具有AUTO_INCREMENT字段的表中的事务所采用的特殊表级锁。

在最简单的情况下&＃xff0c; 如果一个事务正在向表中插入值&＃xff0c; 则其他任何事务都必须等待插入语

句执行完成&＃xff0c; 这样才能保证后面事务插入的主键值是连续的。

innodb_autoinc_lock_mode参数用于控制自增锁的算法&＃xff0c; 通过控制自增值生成的策略

来提高并发能力

三、锁模式对应的含义

当我们使用show engine innodb语句查看锁信息时&＃xff0c; 经常会看到LOCK_MODE字段&＃xff0c;也就是锁模式&＃xff0c; 只有知道各种模式都分别代表什么意思才能更好地去分析锁等待和死锁问题。 这是一个非常重要的知识点。

• IX&＃xff1a; 代表意向排他锁。

• X&＃xff1a; 代表Next-Key Lock锁定记录本身和记录之前的间隙(X) 。

• S&＃xff1a; 代表Next-Key Lock锁定记录本身和记录之前的间隙(S) 。

• X, REC_NOT_GAP&＃xff1a; 代表只锁定记录本身(X) 。

• S, REC_NOT_GAP&＃xff1a; 代表只锁定记录本身(S) 。

• X, GAP&＃xff1a; 代表间隙锁&＃xff0c; 不锁定记录本身(X) 。

• S, GAP&＃xff1a; 代表间隙锁&＃xff0c; 不锁定记录本身(S) 。

• X, GAP,INSERT_INTENTION&＃xff1a; 代表插入意向锁。

相比其他其他数据库而言&＃xff0c;mysql的锁的机制比较简单&＃xff0c;最显著的特点是不同的存储引擎支持的锁的机制不同&＃xff0c;比如MYIASM和MEMORY存储引擎采用的是表级锁&＃xff0c;

BDB存储引擎采用的是页面锁&＃xff0c;但是也支持表级锁&＃xff0c;innoDB默认使用行级锁&＃xff0c;但是也支持表级锁。

MYSQL三种锁的特性可以大致归纳纳入下&＃xff1a;

表级锁&＃xff1a;开销小&＃xff0c;加锁快&＃xff0c;不会出现死锁&＃xff0c;锁定的粒度大&＃xff0c;但是发生锁的冲突的概率最高&＃xff0c;并行度最低。

行级锁&＃xff1a;开销大&＃xff0c;加锁慢&＃xff0c;会出现死锁&＃xff0c;锁定粒度最小&＃xff0c;但是但是发生锁的冲突的概率最低&＃xff0c;并行度最高。

页面锁&＃xff1a;开销和加锁速度介于表锁和行锁之间&＃xff0c;会出现死锁&＃xff0c;锁定粒度和并发度介于两者之间。

由此可以看出表锁更加适合已查询为主&＃xff0c;只有少量按索引条件更新数据的应用&＃xff0c;如web应用。

行锁更加适合有大量按照索引条件并发更新少量不同的数据&＃xff0c;同时又有并发查询的应用&＃xff0c;如一些在线的OLTP系统。

8.0版本开始我们可以通过如下表查看死锁信息&＃xff1a;

performance_schema.data_locks