mysql lockforinsert,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)