死锁案例--(添加联合索引和复合索引,以及添加普通索引使其走二级索引)

用户14527

发布于 2022-07-03 19:44:27

1.5K0

发布于 2022-07-03 19:44:27

文章被收录于专栏：supremesupreme

1. 预备知识

1.1 表锁和行锁

表锁

表锁是MySQL中最基本的锁策略，并且是开销最小的策略。表锁会锁定整张数据表，用户的写操作（插入/删除/更新）前，都需要获取写锁（写锁会相互阻塞）；没有写锁时，读取用户才能获取读锁（读锁不会相互阻塞）。

行锁（仅限定于InnoDB）

行级锁可以最大程度的支持并发处理（同时也带来了最大的锁开销）。行级锁只在存储引擎实现，而MySQL服务器层没有实现。服务器层完全不了解存储引擎中的具体实现。

1.2 行锁简介

行锁的模式有：读/写意向锁（IS/IX锁）,读锁（S锁），写锁（X锁）以及自增锁（AI）。

行锁根据场景的不同又可以进一步细分，依次为Next-Key Lock，Gap Lock间隙锁，Record Lock记录锁和插入意向GAP锁。不同的锁锁定的位置是不同的，比如说记录锁只锁住对应的记录，而间隙锁锁住记录和记录之间的间隔，Next-Key Lock锁住记录和记录之前的间隙。不同类型锁的锁定范围大致如下图所示。

此外，锁对应的死锁日志信息标记如下所示：
记录锁（LOCK_REC_NOT_GAP）: lock_mode X locks rec but not gap
间隙锁（LOCK_GAP）: lock_mode X locks gap before rec
Next-key锁（LOCK_ORNIDARY）: lock_mode X
插入意向锁（LOCK_INSERT_INTENTION）: lock_mode X locks gap before rec insert intention

1.3 行锁加锁示例

InnoDB是聚簇索引，也就是B+树的叶子节点存储了主键索引以及数据行；InnoDB的二级索引的叶子节点存储的则是主键值，所以通过二级索引查询数据时，需要根据查询到的主键去聚簇索引中再次进行查询。

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';

（1）第一条SQL使用主键进行查询，则只需要在`id=49`主键上加上写锁（X锁）；

（2）第二条SQL使用二级索引查询，首先在`name='Tom'`上加写锁，然后根据获取的主键索引查询，在`id=49`主键上添加写锁。

具体如下图所示：

以上是基于单条数据讨论，针对多条数据：

update user set age = 10 where id > 49;

执行步骤：

（1）MySQL Server根据where条件读取满足条件的第一条记录，InnoDB引擎返回行记录并加锁；

（2）MySQL Server发起更新行记录的update请求，更新此记录；

（3）反复循环（1）（2）步骤，直到所有满足条件的记录均被修改。

具体如下图所示：

2.准备工作

2.1 创建数据表并初始化

create table dead_lock_test
(
    id int auto_increment
       primary key,
    v1 int not null,
    v2 int not null
);

插入三条数据：

insert into dead_lock_test (v1,v2) value (1,1);
insert into dead_lock_test (v1,v2) value (2,2);
insert into dead_lock_test (v1,v2) value (3,3);

需要注意，数据表中仅存在主键索引。此外，默认数据库引擎为InnoDB，事务隔离级别为RR（可重复读，相对于RC解决了幻读）。

2.2 开启锁监控

使用如下语句，开启MySQL锁监控：

root@localhost[test]>show variables like '%innodb_status_output%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output       | OFF   |
| innodb_status_output_locks | OFF   |
+----------------------------+-------+
# 开启
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
# 关闭
set GLOBAL innodb_status_output_locks=OFF;

3.场景复现

开启两个数据库客户端连接，分别执行如下SQL语句：

# session1
start transaction ;
insert into dead_lock_test (v1,v2) value (4,4);
delete from dead_lock_test where v1 = 4 and v2 = 4;
commit;

# session2
start transaction;
insert into dead_lock_test (v1,v2) value (5,5);
delete from dead_lock_test where v1 = 5 and v2 = 5;
commit;

> 不要问事务里就两条SQL，插入后删除走回滚就可以了之类的问题（我也不知道为什么这么写的）。

