iT邦幫忙

0

MySQL insert deadlock

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)

1 個回答

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.
這也許是可能的因素.

看更多先前的回應...收起先前的回應...

Hi 一級屠豬士,
感謝您提供一些想法以利思考。
就您提供的想法(計算max id),後來思考了一下,發現如果是包在同個tx的情境下,大家拿序列號應該會是要排隊或是出現duplicate的錯誤訊息。

後來想了想,因為這個資料表有UK,在有UK的情況下,MySQL就要確認UK是否有重複,這邊就需要加上S lock,剛好搜尋的條件下沒有重複,所以就加上了GAP。
我想這應該就是主因了。

我不確定你那邊的語句是否使用 max(id), 因為我看不到,
沒有天眼通.
不過我建議你千萬別這樣用.改用自動遞增,測試看看.

確實如您所說,沒有完整的情境就像瞎子摸象,自己只能夠過猜測去合理化結果也未必是正確的。
而我也太晚來了解問題,想透過binlog去多發現一些蛛絲馬跡,但為時已晚,binlog已被purge掉了。
最終,還是要感謝您的幫助,謝謝!。

你能查到你們的程式碼嗎?

抱歉,晚回應了!
我無法查看到他們的code,也詢問了相關開發人員,而剛好這也是前人的遺作,大多數的人也不太明白其裡面的邏輯/images/emoticon/emoticon20.gif

我要發表回答

立即登入回答