MySQL Forums
Forum List  »  General

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 ---

Options: ReplyQuote


Subject
Written By
Posted
Mysql Deadlock explanation
January 19, 2014 10:14PM
January 20, 2014 06:54PM
January 20, 2014 09:06PM
January 21, 2014 01:54AM
January 22, 2014 11:50PM
January 23, 2014 12:10AM
February 03, 2014 09:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.