Hi,
I managed to create a deadlock with multiple parallel INSERTs and UPDATEs:
LATEST DETECTED DEADLOCK
------------------------
090719 11:49:54
*** (1) TRANSACTION:
TRANSACTION 0 128441, ACTIVE 1 sec, OS thread id 1140 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, undo log entries 6
MySQL thread id 79, query id 3057 FLOELE-LATITUDE 192.168.1.102 root update
INSERT INTO news_feed_item (title, link, date, description, news_feed_id, item_id, description_clean) VALUES ('JoT 1265: Your iPhone is trying to you something.', '...')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `news/news_feed_item` trx id 0 128441 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 0 128443, ACTIVE 1 sec, OS thread id 2660 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 81, query id 3034 FLOELE-LATITUDE 192.168.1.102 root update
INSERT INTO news_feed_item (title, link, date, description, news_feed_id, item_id, description_clean) VALUES ('Page 24', '...'2008-03-20 00:00:00', 'Page 24', 47, '...', 'Page 24')
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `news/news_feed_item` trx id 0 128443 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1068 n bits 112 index `unique_read` of table `news/news_feed_item` trx id 0 128443 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000030; asc 0;; 1: len 30; hex 687474703a2f2f7777772e7667636174732e636f6d2f636f6d6963732f3f; asc
http://www.vgcats.com/comics/?;
.(truncated); 2: len 4; hex 8000000b; asc ;;
*** WE ROLL BACK TRANSACTION (2)
If I understand it correctly, T1 needs a TABLE LOCK, T2 already has a TABLE LOCK and then fails to request a RECORD LOCK. I have done some research, but I was unable to find information on why T2 cannot aquire a RECORD LOCK on a table, which it already has a TABLE LOCK for. Can anyone enlighten me? :)
(Note: I do NOT need a solution for the problem, I only want to understand it.)
Thanks in advance for hints,
Florian