将mysql 5.7与存储引擎一起用作innodb . 我有一个存储产品信息的表 . 该表在productId上使用唯一键看起来像这样
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+-------------------+-----------------------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| productId | varchar(50) | NO | UNI | NULL | |
| seller | varchar(100) | NO | MUL | NULL | |
| updatedAt | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| status | varchar(100) | NO | MUL | NULL | |
| data | longtext | NO | | NULL | |
+-----------+--------------+------+-----+-------------------+-----------------------------+
我通过连接到这个mysql的java应用程序进行了两次操作:
1.如果productId的版本大于现有事件,则需要插入新的传入事件(包含有关产品更改的信息) . 该版本在我的数据列中存储为json blob
2.更新productId的行以更改状态 .
我的隔离级别是读取提交的 . 我尝试了两种方法,但都导致了死锁:
Approach1:
Transaction1 starts
Insert ignore into products where productId='X' values(); // Takes a S lock on the row
select * from products where productId='X' for update ; // Take a X lock on the row to prevent new writes and compare the incoming event with the current event
Insert into products values on duplicate key update values // insert into row and on duplicate key update values
commit
并发更新将打开另一个事务:
Transaction2 starts
select * from products where productId='X' for update ; // Take a X lock on the row to prevent new writes and compare the incoming event with the current event
Insert into products values on duplicate key update values // insert into row and on duplicate key update values
commit;
这导致以下情况陷入僵局:
1.事务1 - 插入ignore语句对该行进行了S锁定 .
2.事务2 - 选择更新语句正在等待对该行进行X锁定 .
3.事务1 - 选择更新语句尝试对该行进行X锁定 .
这会导致死锁,因为事务1正在保持S锁,而事务2正在等待进入X锁,而当事务1尝试进行X锁时,会导致死锁 .
方法2:
Transaction 1 starts:
select * from products where productId='X' for update ; // If a row exists then it will be locked else I know it does not exist
Insert ignore into products where productId='X' values();
commit
Transaction 2 starts:
select * from products where productId='X' for update ; // If a row exists then it will be locked else I know it does not exist
commit
这导致以下情况陷入僵局:
1.事务1 - 选择更新语句对行进行X锁定 .
2.事务2 - 选择更新语句正在等待对该行进行X锁定 .
3.事务1 - 插入ignore语句尝试对该行进行S锁定,但事务1的X锁定已经在等待导致死锁的锁定
所以,我想知道如何处理并发更新并将新事件(而不是行更新)插入到我的表中而不会导致死锁 .
1.锁定顺序应该是什么?
2.如何确保并发更新和新行插入在没有死锁的情况下工作 .
任何帮助,将不胜感激 :)
1 回答
经过一些实验,我设法解决了它,核心问题是S的序列,然后在一个事务中采用X锁,而在另一个事务中采用X锁 . 基本上,在开始时采取的S锁导致所有情况都有死锁 .
因此,我将事务外部的insert ignore语句作为第一个语句移动 . 该事务现在只接受X锁定,这意味着其中一个事务在另一个事务上等待X锁定 .
Event1:插入新事件
事件2:更新现有事件
因此,这两个事件都有只竞争X锁的事务,这有助于我避免死锁 .