Mysql – How to analyse innodb status on deadlock in insert Query

deadlockinnodbMySQL

I have a dead lock which i have posted here. I managed to get the innodb status from my server. Here is the Innodb status. 

OS WAIT ARRAY INFO: reservation count 247864, signal count 247470
Mutex spin waits 0, rounds 1195699, OS waits 5639
RW-shared spins 378430, OS waits 185895; RW-excl spins 96808, OS waits 54060
------------------------
LATEST DETECTED DEADLOCK
------------------------
121102 14:20:43
*** (1) TRANSACTION:
TRANSACTION 0 4908076, ACTIVE 0 sec, process no 14592, OS thread id 2898492272 inserting
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 5
MySQL thread id 49749, query id 8135639 192.168.4.4 phactuser update
INSERT INTO domain_access (nid, gid, realm) VALUES ('46084', '0', 'domain_site'), ('46084', '2', 'domain_id')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 851010 n bits 432 index "PRIMARY" of table "greenland_deadlock"."domain_access" trx id 0 4908076 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 4908078, ACTIVE 0 sec, process no 14592, OS thread id 2904111984 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 5
MySQL thread id 49753, query id 8135640 192.168.4.4 phactuser update
INSERT INTO domain_access (nid, gid, realm) VALUES ('46085', '0', 'domain_site'), ('46085', '2', 'domain_id')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 851010 n bits 432 index "PRIMARY" of table "greenland_deadlock"."domain_access" trx id 0 4908078 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 851010 n bits 432 index "PRIMARY" of table "greenland_deadlock"."domain_access" trx id 0 4908078 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)
------------
TRANSACTIONS
------------
Trx id counter 0 4913696
Purge done for trx's n:o < 0 4913389 undo n:o < 0 0
History list length 164
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 14592, OS thread id 2900900720
MySQL thread id 49755, query id 8183169 192.168.4.4 phactuser
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 4913695, ACTIVE 0 sec, process no 14592, OS thread id 2898492272 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 1 row lock(s), undo log entries 3
MySQL thread id 49749, query id 8183170 192.168.4.4 phactuser update
INSERT INTO field_data_body (entity_type, entity_id, revision_id, bundle, delta, language, body_value, body_summary, body_format) VALUES ('node', '48879', '49412', 'article', '0', 'und', 'Dummy body', NULL, 'filtered_html')
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1316717 OS file reads, 1059070 OS file writes, 855391 OS fsyncs
9.66 reads/s, 16384 avg bytes/read, 81.31 writes/s, 80.97 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 11, seg size 13,
25808 inserts, 25808 merged recs, 4564 merges
Hash table size 34679, node heap has 7 buffer(s)
3456.51 hash searches/s, 3090.30 non-hash searches/s
---
LOG
---
Log sequence number 19 1800780662
Log flushed up to   19 1800779562
Last checkpoint at  19 1798594112
0 pending log writes, 0 pending chkp writes
823951 log i/o's done, 80.31 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 55055266; in additional pool allocated 1048576
Dictionary memory allocated 31469720
Buffer pool size   512
Free buffers       1
Database pages     504
Modified db pages  160
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1795101, created 148132, written 477518
9.66 reads/s, 4.67 creates/s, 3.33 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 14592, id 2933046128, state: sleeping
Number of rows inserted 2104748, updated 574005, deleted 440358, read 58938973
862.05 inserts/s, 78.64 updates/s, 156.61 deletes/s, 470.18 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

The exact structure of the table is 

  +-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| nid   | int(10) unsigned | NO   | PRI | 0       |       |
| gid   | int(10) unsigned | NO   | PRI | 0       |       |
| realm | varchar(255)     | NO   | PRI |         |       |
+-------+------------------+------+-----+---------+-------+

I believe in the fact that we insert 0 (the default value) in the {domain_access}.gid, {domain_access}.nid for a short period of time during save. As a consequence, MySQL has to lock the beginning of index attached to the {domain_access}.gid, {domain_access}.nid unique key until the transaction ends. This increases the window during which a deadlock is possible.

Is my assumption correct ? In this case How two transaction holds the resource of each other in a insert Query. ?

Best Answer

OBSERVATION #1

The locking of the PRIMARY KEY is much more involved that you think. It has nothing to do with the default values because the three columns define the uniqueness of the key.

OBSERVATION #2

Your PRIMARY KEY has three columns. This makes great demands on the gen_clust_index, the Clustered Index. The entire row is basically stored in the index. The locking has to serialize the creation of rowids.

OBSERVATION #3

With the large column (realm) as part of the PRIMARY KEY, this makes for a lot of BTREE operations. I say this because the max row size is 263 bytes. Assuming every row is 263 bytes, and the size of a data page is 16K, this makes for each BTREE node holding about 255 keys before splitting. I recommend storing the MD5 or SHA1 of the realm as follows:

ALTER TABLE domain_access ADD realmkey CHAR(40);
CREATE TABLE domain_access_new
(
    nid int(10) unsigned,
    gid int(10) unsigned,
    realmkey char(40),
    realm varchar(255),
    PRIMARY KEY (nid,gid,realmkey)
    KEY (realm)
) ENGINE=InnoDB;
INSERT INTO domain_access_new
SELECT nid,gid,SHA1(realm),realm FROM domain_access;
ALTER TABLE domain_access RENAME domain_access_old;
ALTER TABLE domain_access_new RENAME domain_access;
DROP TABLE domain_access_old;

With this, your PRIMARY KEY would be 48 bytes instead of 263. You should look further into make the PRIMARY KEY even smaller.

EPILOGUE

Locking of the PRIMARY KEY must occur for INSERTs, UPDATEs, and DELETEs. I remember a hefty deadlocking exercise I went through with RedBlueThing

Related Question