mysql常见的死锁场景

最新推荐文章于 2024-05-13 02:58:20 发布

置顶祖传编码手艺人

最新推荐文章于 2024-05-13 02:58:20 发布

阅读量1.9k

点赞数 1

分类专栏： mysql 死锁文章标签： mysql 多线程

本文链接：https://blog.csdn.net/qw790707988/article/details/108857664

版权

mysql 同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

死锁

1 篇文章 0 订阅

订阅专栏

mysql的锁类型

根据锁的粒度分为：
1、表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
2、页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。很少使用。
3、行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

innodb引擎的锁类型

1、共享锁与排它锁

共享锁：事务A获取对行锁后不会阻塞其他事务对行的读取操作，但会阻塞对行的修改。
排它锁：事务A获取了排它锁后会阻塞其他事务对改行的读取与修改。

2、意向锁

意向锁是表级锁，指示事务稍后对表中的行需要哪种类型的锁（共享锁或排他锁）。有两种类型的意图锁：

意向共享锁
意向排它锁

使用SELECT … FOR SHARE会先对表设置一个IS锁，使用 SELECT … FOR UPDATE先对表设置一个IX锁。

意向锁定协议如下：
在事务可以获取表中某行上的共享锁之前，它必须首先获取IS该表上的锁或更高级别的锁。
在事务可以获取表中某行的排它锁之前，它必须首先获取IX 该表中的锁。

	`X`	`IX`	`S`	`IS`
`X`	冲突	冲突	冲突	冲突
`IX`	冲突	兼容	冲突	兼容
`S`	冲突	冲突	兼容	兼容
`IS`	冲突	兼容	兼容	兼容

3、记录锁

记录锁定是对索引记录的锁定。例如， SELECT c1 FROM t WHERE id = 10 FOR UPDATE; 可以防止其他事务对id=10 的行的插入、更新、删除。

4、间隙锁

间隙锁定是对索引记录之间的间隙的锁定，或者是对第一个或最后一个索引记录之前的间隙的锁定。
以下场景会产生间隙锁：

SELECT c1 FROM t WHERE id BETWEEN 10 and 20 FOR UPDATE;
SELECT * FROM child WHERE id = 100 FOR UPDATE;

5、下一键锁

下一键锁定是索引记录上的记录锁定和索引记录之前的间隙上的间隙锁定的组合。