事务执行步骤如下表所示：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-07-24 15:03:19 0x7f30dda1c700
*** (1) TRANSACTION:
TRANSACTION 2129934, ACTIVE 17 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 139847853790976, query id 27 localhost root updating
delete from dead_lock_test where v1 = 4 and v2 = 4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 3 n bits 72 index PRIMARY of table `test`.`dead_lock_test` trx id 2129934 lock_mode X waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000208013; asc       ;;
 2: len 7; hex af000001980110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2129939, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 139847853524736, query id 28 localhost root updating
delete from dead_lock_test where v1 = 5 and v2 = 5
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 267 page no 3 n bits 72 index PRIMARY of table `test`.`dead_lock_test` trx id 2129939 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000208013; asc       ;;
 2: len 7; hex af000001980110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 3 n bits 72 index PRIMARY of table `test`.`dead_lock_test` trx id 2129939 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000208007; asc       ;;
 2: len 7; hex a7000001a70110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;

（1）由以上注释可知，事务2129934在尝试删除时，会对表中所有记录添加记录锁。这是因为当前删除记录条件为v1 = 4 and v2 = 4，在v1与v2字段上，并未建立相应的索引。因为无法通过索引确定主键，导致MySQL Server会先尝试锁定当前dead_lock_test表中所有记录添加记录锁（可以设置参数进行优化，根据where条件逐渐解除不满足条件记录上的记录锁）。

（2）事务2129934尝试对dead_lock_test表中所有记录添加锁，发现记录（id=5）已经被事务91327添加记录锁，导致事务2129934只能等待事务2129939放弃记录锁。

由上可知：

（1）事务2129934执行删除操作时，尝试获取表中所有记录的记录锁，其中记录（id=5）的锁被事务2129939持有；

（2）事务2129939执行删除操作时，尝试获取表中所有记录的记录锁，发现记录（id=1）的锁被事务2129934持有；

（3）至此，事务2129934与事务2129939构成互相等待，死锁形成。

4 解决方案

4.1 添加索引

由上述的分析可知，删除时因为where条件无法利用索引，导致MySQL会尝试对表中所有记录加记录锁，产生死锁。

我们仅需在v1以及v2字段上建立联合索引，缩小记录冲突范围。

create index dead_lock_test_v2_v1_index on dead_lock_test (v1, v2);
| dead_lock_test | CREATE TABLE `dead_lock_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v1` int(11) NOT NULL,
  `v2` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `dead_lock_test_v2_v1_index` (`v1`,`v2`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4

> 此处没有设置唯一索引，如果多个事务根据索引查询，锁定的记录存在重叠，也容易复现死锁现象。

> 不过当前业务侧的数据插入，可保证在短暂时间范围内，不存在重叠记录，且表中存在一些重复数据，因此不使用唯一索引。

4.2 最终

表中添加索引。

再执行上面的SQL，发现不会再发生锁等待以及死锁的情况

参考文章：https://www.modb.pro/db/65723

微信视频讲解：https://mp.weixin.qq.com/s/I7QujZrzJZRs6S0KdTMoBw

5

两个事务导致了死锁，因为where中的两个字段没有索引，所以在操作时会全表扫描导致锁整个表，可以通过建普通索引或者建(复合索引，联合索引{自己认为的})进行优化。

6 修改主键和其中一个字段为复合索引

mysql> show create table t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cnt` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_cnt` (`cnt`)
) ENGINE=InnoDB AUTO_INCREMENT=1649 DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.00 sec)

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          0 | unq_cnt  |            1 | cnt         | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

开始修改：

mysql> drop index unq_cnt on t;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t add primary  key (id,cnt);     //因为此时id为主键并且自增  
ERROR 1068 (42000): Multiple primary key defined
mysql> alter table t modify id int not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t drop primary key;
Query OK, 9 rows affected (0.06 sec)
Records: 9  Duplicates: 0  Warnings: 0
mysql> alter table t add primary key(id,cnt);        //设置复合主键
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cnt` varchar(32) NOT NULL,
  PRIMARY KEY (`id`,`cnt`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

联合主键体现在多个表上，复合主键体现在一个表中的多个字段

完结！

本文系转载，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

sql

本文系转载，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

sql

登录后参与评论

0 条评论

热度