Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

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
share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.