Mysql Deadlock explanation
Posted by:
Akex -
Date: January 19, 2014 10:14PM
I have a problem with reading innodb monitor output. It's seem that problem in gap lock on FK_SESSION_CLIENT_ID index. But i can't simulate this situation on dev host.
Transaction isolation level is REPEATABLE READ.
The question is, what the cause if deadlock?
--innodb monitor output code --
LATEST DETECTED DEADLOCK
------------------------
140108 14:11:44
*** (1) TRANSACTION:
TRANSACTION 0 230749560, ACTIVE 0 sec, process no 24134, OS thread id 2816273264 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 209142, query id 1364070033 n1-rest 192.168.1.71 secsys update
INSERT INTO session (client,reference,state_id,auth_id,created,updated,portal) VALUES (196609,'ba205dd8-1168-4218-8095-af905d3c4f55',1,285204,'2014-01-08 14:16:16','2014-01-08 14:16:16',1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 95760 n bits 552 index `FK_SESSION_CLIENT_ID` of table `security`.`session` trx id 0 230749560 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 230749561, ACTIVE 0 sec, process no 24134, OS thread id 2929900400 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 209137, query id 1364070031 n1-rest 192.168.1.71 secsys update
INSERT INTO session (client,reference,state_id,auth_id,created,updated,portal) VALUES (196610,'a443acaf-c613-4d64-980c-809151b7a7bf',1,285205,'2014-01-08 14:16:16','2014-01-08 14:16:16',1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 95760 n bits 552 index `FK_SESSION_CLIENT_ID` of table `security`.`session` trx id 0 230749561 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 0 page no 95760 n bits 552 index `FK_SESSION_CLIENT_ID` of table `security`.`session` trx id 0 230749561 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)
-- --
Table Structure
--- table structure ---
CREATE TABLE session (
ID bigint(20) AUTO_INCREMENT NOT NULL,
CLIENT bigint(20) NULL,
CLOSED datetime NULL,
CREATED timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
REFERENCE varchar(255) NULL,
STATE_ID bigint(20) NULL,
AUTH_ID bigint(20) NULL,
UPDATED timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PORTAL int(11) NULL DEFAULT '1',
PRIMARY KEY(ID)
)
ENGINE = InnoDB
GO
ALTER TABLE session
ADD CONSTRAINT FK_SESSION_STATE_ID
FOREIGN KEY(STATE_ID)
REFERENCES sessionstate(ID)
ON DELETE RESTRICT
ON UPDATE RESTRICT
GO
ALTER TABLE session
ADD CONSTRAINT FK_SESSION_AUTH_ID
FOREIGN KEY(AUTH_ID)
REFERENCES auth(ID)
ON DELETE RESTRICT
ON UPDATE RESTRICT
GO
CREATE INDEX FK_SESSION_AUTH_ID
ON session(AUTH_ID)
GO
CREATE INDEX FK_SESSION_CLIENT_ID
ON session(CLIENT)
GO
CREATE INDEX FK_SESSION_REFERENCE
ON session(REFERENCE)
GO
CREATE INDEX FK_SESSION_STATE_ID
ON session(STATE_ID)
GO
CREATE INDEX idx_sesson_portal
ON session(PORTAL)
GO
CREATE INDEX idx_session_client
ON session(CLIENT)
GO
--- end table structure ---