Savage

G

0%

事务的隔离性和锁（一）

发表于 分类于
这里介绍了InnoDB中各种类型的锁，正是通过这些锁来实现事务的隔离性

事务的隔离性和锁（一）

事务并发问题

两个事务同时读写同一张表的内容数据库会如何处理呢？如果同时读似乎没什么问题，同一个数据你读我读都一样，但是如果发生写操作，数据应该变成谁的修改结果呢？

这时候就应该隔离两个事务的数据，让它们读写互相独立，相互不受影响。最简单的解决方法就是加锁，保证两个事务是串行的。

InnoDB中的锁

标准读写锁

共享数据时为了避免数据异常，通常会使用如下两种锁作为标准方案，InnoDB存储引擎实现了如下两种行级锁。

排他锁 X-Lock（eXclusive Lock）

允许事务删除或者更新一行数据。当给数据加上排他锁时，其他事务不能再次对该数据增加排他锁或者共享所锁。

共享锁S-Lock（Share Lock）

允许事务读取一行数据。当给数据加上共享锁时，其他事务可以对该数据加共享锁，不能加排他锁。

排他锁和共享锁的兼容性

排他锁和共享锁的兼容性

对行加锁

对据行加锁有以下两种语句

SELECT … LOCK IN SHARE MODE; 对数据行加共享锁 S-Lock

SELECT … FOR UPDATE; 对数据行加排他锁 X-Lock

下面发起两个事务对tagId=1 的数据行加锁，事务一加X锁成功后可以看到，事务二无论是加S锁还是X锁都会被阻塞，直到事务释放锁，有三种释放方式：手动释放、提交事务或者阻塞超时。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--- 事务一
mysql> begin
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tag where id = 1 for update;
+----+----------+-----------+---------------------+---------------------+--------+
| id | tag_name | tag_value | create_time         | update_time         | enable |
+----+----------+-----------+---------------------+---------------------+--------+
|  1 | tt       |        10 | 2024-06-20 20:33:08 | 2024-06-20 20:33:09 |      1 |
+----+----------+-----------+---------------------+---------------------+--------+
1 row in set (0.00 sec)

--- 事务二
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from tag where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  select * from tag where id = 1 LOCK IN SHARE MODE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以通过SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';来查看当前会话的事务超时时间，这里是50秒

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)

查看加锁事务

information_schema.INNODB_LOCKS;表中记录了任务加锁的信息，可以看到一个S锁，一个X锁，注意lock_type是RECORD

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM information_schema.INNODB_LOCKS;
+-------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table      | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 7748:68:3:6 | 7748        | S         | RECORD    | `fs_demo`.`tag` | PRIMARY    |         68 |         3 |        6 | 1         |
| 7747:68:3:6 | 7747        | X         | RECORD    | `fs_demo`.`tag` | PRIMARY    |         68 |         3 |        6 | 1         |
+-------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

information_schema.INNODB_LOCK_WAITS; 表中记录了等待上锁的事务

1
2
3
4
5
6
7
8

mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 7748              | 7748:68:3:6       | 7747            | 7747:68:3:6      |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

information_schema.INNODB_TRX; 此时表中的事务状态也变成了LOCK WAIT

1
2
3
4
5
6
7
8
9

mysql> SELECT trx_id,trx_state FROM information_schema.INNODB_TRX;
+--------+-----------+
| trx_id | trx_state |
+--------+-----------+
| 7748   | LOCK WAIT |
| 7747   | RUNNING   |
+--------+-----------+
2 rows in set (0.00 sec)

对表加锁

事务一先对tag表加读锁（read），事务二再对表加写锁（write）时会被阻塞

1
2
3
4
5
6
7
8
9
10
11
12
13
--- 事务一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables tag read;
Query OK, 0 rows affected (0.00 sec)

--- 事务二
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables tag write;

直到事务一释放读锁，事务二中的命令才得到响应，可以看到等待了4分钟左右

1
2
3
4
5
6
7
--- 事务一
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

--- 事务二
mysql> lock tables tag write;
Query OK, 0 rows affected (4 min 23.23 sec)

在阻塞期间可以通过SHOW PROCESSLIST; 查看当前阻塞会话的状态，在info属性中可以看到它在等待给tag表上写锁，同时注意state属性中的Waiting for table metadata lock ，稍后会说metadata lock

