MySQL Transaktionsübegreifendes Locking?

Test-Tomato

Grünschnabel
Hallo zusammen,

ich habe zwei parallel Transaktionen und MySQL lockt immer neue Datensätze die noch nicht committet wurden.
Also Transaktion A fügt unter anderem neue Datensätze ein und Transaktion B möchte dann auf die neuen Datensätze einen Lock bekommen, warum? Die neuen Datensätze sind doch noch nicht committet warum also einen Lock darauf holen obwohl die Daten von Transaktion A nicht mit dem Update von Transaktion B zusammen hängen wodurch dann bei vielen parallelen Aktionen Deadlocks auftreten.

IsolationsLevel: READ COMMITTED

innodb_locks_unsafe_for_binlog=0
innodb_table_locks=1

show engine innodb status:
Code:
*** (1) TRANSACTION:
TRANSACTION 0 50259832, ACTIVE 0 sec, OS thread id 6060 updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 41 lock struct(s), heap size 2496, undo log entries 178
MySQL thread id 19, query id 117258 localhost 127.0.0.1 root Updating
update sometable set y_id='c1157aa0-f4a3-44ff-a1b0-3e48751c25b3', IDX=0 where ID='6f6e8db9-a939-4b96-a00a-bd8f4e0de145'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 149052 n bits 208 index `INDEX_Y_ID` of table `dbschema/sometable` trx id 0 50259832 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 74 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 63323433343062652d313164382d346530392d393463302d386433343064; asc c24340be-11d8-4e09-94c0-8d340d;...(truncated); 1: len 30; hex 30626534646235332d353030652d343030642d393963622d353564353333; asc 0be4db53-500e-400d-99cb-55d533;...(truncated);

*** (2) TRANSACTION:
TRANSACTION 0 50259831, ACTIVE 0 sec, OS thread id 4388 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
39 lock struct(s), heap size 2496, undo log entries 182
MySQL thread id 17, query id 117264 localhost 127.0.0.1 root Updating
update sometable set y_id='bf63ab58-c88d-4ee3-9632-1483fd5b8f07', IDX=0 where ID='4618fa6a-e4bc-4371-a077-71fdc49b1ecd'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 149052 n bits 200 index `INDEX_Y_ID` of table `dbschema/sometable` trx id 0 50259831 lock_mode X locks gap before rec
Record lock, heap no 74 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 63323433343062652d313164382d346530392d393463302d386433343064; asc c24340be-11d8-4e09-94c0-8d340d;...(truncated); 1: len 30; hex 30626534646235332d353030652d343030642d393963622d353564353333; asc 0be4db53-500e-400d-99cb-55d533;...(truncated);

Record lock, heap no 82 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 64353761346465612d353333362d343966362d386337612d313965646566; asc d57a4dea-5336-49f6-8c7a-19edef;...(truncated); 1: len 30; hex 35666361343730362d393337352d346239312d383932612d373937383631; asc 5fca4706-9375-4b91-892a-797861;...(truncated);

Record lock, heap no 86 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 65326266663862322d343836392d343537622d393736352d386361363362; asc e2bff8b2-4869-457b-9765-8ca63b;...(truncated); 1: len 30; hex 33396435396235612d383039322d346533612d623532312d643235373763; asc 39d59b5a-8092-4e3a-b521-d2577c;...(truncated);

Record lock, heap no 116 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 36336330316461632d313030382d343637382d383566302d613766346335; asc 63c01dac-1008-4678-85f0-a7f4c5;...(truncated); 1: len 30; hex 33383237303635392d643531372d346166342d613433632d663733316162; asc 38270659-d517-4af4-a43c-f731ab;...(truncated);

Record lock, heap no 134 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 64643938653739642d333737312d346261662d393435352d333165393561; asc dd98e79d-3771-4baf-9455-31e95a;...(truncated); 1: len 30; hex 31653432633564362d336239632d346263332d623530362d366338313935; asc 1e42c5d6-3b9c-4bc3-b506-6c8195;...(truncated);

Record lock, heap no 136 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 64643938653739642d333737312d346261662d393435352d333165393561; asc dd98e79d-3771-4baf-9455-31e95a;...(truncated); 1: len 30; hex 35343261613631392d316434662d346633662d383264302d386264333961; asc 542aa619-1d4f-4f3f-82d0-8bd39a;...(truncated);

Record lock, heap no 138 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 64353339623235392d666365352d343466662d393265612d346334383534; asc d539b259-fce5-44ff-92ea-4c4854;...(truncated); 1: len 30; hex 65666666653830382d626137392d343362642d623638312d646338366533; asc efffe808-ba79-43bd-b681-dc86e3;...(truncated);

Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 64353339623235392d666365352d343466662d393265612d346334383534; asc d539b259-fce5-44ff-92ea-4c4854;...(truncated); 1: len 30; hex 64383231393336332d326136352d343436312d386266662d346663343062; asc d8219363-2a65-4461-8bff-4fc40b;...(truncated);

Record lock, heap no 140 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 36323666383333392d323930372d343339302d393061632d643036643334; asc 626f8339-2907-4390-90ac-d06d34;...(truncated); 1: len 30; hex 33656163666638622d393563632d346133632d386162332d333735336533; asc 3eacff8b-95cc-4a3c-8ab3-3753e3;...(truncated);

Record lock, heap no 141 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 36323666383333392d323930372d343339302d393061632d643036643334; asc 626f8339-2907-4390-90ac-d06d34;...(truncated); 1: len 30; hex 61396662303931392d616632642d343130362d393464352d343338333938; asc a9fb0919-af2d-4106-94d5-438398;...(truncated);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 149052 n bits 208 index `INDEX_Y_ID` of table `dbschema/sometable` trx id 0 50259831 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 74 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 63323433343062652d313164382d346530392d393463302d386433343064; asc c24340be-11d8-4e09-94c0-8d340d;...(truncated); 1: len 30; hex 30626534646235332d353030652d343030642d393963622d353564353333; asc 0be4db53-500e-400d-99cb-55d533;...(truncated);

*** WE ROLL BACK TRANSACTION (1)

Für Tipps bin ich sehr Dankbar!

P.S.: Mit innodb_locks_unsafe_for_binlog=1 habe ich bisher keinen Deadlock produzieren können, da wohl nur dann die Datensätze gelockt werden die mit der where condition übereinstimmen.
 
Zuletzt bearbeitet:
Zurück