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

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.