MySQL的锁机制

锁的概念

锁（Locking）是数据库在并发访问时保证数据一致性和完整性的主要机制。MySQL中不同的存储引擎使用的加锁方式不同，数据库的锁可以自动获取，也可以手动添加

分类

MySQL中的锁按照锁粒度分为两类：

• 锁定整个表的表级锁（table-level locking），MyISAM存储引擎默认
• 锁定数据行的行级锁（row-level locking），InnoDB存储引擎默认

锁的特性

表级锁的特性：开销小、加锁块，锁粒度大，发生锁冲突的几率高，并发支持低下

行级锁的特性：开销大、加锁慢，锁粒度较小，发生锁冲突的几率低，并发支持高。

对行锁的解释

InnoDB存储引擎同时支持表级锁和行级锁，默认使用行级锁

行锁锁定的是索引，而不是整条数据行。① 当SQL语句操作聚簇索引的时候，MySQL会锁定该主键索引；② 当操作的记录还包含非聚簇索引的时候，先锁定非聚簇索引，再回表锁定聚簇索引

当①、②两种情况交叉发生的时候，就造成了死锁。

select ... where ... for update 操作一个只有聚簇索引的表时，会锁表，可以通过给where条件的字段加索引解决

select ... where ... for update 操作包含聚簇索引和非聚簇索引的时候，可能会出现死锁

适用场景

表级锁适用于并发较低、以查询为主的应用，例如中小型的网站；MyISAM 和 MEMORY 存储引擎采用表级锁。

行级锁适用于按索引条件高并发更新少量不同数据，同时又有并发查询的应用，例如 OLTP 系统；InnoDB 和 NDB 存储引擎实现了行级锁。

共享锁与排他锁

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：允许获得该锁的事务读取数据行（读锁），同时允许其他事务获得该数据行上的共享锁，并且阻止其他事务获得数据行上的排他锁。
• 排他锁（X）：允许获得该锁的事务更新或删除数据行（写锁），同时阻止其他事务取得该数据行上的共享锁和排他锁。

锁的兼容性如下：

锁类型	共享锁 S	排他锁 X	意向共享锁 IS	意向排他锁 IX
共享锁 S	兼容	冲突	兼容	冲突
排他锁 X	冲突	冲突	冲突	冲突
意向共享锁 IS	兼容	冲突	兼容	兼容
意向排他锁 IX	冲突	冲突	兼容	兼容

 共享锁和共享锁可以兼容，排他锁和其它锁都不兼容

通过示例说明锁的兼容性

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

建立测试表

create table test1 (id int auto_increment primary key,
num1 int unique key,
num2 int,
num3 int,
index idx_n2(num2)
);

插入测试数据

insert into test1(num1,num2,num3) values(1,1,1),(2,3,4),(3,9,9);

 

mysql> select * from test1;
+----+------+------+------+
| id | num1 | num2 | num3 |
+----+------+------+------+
|  1 |    1 |    1 |    1 |
|  2 |    2 |    3 |    4 |
|  3 |    3 |    9 |    9 |
+----+------+------+------+
3 rows in set (0.00 sec)

开启两个终端，在A终端执行select ... for share语句获得id=1数据行的共享锁，（MySQL8.0之前的版本获取共享锁，需要使用select ... for share mode;）

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test1 where id = 1 for share;
+----+------+------+------+
| id | num1 | num2 | num3 |
+----+------+------+------+
|  1 |    1 |    1 |    1 |
+----+------+------+------+
1 row in set (0.00 sec)

打开B终端，可以获取A终端该数据行的共享锁

mysql> select * from test1 where id = 1 for share;
+----+------+------+------+
| id | num1 | num2 | num3 |
+----+------+------+------+
|  1 |    1 |    1 |    1 |
+----+------+------+------+
1 row in set (0.00 sec)

在B终端获取该数据行的排它锁，该语句会被锁定到超时，证明了共享锁和排它锁不兼容

mysql> select * from test1 where id = 1 for update;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

意向锁

MySQL有行级锁和表级锁（LOCK TABLES ... WRITE，排他锁），当行级锁和表级锁同时存在的时候，可能会引发冲突。

当A事务获取了一行数据的行级锁的时候，事务B申请写锁。如果B成功获取锁，B事务可以修改表中的任意行数据，但是A持有的行锁不允许修改被锁定的数据行，这就是锁冲突。

事务B是否能获取表级锁需要通过以下条件判断：

先检查该表上是否已经有其他事务的表级锁，再检查每一行数据是否有行锁存在。这样就需要遍历整个表，这样效率低，InnoDB存储引擎引入意向锁（Intention Lock）

补充

锁冲突和死锁不是一个概念，锁冲突会按顺序执行，死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象

意向锁是表级锁，由InnoDB存储引擎自行管理。

意向锁的分类

• 意向共享锁（IS）：事务在给数据行加行级共享锁之前，必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务在给数据行加行级排他锁之前，必须先取得该表的 IX 锁。

引入意向锁的目的是为了是函索和表锁共存。意向锁和表锁之间只有共享锁兼容，意向锁和意向锁之间都可以兼容。意向锁的主要作用是表明某个事务正在或者即将锁定表中的数据行。

通过示例说明意向锁的兼容性

意向锁和其他锁的兼容性

现在A终端给表中 id = 1 的记录加上意向排它锁

mysql> select * from test1 where id = 1 for update;
+----+------+------+------+
| id | num1 | num2 | num3 |
+----+------+------+------+
|  1 |    1 |    1 |    1 |
+----+------+------+------+
1 row in set (0.00 sec)

在B终端给表加上共享锁，由于意向排他锁和共享锁冲突，所以会有锁等待

mysql> lock tables test1 read;

在A终端提交或者回滚事务

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

这时B终端自动获得共享锁

-- B终端获得了共享锁
Query OK, 0 rows affected (13.54 sec)

这时查看锁情况

mysql> show OPEN TABLES where In_use >0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | test1 |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

释放锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show OPEN TABLES where In_use >0;
Empty set (0.00 sec)

意向排他锁之间的兼容性

终端A对id =1 的行记录加上了排它锁和意向排它锁

mysql> select * from test1 where id = 1 for update;
+----+------+------+------+
| id | num1 | num2 | num3 |
+----+------+------+------+
|  1 |    1 |    1 |    1 |
+----+------+------+------+
1 row in set (0.01 sec)

终端B对 id =2 的行记录加上排它锁和意向排他锁

mysql>  select * from test1 where id = 2 for update;
+----+------+------+------+
| id | num1 | num2 | num3 |
+----+------+------+------+
|  2 |    2 |    3 |    4 |
+----+------+------+------+
1 row in set (0.00 sec)

这时表test1上就有了表级别意向排它锁和数据行上的行级排它锁，锁粒度控制更精细