MySQL Bugs: #24188: show engine innodb status

Bug #24188	show engine innodb status - deadlock report inaccurate?
Submitted:	10 Nov 2006 13:46	Modified:	10 Nov 2006 15:32
Reporter:	Brice Figureau	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.26	OS:	Linux (linux debian sid)
Assigned to:		CPU Architecture:	Any
Tags:	deadlock, innodb

Description:
I'm observing a strange Innodb deadlocks that happens about 2 or 3 times per day.

I have one process that "insert...select" from table game_rank (see details below) every 5s, while this innodb table is constantly updated:

Table definition:
CREATE TABLE `game_rank` (
`playerID` int(10) unsigned NOT NULL,
`game` enum('GF','DS','MA','QN','TT','MM','PC','TT_us','TT_eu','TT_ch','TT_u1','TT_u2','TT_u3','TT_2p','TT_mp') NOT NULL default 'GF',
`nbgames` int(11) NOT NULL default '0',
`rankscore` double NOT NULL default '0',
`lastgame` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`karma` int(11) NOT NULL default '50',
PRIMARY KEY (`playerID`,`game`),
KEY `idx` (`game`,`lastgame`)
) ENGINE=InnoDB

See the transaction information in the deadlock report.
The whole deadlock output is attached to the report.

I know that insert...select runs in Share Lock so that replication can work, it is certainly why it deadlocks.

But what is strange is the following:
1) the deadlock report says basically, that transaction 2 is waiting to grab lock for an index (idx) that is not locked/touched by transaction 1.

2) the deadlock report says "undo log entries 1" which means one request have been issued in this transaction, which seems strange since the session is autocommit=1.

Basically I see two possible issues:
1) deadlock report is inaccurate: it doesn't show all the locks involved in the deadlock.
or
2) there are more than 2 transactions involved, but deadlock reports only 2 transactions, thus leaving me blind regarding the exact cause of the problem.

How to repeat:
I fear this is impossible as this happens only on a production server. I wasn't able to explicitely reproduce it on a test server.

Suggested fix:
I suppose that running without binlog will solve the issue (remove the S lock from the big select), but I really need binlog enabled to perform point in time recovery in case of real problems, or replication.

show engine innodb status - deadlock output

Attachment: trn.txt (text/plain), 2.51 KiB.

Sorry, but I do not see any bug here. At least, please, show all the results of SHOW INNODB STATUSD/SHOW PROCESSLIST that proves there are more than 2 transactions involved...

I see the following:

1. Transaction 2 set X lock on PRIMARY index record(s) in www_dow/game_rank because of UPDATE

2. Transaction 1 set S lock on table (not shown), and wants S lock on PRIMARY index record(s) of www_dow/game_rank, so it should wait...

3. Transaction 2 wants X lock on some record in index `idx` of table `www_dow/game_rank, (to UPDATE it), but can not get it because of S lock at table level.

We have a deadlock. I see no bugs here, really.

Thank you for the fast anwser. I'm sorry for the noise.

I think it would be great if the deadlock output could show _all_ the locks involved, as you did in your comment.

About more than 2 transactions, that was just a shot in the dark, as I didn't understood the whole table was S locked by the transaction 1. I was wondering why it couldn't grab the lock on idx, so I thought something else was locking it.