CREATE TABLE `lock3` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` int DEFAULT NULL,
  `aa` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8;
表记录如下：
mysql> select * from lock3;
+----+------+------+
| id | name | aa   |
+----+------+------+
|  1 |    2 | aa   |
|  2 |    3 | bb   |
|  6 |    7 | ee   |
| 10 |    7 | ff   |
| 11 |   10 | cc   |
| 15 |   19 | zs   |
+----+------+------+
6 rows in set (0.00 sec)

场景一：对普通索引添加排它锁产生间隙锁

select * from lock3 where name=10 for update;
+----+------+------+
| id | name | aa   |
+----+------+------+
| 11 |   10 | cc   |
+----+------+------+
1 row in set (0.00 sec)

加锁信息如下：

SELECT * FROM `performance_schema`.`data_locks` LIMIT 0,1000\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139712856206712:1093:139712736080064
ENGINE_TRANSACTION_ID: 149575
            THREAD_ID: 53
             EVENT_ID: 27
        OBJECT_SCHEMA: test
          OBJECT_NAME: lock3
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139712736080064
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139712856206712:36:5:13:139712736076960
ENGINE_TRANSACTION_ID: 149575
            THREAD_ID: 53
             EVENT_ID: 27
        OBJECT_SCHEMA: test
          OBJECT_NAME: lock3
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: index_name
OBJECT_INSTANCE_BEGIN: 139712736076960
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10, 11
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139712856206712:36:4:18:139712736077304
ENGINE_TRANSACTION_ID: 149575
            THREAD_ID: 53
             EVENT_ID: 27
        OBJECT_SCHEMA: test
          OBJECT_NAME: lock3
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139712736077304
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 11
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139712856206712:36:5:14:139712736077648
ENGINE_TRANSACTION_ID: 149575
            THREAD_ID: 53
             EVENT_ID: 27
        OBJECT_SCHEMA: test
          OBJECT_NAME: lock3
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: index_name
OBJECT_INSTANCE_BEGIN: 139712736077648
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 19, 15
4 rows in set (0.00 sec)

产生的间隙锁为：
(7, 10)
[10, 19)

场景二：对不存在的唯一索引加锁

mysql> select * from lock3 where id=100 for update;

加锁信息如下：
mysql> SELECT * FROM `performance_schema`.`data_locks` LIMIT 0,1000\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139712856206712:1093:139712736080064
ENGINE_TRANSACTION_ID: 149587
            THREAD_ID: 53
             EVENT_ID: 34
        OBJECT_SCHEMA: test
          OBJECT_NAME: lock3
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139712736080064
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139712856206712:36:4:1:139712736076960
ENGINE_TRANSACTION_ID: 149587
            THREAD_ID: 53
             EVENT_ID: 34
        OBJECT_SCHEMA: test
          OBJECT_NAME: lock3
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139712736076960
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
2 rows in set (0.00 sec)

此时会产生一条为最终记录锁：
会产生一个间隙锁：
(15, positive infinity)

6、插入意向锁

插入意图锁定是一种通过INSERT行插入之前的操作设置的间隙锁定。此锁以这种方式发出信号，表明要插入的意图是：如果多个事务未插入间隙中的相同位置，则不必等待彼此插入的多个事务。

死锁

什么是死锁？
死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去。

对于mysql而言，表锁不会产生死锁。而行锁则会产生死锁（原因是：表锁一次性锁定所有的表）。

死锁场景一：由于加锁顺序不一致导致的死锁
商品表goods表有商品G1，G2，G3 ，… n中商品。用户A的购物车添加的商品有G1，G2。用户B添加到购物车的商品有G2，G1，G4。当用户结算的时候，需要将goods表中对应的商品库存减1。此时，用户A与用户B同时结算。

用户A	用户B
select * from goods where good_id=G1 for update	select * from goods where good_id=G2 for update
对商品G2加锁，处于等待…	对商品G1进行加锁，等待…产生死锁

在上表的情况中，由于加锁顺序不一致导致死锁产生。

解决办法：对需要加锁的记录一次性加锁

  用户A: select * from goods where good_id in (G1,G2) for update; #获取锁成功
  用户B: select * from goods where good_id in (G2,G1,G3) for update; # 处于等待状态
  
  用户A: 更新库存以及其他业务操作提交事务
  用户B：获取到锁
  
  用户A: 更新完毕
  用户B：更新库存以及其他业务操作提交事务

死锁场景二：间隙锁与插入意图锁冲突导致的死锁
对一条并不存在的记录加排它锁，记录不存在插入记录。在事务并发场景下出现死锁。
测试表信息如下：

#表结构
CREATE TABLE `lock3` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` int DEFAULT NULL,
  `aa` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8;

#表数据
mysql> select * from lock3;
+----+------+------+
| id | name | aa   |
+----+------+------+
|  1 |    2 | aa   |
|  2 |    3 | bb   |
|  6 |    7 | ee   |
| 10 |    7 | ff   |
| 11 |   10 | cc   |
| 15 |   19 | zs   |
+----+------+------+
6 rows in set (0.00 sec)

sessionA	结果A	结果B	sessionB
select * from lock3 where id=100 for update;	Query OK, 0 rows affected (0.00 sec)
		Query OK, 0 rows affected (0.00 sec)	select * from lock3 where id=100 for update;
进行其他业务处理
insert into lock3 values(100, 101, ‘aa’)	处于等待中		进行其他业务处理
	插入成功	产生死锁	insert into lock3 values(100, 101, ‘aa’)

在上面的示例中sessionA与sessionB都去获取了id=100的排它锁，并且都获取成功，原因是：由于id=100的记录并不存在，实际上获取的是一个supremum pseudo-record的间隙锁，锁定范围为：(15, 无穷大)，间隙锁并不冲突。
当sessionA插入记录时候，会产生一个 “插入意图锁” ，这与sessionB获取的间隙锁是冲突的，因此sessionA会处于等待状态。而sessionB再次插入数据时也是如此，sessionA与sessionB互相等待产生了死锁。

可通过命令查询最近一次死锁信息

mysql> show engine innodb status \G;

祖传编码手艺人

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
mysql常见的死锁场景

mysql的锁类型根据锁的粒度分为：1、表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。2、页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。很少使用。3、行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。...
复制链接

扫一扫