1
2
3
4
5
6
7
8
9
mysql> SHOW PROCESSLIST;
+----+------+-----------------+--------------------+---------+------+---------------------------------+-----------------------+
| Id | User | Host            | db                 | Command | Time | State                           | Info                  |
+----+------+-----------------+--------------------+---------+------+---------------------------------+-----------------------+
|  3 | root | localhost:60112 | information_schema | Sleep   |    8 |                                 | NULL                  |
|  6 | root | localhost:53243 | fs_demo            | Query   |    0 | starting                        | SHOW PROCESSLIST      |
|  7 | root | localhost:53244 | fs_demo            | Query   |   11 | Waiting for table metadata lock | lock tables tag write |
+----+------+-----------------+--------------------+---------+------+---------------------------------+-----------------------+
3 rows in set (0.00 sec)

元数据锁(metadata lock)

很久之前的MySQL有一著名的的BUG，在一个事务中对表A插入数据，在另一个客户端中删除表A，binlog中有如下内容：

1
2
3
4
DROP TABLE t;
BEGIN;
INSERT INTO t ... ;
COMMIT;

MySQL执行时会先删除表t，然后执行insert 会报1032 error。为了避免事务中DDL（Data Definition Languages）和DML（Data Manipulation Language）发生冲突，MySQL后续就引入了元数据锁MDL。

MySQL的DDL和DML操作都会加元数据锁，并且它是一个表级别的锁，为了减少加锁带来的负面效果，MDL也有读锁和写锁，当对一个表做增删改查的时候就加MDL读锁；当对表结构做变更时就加MDL写锁。

MDL阻塞复现

在第一个session中开启事务，然后查询一下表，然后再第二个session中对tag_name列添加索引，此时第二个session就会被阻塞，直到session1的事务提交。

1
2
3
4
5
6
7
8
9
10
--- session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tag;

--- session 2
mysql> alter table `tag` add index name_index(tag_name);
Query OK, 0 rows affected (4 min 18.75 sec) -- 4分钟后我提交了第一个事务
Records: 0  Duplicates: 0  Warnings: 0

在阻塞时通过查看processlist可以发现 Waiting for table metadata lock ，这表示这个session在等待MDL锁

1
2
3
4
5
6
7
8
9
mysql> SHOW PROCESSLIST;
+----+------+-----------------+--------------------+---------+------+---------------------------------+--------------------------------------------------+
| Id | User | Host            | db                 | Command | Time | State                           | Info                                             |
+----+------+-----------------+--------------------+---------+------+---------------------------------+--------------------------------------------------+
|  8 | root | localhost:54227 | fs_demo            | Query   |    0 | starting                        | SHOW PROCESSLIST                                 |
|  9 | root | localhost:54230 | fs_demo            | Query   |  153 | Waiting for table metadata lock | alter table `tag` add index name_index(tag_name) |
| 10 | root | localhost:54321 | information_schema | Sleep   |   11 |                                 | NULL                                             |
+----+------+-----------------+--------------------+---------+------+---------------------------------+--------------------------------------------------+
3 rows in set (0.00 sec)

这种阻塞会严重影响线上的项目使用，修改线上数据库表结构时尽量避免使用高峰期，同时应该避免长事务。

如何安全的修改线上的表结构

  1. 生产环境的任何大表或频繁操作的小表，ddl都要非常慎重，最好在业务低峰期执行。

  2. 设计上要尽可能避免大事务，大事务不仅仅会带来各种锁问题，还会引起复制延迟/回滚空间爆满等各类问题。

  3. 设置参数 lock_wait_timeout 为较小值，使被阻塞端主动停止。

  4. 增强监控告警，及时发现 MDL 锁。

  5. 或许这样操作也是一种好办法：按新结构创建新表 -> 将旧表数据迁移至新表 -> 重命名两个表（三步都通过编写sql语句完成，比手动操作快，第二步的数据迁移操作视情况而定）。过程中最好在没人用的时候操作

  6. 操作ddl之前，先用以下语句查一下有没有长事务：

    1
    SELECT * FROM information_schema.INNODB_TRX;

  7. 多副本（主从、集群）下可以做热更新。

意向锁(Intention Locks)

