MySQL insert deadlock

mysql deadlock database

keeplearning 2020-05-04 16:49:24 ‧ 4500 瀏覽

Hi ,各位先進!
最近查看了一下MySQL的Error log發現一個deadlock，所以想試著了解這個deadlock是如何產生的。
觀察了一下該資料表的行為應該是一個單純insert的資料表。
資料表上是有建立了unique key，但卻在insert時會產生gap lock，實在不太了解這個死鎖是如何產生，
所以想來這邊請教一下大家的經驗。

下面提供了資料表的結構和error log資訊，如有任何遺漏的訊息，我將再補上，謝謝！

CREATE TABLE `batch_job_instance` (
  `JOB_INSTANCE_ID` bigint(20) NOT NULL,
  `VERSION` bigint(20) DEFAULT NULL,
  `JOB_NAME` varchar(100) NOT NULL,
  `JOB_KEY` varchar(32) NOT NULL,
  PRIMARY KEY (`JOB_INSTANCE_ID`),
  UNIQUE KEY `JOB_INST_UN` (`JOB_NAME`,`JOB_KEY`)
);

2020-04-23T00:00:00.027656+08:00 26928005 [Note] InnoDB:
*** (1) TRANSACTION:

TRANSACTION 248976630, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 26927988, OS thread handle 139975505934080, query id 38449466544 192.168.1.161 appledevor update
INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (1786, 'deleteInvalidAccount', 'b2fa84f433bd36aa78cb720c14620eac', 0)
2020-04-23T00:00:00.027748+08:00 26928005 [Note] InnoDB: 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 887 page no 19 n bits 304 index JOB_INST_UN of table `djob`.`batch_job_instance` trx id 248976630 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 128 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 11; hex 73796e63557365724a6f62; asc syncUser;;
 1: len 30; hex 313836633835666539326432333735643633386466373865313234383930; asc 186c85fe92d2375d638df78e124890; (total 32 bytes);
 2: len 8; hex 80000000000003dd; asc         ;;

2020-04-23T00:00:00.028468+08:00 26928005 [Note] InnoDB:
*** (2) TRANSACTION:

TRANSACTION 248976637, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 26928005, OS thread handle 139974650029824, query id 38449466559 192.168.1.161 appledevor update
INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (1788, 'deleteWEBAccount', 'b59171737abfaba2be315dba95923f54', 0)
2020-04-23T00:00:00.028552+08:00 26928005 [Note] InnoDB: 
*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 887 page no 19 n bits 304 index JOB_INST_UN of table `djob`.`batch_job_instance` trx id 248976637 lock mode S locks gap before rec
Record lock, heap no 128 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 11; hex 73796e63557365724a6f62; asc syncUser;;
 1: len 30; hex 313836633835666539326432333735643633386466373865313234383930; asc 186c85fe92d2375d638df78e124890; (total 32 bytes);
 2: len 8; hex 80000000000003dd; asc         ;;

2020-04-23T00:00:00.029458+08:00 26928005 [Note] InnoDB: 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 887 page no 19 n bits 304 index JOB_INST_UN of table `djob`.`batch_job_instance` trx id 248976637 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 128 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 11; hex 73796e63557365724a6f62; asc syncUser;;
 1: len 30; hex 313836633835666539326432333735643633386466373865313234383930; asc 186c85fe92d2375d638df78e124890; (total 32 bytes);
 2: len 8; hex 80000000000003dd; asc         ;;

2020-04-23T00:00:00.030145+08:00 26928005 [Note] InnoDB: 
*** WE ROLL BACK TRANSACTION (2)

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

一級屠豬士

iT邦大師 1 級 ‧ 2020-05-05 11:14:15

你的設計, JOB_INSTANCE_ID , 當 PK, PRIMARY KEY (`JOB_INSTANCE_ID`).
log 裡面看到
先有(1) TRANSACTION: query id 38449466544 (時間T00:00:00.027748+08:00)
INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values 
(1786, 'deleteInvalidAccount', 'b2fa84f433bd36aa78cb720c14620eac', 0)

產生一個 trx
trx id 248976630 (時間T00:00:00.028468+08:00)
lock_mode X locks ;  
這是 exclusive (X) lock

接著是 (2) TRANSACTION: query id 38449466559 (時間T00:00:00.028552+08:00)
產生兩個 trx

trx id 248976637 (時間T00:00:00.029458+08:00)
lock mode S ;
這是 shared (S) lock 

trx id 248976637 (時間T00:00:00.030145+08:00)
lock_mode X

第二個 query 有兩個 trx; 先是 shared mode,
這是去做查詢,查詢也是會有 lock 的,就是 shared mode,
而且是查到的 record 有一個 unique key ; 跟第一道 query 的 unique key
值是一樣的.
然後是 第二個 trx , 是 X lock, 這就是要 insert 了, 但是是一起的 transcation,
所以受到了影響.

因為我只能透過部分資料,與一些個人的推測.
觀察到你的 table 定義中, JOB_INSTANCE_ID 並沒有 auto_increment,
是否你是使用 
select max(JOB_INSTANCE_ID) from table 的方式來取得,自行遞增.
這在 insert 之前,包成 transcation.
這也許是可能的因素.