mysql批量插入死锁问题分析(正序VS逆序)

最新推荐文章于 2024-03-13 23:18:25 发布

weixin_34232617

最新推荐文章于 2024-03-13 23:18:25 发布

阅读量1.1k

点赞数

文章标签：数据库 java python

原文链接：https://my.oschina.net/tantexian/blog/2245848

版权

2019独角兽企业重金招聘Python工程师标准>>>

https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html#innodb-insert-intention-locks

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

1、插入意向锁是gap锁的一种，在row insert操作之前插入。

2、插入意向锁表示：当多个事务插入记录到相同的索引间隙(same index gap), 只要他们插入的不是gap中的相同位置，则各个插入记录互相不需要等待。

3、举例：假若有索引记录4和7，两个分离的事务分别视图插入5和6. 那么它们则会各自在获取对应行的排他锁之前锁住gap(4,7) (between 4 and 7)，但是他们不会互相阻塞，因为这些行没有冲突。

The following example demonstrates a transaction taking an insert intention lock prior to obtaining an exclusive lock on the inserted record. The example involves two clients, A and B.

Client A creates a table containing two index records (90 and 102) and then starts a transaction that places an exclusive lock on index records with an ID greater than 100. The exclusive lock includes a gap lock before record 102:

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

Client B begins a transaction to insert a record into the gap. The transaction takes an insert intention lock while it waits to obtain an exclusive lock.

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

Transaction data for an insert intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

AUTO-INC Locks

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

For more information, see Section 14.11.1.5, “AUTO_INCREMENT Handling in InnoDB”.

当insert出现duplicate-key冲突error，将会对冲突的index record加上s锁：

https://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. Suppose that an InnoDB table t1 has the following structure:

insert ignore的行为与insert一致，只是当错误发生时候，继续go on：

https://stackoverflow.com/questions/18170235/does-insert-ignore-lock-the-table-even-if-it-ignores-the-insert

On InnoDB table, if the record is not locked, the regular INSERT will immediately report the duplicate key error (INSERT IGNORE will skip the error and go on).
But if the record is locked by the other session (for example the record is inserted but not commited yet, or the record is locked by an UPDATE or DELETE or SELECT FOR UPDATE command), the INSERT command will "hang" and will be waiting until the other session will remove the lock (by COMMIT or ROLLBACK). Then, if record still exists after removing the lock, INSERT will report the error (INSERT IGNORE will ignore the error), but if record doesn't exist, INSERT will add this record to the table.

IGNORE keyword just says "in case of any error just ignore it an go on", but it doesn't affect locking behaviour.

业务中具体示例：

MySQL InnoDB支持三种行锁定方式：

l 行锁（Record Lock）:锁直接加在索引记录上面，锁住的是key。

l 间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。

l Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。

| insert intention lock（IK意向锁）如果插入前，该间隙已经由gap锁，那么Insert会申请插入意向锁。为了避免幻读，

当其他事务持有该间隙的间隔锁，插入意向锁就会被阻塞（不用直接用gap锁，是因为gap锁不互斥）。

下述针对死锁日志进行分：

先给出表结构：

CREATE TABLE `account_message` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`account_id` int(11) DEFAULT NULL COMMENT '接收方帐号id',

`message_id` bigint(20) NOT NULL COMMENT 'message.id',

`xxx` tinyint(2) NOT NULL COMMENT 'xxx',

`xxx` int(11) DEFAULT '0' COMMENT 'xxx',

`xxx` tinyint(2) DEFAULT NULL COMMENT 'xxx',

`status` tinyint(2) NOT NULL COMMENT '消息状态 1：未读 2：已读',

`deleted` tinyint(1) NOT NULL COMMENT '是否已删除 0：否 1是',

`created_time` datetime NOT NULL COMMENT '创建时间',

`modify_time` datetime NOT NULL COMMENT '修改时间',

PRIMARY KEY (`id`),