假设这时候你需要对一张表加写锁，但是写锁不能和其他锁冲突，所以你需要检查表中每一行数据是否加锁，难道加个锁也要去遍历表中的记录吗？明显这样的效率比较低。

  • 意向共享锁（IS Lock）

  • 意向排他锁（IX Lock）

    表级意向锁与行级锁的兼容性表

    表级意向锁与行级锁的兼容性表

如果需要对记录R上X锁，那么需要对记录R的表先上IX锁，再对记录R上X锁。如果表上有IX锁则表明此时表内有X锁。

意向锁的作用就是在上表锁的时候可以快速判断是否可以上锁，而不需要遍历表中的记录。

三种行锁

Record Lock

单个记录上的锁，锁住索引记录，如果表没有设置索引则会使用隐藏的主键来进行锁定

Gap Lock

间隙锁，锁定一个范围，但是不包含自身

Next-Key Lock

Gap Lock+ Record Lock 锁定一个范围，并且锁定记录本身，Next-Key Lock 的锁定技术被称为 Next-Key Locking，这种技术主要是为了解决Phantom Problem，

复现间隙锁

  • 对tag表中tag_value列增加唯一索引，然后在事务一中对tag_value=30 加X锁，在事务二中执行插入tag_value=20的数据行，不会阻塞，此时Next-Key Lock降级为Record Lock，提高系统并发性，这种降级只会发生在查询的列是唯一索引的情况下。
  • 对tag表中tag_value列增加普通索引，然后在事务一中对tag_value=30 加X锁，在事务二中执行插入tag_value=20的数据行，发生阻塞。阻塞的范围是（10,30）、（30，+∞）在可以看到最后只有tag_value =5 插入成功。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--- 事务一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tag;
+----+----------+-----------+---------------------+---------------------+--------+
| id | tag_name | tag_value | create_time         | update_time         | enable |
+----+----------+-----------+---------------------+---------------------+--------+
|  1 | tt       |        10 | 2024-06-20 20:33:08 | 2024-06-20 20:33:09 |      1 |
|  2 | sss      |        30 | 2024-06-20 20:33:32 | 2024-06-20 20:33:33 |      1 |
+----+----------+-----------+---------------------+---------------------+--------+

mysql> select * from tag where tag_value =30 for update;

--- 事务二
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `fs_demo`.`tag` (`id`, `tag_name`, `tag_value`) VALUES (3, 'e\'e\'e', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `fs_demo`.`tag` (`id`, `tag_name`, `tag_value`) VALUES (3, 'e\'e\'e', 31);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `fs_demo`.`tag` (`id`, `tag_name`, `tag_value`) VALUES (3, 'e\'e\'e', 5);
Query OK, 1 row affected (0.00 sec)

通过查看information_schema.INNODB_LOCKS; 可以发现阻塞的lock_mode中有间隙锁GAP

1
2
3
4
5
6
7
8
mysql> SELECT * FROM information_schema.INNODB_LOCKS;
+-------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table      | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 7880:68:4:5 | 7880        | X,GAP     | RECORD    | `fs_demo`.`tag` | tag_value  |         68 |         4 |        5 | 30, 2     |
| 7879:68:4:5 | 7879        | X         | RECORD    | `fs_demo`.`tag` | tag_value  |         68 |         4 |        5 | 30, 2     |
+-------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

总结

这里介绍了两种用来控制数据行的标准锁：排他锁和共享锁，还介绍了避免DDL和DML冲突的元数据锁MDL，优化加锁流程的意向锁，后续又介绍了行锁的三种算法Record Lock、Gap Lock、Next-Key Lock，事务的隔离性其实就是通过组合各种锁来实现的，其实数据库的外键和自增长也用到了锁，下一篇总结一下事务的隔离级别的实现和MVCC原理。

mysql创建索引导致死锁，数据库崩溃，mysql的表级锁之【元数据锁（meta data lock，MDL)】全解_51CTO博客_数据库表锁死原因

12 | 本地事务如何实现隔离性？-周志明的软件架构课-极客时间 (geekbang.org)

《MySQL技术内幕（InnoDB存储引擎）》

如果觉得我的文章对您有用，赏我一包辣条吧！您的支持将鼓励我继续创作！也可以加我微信一起交流学习，折腾点有意思事情。