mysql deadlocks with concurrent inserts
来源:互联网 发布:电脑上能看淘宝直播吗 编辑:程序博客网 时间:2024/04/29 01:44
转载自blogspot
It is possible to cause deadlocks in mysql (Innodb) on concurrent insert statements, without there being any transactions in progress. Deadlocks are possible even when the inserts don't collide on any key.
The deadlocks occur due to gap locking done by mysql. There are several reasons for gap locking, and in this particular case, it has to do with preserving a unique key constraint on an index. The situation presents itself to us this way: There is a unique key constraint on a column and we are doing an insert. Mysql has to make sure that the lock it takes is sufficient to prevent another concurrent insert from adding a record with the same key, thus breaking the unique key constraint.
Mysql innodb engine performs row locking on inserts. If column A has a unique key constraint, and we are adding the value "bbb" for column A in an insert statement, mysql needs to lock any gap in the index between the two current records where "bbb" will be inserted at.
To illustrate the deadlock, let us start with a table schema:
TABLE vegetable (
id bigint(10) NOT NULL auto_increment,
name varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_name (name)
) ENGINE=InnoDB
Let us assume the existence of these records in the table, and look at them in 'name' index order:
id name
10 ggg
05 jjj
Now, imagine two concurrent connections executing the following inserts in the following order:
Connection 1:
insert ignore into vegetable values(null, "ppp");
For this insert to proceed, connection 1 will lock the gap between "jjj" and "ppp" in the name index.
Connection 2:
insert ignore into vegetable values (null,"iii");
This will require locking the gap after "ggg", upto "iii". Since the lock from connection 1 does not span this, it will succeed.
insert ignore into vegetable values (null, "mmm");
This needs to lock the gap after "jjj" upto "mmm". Since connection 1 has a lock between "jjj" and "ppp", effectively spanning the lock connection 2 is attempting to take, this will block.
Connection 1:
insert ignore into vegetable values (null, "hhh");
This requires the gap lock between "ggg" and "hhh". This will block as it spans the the lock ["ggg" to "iii"] held by connection 2.
Thus we have both connections blocked on each other. This is the deadlock.
Here is a diagram. Transactions to the left are done by Connection 2. Transactions to the right are done by Connection 1. The sequence of transactions is donated by numbers 1) through 4).
Connection 2 Connection1
--------------------------- ggg
G G
A AP
P <------------------- 4) hhh
Lock blocks (deadlock)
2) iii -------------------->
--------------------------- jjj
G G
A
P A
Lock
3) mmm ---------------> P
blocks
L
o
c
k
<--------------------- 1) ppp
You can avoid this if you can order the inserts on each connection on the same direction. The deadlock happens as connection 2 inserts in ascending order of the index, while connection 1 inserts on descending order.
If you can't do this for practical reasons, you could retry the operation. Unless there is a high level of concurrency with a high load on the db where each transaction takes a heavy hit, a simple retry should work.
The deadlocks occur due to gap locking done by mysql. There are several reasons for gap locking, and in this particular case, it has to do with preserving a unique key constraint on an index. The situation presents itself to us this way: There is a unique key constraint on a column and we are doing an insert. Mysql has to make sure that the lock it takes is sufficient to prevent another concurrent insert from adding a record with the same key, thus breaking the unique key constraint.
Mysql innodb engine performs row locking on inserts. If column A has a unique key constraint, and we are adding the value "bbb" for column A in an insert statement, mysql needs to lock any gap in the index between the two current records where "bbb" will be inserted at.
To illustrate the deadlock, let us start with a table schema:
TABLE vegetable (
id bigint(10) NOT NULL auto_increment,
name varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_name (name)
) ENGINE=InnoDB
Let us assume the existence of these records in the table, and look at them in 'name' index order:
id name
10 ggg
05 jjj
Now, imagine two concurrent connections executing the following inserts in the following order:
Connection 1:
insert ignore into vegetable values(null, "ppp");
For this insert to proceed, connection 1 will lock the gap between "jjj" and "ppp" in the name index.
Connection 2:
insert ignore into vegetable values (null,"iii");
This will require locking the gap after "ggg", upto "iii". Since the lock from connection 1 does not span this, it will succeed.
insert ignore into vegetable values (null, "mmm");
This needs to lock the gap after "jjj" upto "mmm". Since connection 1 has a lock between "jjj" and "ppp", effectively spanning the lock connection 2 is attempting to take, this will block.
Connection 1:
insert ignore into vegetable values (null, "hhh");
This requires the gap lock between "ggg" and "hhh". This will block as it spans the the lock ["ggg" to "iii"] held by connection 2.
Thus we have both connections blocked on each other. This is the deadlock.
Here is a diagram. Transactions to the left are done by Connection 2. Transactions to the right are done by Connection 1. The sequence of transactions is donated by numbers 1) through 4).
Connection 2 Connection1
--------------------------- ggg
G G
A AP
P <------------------- 4) hhh
Lock blocks (deadlock)
2) iii -------------------->
--------------------------- jjj
G G
A
P A
Lock
3) mmm ---------------> P
blocks
L
o
c
k
<--------------------- 1) ppp
You can avoid this if you can order the inserts on each connection on the same direction. The deadlock happens as connection 2 inserts in ascending order of the index, while connection 1 inserts on descending order.
If you can't do this for practical reasons, you could retry the operation. Unless there is a high level of concurrency with a high load on the db where each transaction takes a heavy hit, a simple retry should work.
0 0
- mysql deadlocks with concurrent inserts
- 8.11.3 Concurrent Inserts 并发插入:
- Deadlocks
- Inserts插入
- Concurrent Programming with Processes
- Concurrent Programming with Threads
- Concurrent list with condition variable
- 6-DEADLOCKS
- Concurrent Programming 2: Concurrent Programming with Objective-C
- Introduction to Concurrent Programming with Stackless Python
- Concurrent Programming with I/O Multiplexing
- 批量插入(Batch inserts)
- deadlocks(死锁)
- deadlocks(死锁)
- Locks, Deadlocks, and Synchronization
- 消息死锁(Message Deadlocks)
- Debugging Deadlocks on Android
- Locks, Deadlocks, and Synchronization
- 每个程序员都必读的10篇重要文章
- LeetCode 014 Longest Common Prefix
- lua调用c函数
- cocos2dx游戏开发学习笔记3-lua面向对象分析
- 在C语言中,double、long、unsigned、int、char类型数据所占字节数
- mysql deadlocks with concurrent inserts
- printf函数
- 邮件传输协议SMTP,POP3,IMAP,MIME
- 为现代JavaScript开发做好准备
- C++编程对缓冲区的理解
- 租用服器WIN2003+IIS6+PHP5.4 +PHP5.3.8的安装配置
- 802.1x协议浅析
- lua正则表达式
- Codeforces 432A Choosing Teams