Re: Mysql Deadlock explanation
I remove duplicate client_id index.
But deadlock still exsist.
Yes we supply 'id' value on server side.
---cut---
140123 8:59:37
*** (1) TRANSACTION:
TRANSACTION 0 810056834, ACTIVE 0 sec, process no 7195, OS thread id 2371476336 inserting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1024, 5 row lock(s), undo log entries 2
MySQL thread id 1771067, query id 3912744865 n1-rest 192.168.3.65 secsys update
INSERT INTO session (client,reference,state_id,auth_id,created,updated,portal) VALUES (205985,'1e1f37cb-af72-471c-bf8c-f890ec18bff1',1,298993,'2014-01-23 08:59:37','2014-01-23 08:59:37',1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 603 page no 3127 n bits 208 index `idx_session_client` of table `security`.`session` trx id 0 810056834 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 0 810056833, ACTIVE 0 sec, process no 7195, OS thread id 2336070512 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1024, 5 row lock(s), undo log entries 2
MySQL thread id 1771086, query id 3912744866 n1-rest 192.168.3.65 secsys update
INSERT INTO session (client,reference,state_id,auth_id,created,updated,portal) VALUES (205984,'3c5b2ad7-e158-4d2a-ad8b-d751cb7745dc',1,298992,'2014-01-23 08:59:37','2014-01-23 08:59:37',1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 603 page no 3127 n bits 208 index `idx_session_client` of table `security`.`session` trx id 0 810056833 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 603 page no 3127 n bits 208 index `idx_session_client` of table `security`.`session` trx id 0 810056833 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
---cut---