UNIQUE KEY `uk_message_account` (`account_id`,`platform`,`message_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户消息表';

explain解释执行计划：

insert ignore into account_message (

account_id, message_id,

xxx, xxx, xxx,

xxx, xxx, xxx, deleted,

created_time, modify_time

)

values

(100861234, 325,

'2018-01-17 16:32:30',

'2018-01-17 16:32:30')

(100861234, 219,

'2018-01-05 15:50:21',

'2018-01-05 15:50:21')

1、出现了死锁。（在MySQL客户端下输入命令： show engine innodb status \G; ）

------------------------

LATEST DETECTED DEADLOCK 注释：此处表示发生的死锁

------------------------

2018-04-20 10:36:58 7f000a1c3700

*** (1) TRANSACTION:注释：此处表示事务1开始

TRANSACTION 3461032868, ACTIVE 0 sec inserting 注释：此处为记录当前事务1的id

mysql tables in use 1, locked 1 注释：当前事务1正在操作一张表(tables in use 1)

LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 注释：等待持有两把锁(2 lock struct(s))，一个表级意向锁，一个行锁（1 row lock(s)）

注释：此处为记录当前数据库线程id

MySQL thread id 98662187, OS thread handle 0x7f00095ab700, query id 12918596071 10.5.130.253 cep_mess_65f3 update

/*id:a9ad0cab*/insert ignore into account_message (

account_id, message_id,

platform, from_platform, priority,

type, message_type, status, deleted,

created_time, modify_time

)

values

(39440662, 325, 3,0,1,0,2,1,0,'2018-01-17 16:32:30','2018-01-17 16:32:30')

（省略部分日志...）

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 注释：此处表示当前事务1等待获取行锁S(共享锁)

RECORD LOCKS space id 2044 page no 4632 n bits 664 index `uk_message_account` of table `cep_message`.`account_message` trx id 3461032868 lock mode S waiting

注释：记录锁（RECORD LOCKS，锁索引uk_message_account）

*** (2) TRANSACTION:注释：此处表示事务2开始

TRANSACTION 3461032867, ACTIVE 0 sec inserting注释：此处为记录当前事务2的id

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 2 注释：等待持有三把锁(3 lock struct(s))，一个表级意向锁，2个行锁（1 row lock(s)）

注释：此处为记录当前数据库线程id（此处的mysql线程id与上次标记98662187 不一样，因此说明是两个线程并发请求）

MySQL thread id 98662189, OS thread handle 0x7f000a1c3700, query id 12918596070 10.5.130.253 cep_mess_65f3 update

/*id:a9ad0cab*/insert ignore into account_message (

account_id, message_id,

platform, from_platform, priority,

type, message_type, status, deleted,

created_time, modify_time

)

values

(39440662, 325, 3,0,1,0,2,1,0,'2018-01-17 16:32:30','2018-01-17 16:32:30')

（省略部分日志...）

*** (2) HOLDS THE LOCK(S):注释：此处表示当前事务2占有X锁（排它锁）（但是不加间隙锁）

RECORD LOCKS space id 2044 page no 4632 n bits 664 index `uk_message_account` of table `cep_message`.`account_message`

trx id 3461032867 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:注释：此处表示当前事务2等待获取插入意向锁

RECORD LOCKS space id 2044 page no 4632 n bits 664 index `uk_message_account` of table `cep_message`.`account_message`

trx id 3461032867 lock_mode X locks gap before rec insert intention waiting

*** WE ROLL BACK TRANSACTION (1) 注释：对事物1进行回滚

------------

TRANSACTIONS

------------

总结：

1、事务1等待获取行级共享S锁。

2、事务2持有行级排它X锁

3、事务2等待获取插入意向锁

因此事务1后续获取的行级共享锁S被事务2的排它锁给占有了，从而导致获取不到，因此事务1被回滚了。

Next-Key Locks

在默认情况下，mysql的事务隔离级别是可重复读，并且innodb_locks_unsafe_for_binlog参数为0，这时默认采用next-key locks。所谓Next-Key Locks，

就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。

下面我们针对大部分的SQL类型分析是如何加锁的，假设事务隔离级别为可重复读。

select .. from

不加任何类型的锁

select...from lock in share mode

在扫描到的任何索引记录上加共享的（shared）next-key lock，还有主键聚集索引加排它锁

select..from for update

在扫描到的任何索引记录上加排它的next-key lock，还有主键聚集索引加排它锁

update..where delete from..where

在扫描到的任何索引记录上加next-key lock，还有主键聚集索引加排它锁

insert into..

简单的insert会在insert的行对应的索引记录上加一个排它锁，这是一个record lock，并没有gap，所以并不会阻塞其他session在gap间隙里插入记录。不过在insert操作之前，还会加一种锁，

官方文档称它为insertion intention gap lock，也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，只要插入的记录不是gap间隙中的相同位置，

则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。想象一下，如果一个表有一个索引idx_test，表中有记录1和8，那么每个事务都可以在2和7之间插入任何记录，

只会对当前插入的记录加record lock，并不会阻塞其他session插入与自己不同的记录，因为他们并没有任何冲突。

假设发生了一个唯一键冲突错误，那么将会在重复的索引记录上加读锁。当有多个session同时插入相同的行记录时，如果另外一个session已经获得改行的排它锁，那么将会导致死锁。

https://www.cnblogs.com/crazylqy/p/7689447.html

http://www.cnblogs.com/crazylqy/p/7773492.html

http://blog.sina.com.cn/s/blog_a1e9c7910102vnrj.html

http://hedengcheng.com/?p=771

转载于:https://my.oschina.net/tantexian/blog/2245848

weixin_34232617

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql批量插入死锁问题分析(正序VS逆序)

2019独角兽企业重金招聘Python工程师标准>>> ...
复制链接

扫一扫