deadlock issue. Why is transaction waiting for an S lock when it already has an X lock?
Hi,
I'm seeing a deadlock which is confusing when I look at the log. It says here that Transaction 2 is waiting for a shared lock on the primary index of table, that it the transaction already has an X lock on. Why is waiting for an S lock, when it already has an X lock? Does it not realize this, or am I interpreting this incorrectly?
Thanks,
Rich
=====================================
050103 14:03:45 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 157019, signal count 153045
Mutex spin waits 5445310, rounds 6561494, OS waits 63120
RW-shared spins 81295, OS waits 26826; RW-excl spins 187823, OS waits 51820
------------------------
LATEST DETECTED DEADLOCK
------------------------
050103 14:03:31
*** (1) TRANSACTION:
TRANSACTION 0 149875076, ACTIVE 0 sec, process no 9207, OS thread id 94702512 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 1084, query id 776283 xxxxxx xxxxx xxxx Sending data
SELECT id,name,reported_name,description,software_version,catalog_version,owner_company_id,selling_distributor_company_id,servicing_distributor_company_id,op_contract_id,service_term_start_date,service_term_end_date,location_contact_id,location_company,model,mac_address,serial_number,hard_disk_id,ha
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 8462 n bits 88 index `PRIMARY` of table `xxxxxx_jukebox/jukeboxes` trx id 0 149875076 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 51; 1-byte offs FALSE; info bits 0
0: len 4; hex 80000114; asc ;; 1: len 1; hex 80; asc ;; 2: len 6; hex 000008eee90f; asc ;; 3: len 7; hex 00000080181851; asc Q;; 4: len 12; hex 6a756b65626f785f6e616d65; asc jukebox_name;; 5: len 12; hex 6a756b65626f785f6e616d65; asc jukebox_name;; 6: SQL NULL, size 0 ; 7: len 5; hex 332e333143; asc 3.31C;; 8: len 4; hex 80000042; asc B;; 9: len 4; hex 8000001a; asc ;; 10: SQL NULL, size 4 ; 11: SQL NULL, size 4 ; 12: len 4; hex 8000002b; asc +;; 13: len 8; hex 8000123a2968bb5f; asc :)h _;; 14: len 8; hex 80001245cda42f5f; asc E /_;; 15: len 4; hex 8000013b; asc ;;; 16: SQL NULL, size 0 ; 17: len 5; hex 4e53442d31; asc NSD-1;; 18: len 17; hex 30302d36302d30432d30302d38312d4437; asc 00-60-0C-00-81-D7;; 19: SQL NULL, size 0 ; 20: SQL NULL, size 4 ; 21: len 5; hex 3130303234; asc 10024;; 22: len 1; hex 02; asc ;; 23: len 4; hex 00000000; asc ;; 24: len 1; hex 80; asc ;; 25: len 1; hex 80; asc ;; 26: len 1; hex 80; asc ;; 27: len 4; hex 80000000; asc ;; 28: SQL NULL, size 8 ; 29: SQL NULL, size 8 ; 30: SQL NULL, size 8 ; 31: SQL NULL, size 8 ; 32: SQL NULL, size 8 ; 33: SQL NULL, size 8 ; 34: len 8; hex 8000123c47466ab4; asc <GFj ;; 35: len 8; hex 8000123c47466a73; asc <GFjs;; 36: len 8; hex 8000123c47466a74; asc <GFjt;; 37: len 8; hex 8000123c47466a73; asc <GFjs;; 38: len 8; hex 8000123c47466a74; asc <GFjt;; 39: len 8; hex 8000123c47466a73; asc <GFjs;; 40: len 4; hex 80000000; asc ;; 41: len 0; hex ; asc ;; 42: len 5; hex 5452414345; asc TRACE;; 43: len 1; hex 01; asc ;; 44: len 30; hex 54686973206a756b65626f7820697320776f726b696e6720636f72726563; asc This jukebox is working correc;...(truncated); 45: len 8; hex 8000123c47467f8e; asc <GF ;; 46: len 8; hex 8000123a28fd5172; asc :( Qr;; 47: len 4; hex 41d996f4; asc A ;; 48: len 8; hex 8000123a28fefab9; asc :( ;; 49: len 1; hex 03; asc ;; 50: len 4; hex 80000000; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 149875052, ACTIVE 1 sec, process no 9207, OS thread id 9194416 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 386, query id 776097 xxxxxx xxxxx xxxx update
INSERT INTO xxxxxx_log.music_download_detail SET id=NULL, jukebox_id=276, mac_address='00-60-0C-00-81-D7', hard_disk_id=NULL, company_id=26, albums_downloaded=0, songs_downloaded=0, download_start_time='2004-10-05 09:32:34', download_end_time='2004-10-05 09:32:34'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 8462 n bits 88 index `PRIMARY` of table `xxxxxx_jukebox/jukeboxes` trx id 0 149875052 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 51; 1-byte offs FALSE; info bits 0
0: len 4; hex 80000114; asc ;; 1: len 1; hex 80; asc ;; 2: len 6; hex 000008eee90f; asc ;; 3: len 7; hex 00000080181851; asc Q;; 4: len 12; hex 6a756b65626f785f6e616d65; asc jukebox_name;; 5: len 12; hex 6a756b65626f785f6e616d65; asc jukebox_name;; 6: SQL NULL, size 0 ; 7: len 5; hex 332e333143; asc 3.31C;; 8: len 4; hex 80000042; asc B;; 9: len 4; hex 8000001a; asc ;; 10: SQL NULL, size 4 ; 11: SQL NULL, size 4 ; 12: len 4; hex 8000002b; asc +;; 13: len 8; hex 8000123a2968bb5f; asc :)h _;; 14: len 8; hex 80001245cda42f5f; asc E /_;; 15: len 4; hex 8000013b; asc ;;; 16: SQL NULL, size 0 ; 17: len 5; hex 4e53442d31; asc NSD-1;; 18: len 17; hex 30302d36302d30432d30302d38312d4437; asc 00-60-0C-00-81-D7;; 19: SQL NULL, size 0 ; 20: SQL NULL, size 4 ; 21: len 5; hex 3130303234; asc 10024;; 22: len 1; hex 02; asc ;; 23: len 4; hex 00000000; asc ;; 24: len 1; hex 80; asc ;; 25: len 1; hex 80; asc ;; 26: len 1; hex 80; asc ;; 27: len 4; hex 80000000; asc ;; 28: SQL NULL, size 8 ; 29: SQL NULL, size 8 ; 30: SQL NULL, size 8 ; 31: SQL NULL, size 8 ; 32: SQL NULL, size 8 ; 33: SQL NULL, size 8 ; 34: len 8; hex 8000123c47466ab4; asc <GFj ;; 35: len 8; hex 8000123c47466a73; asc <GFjs;; 36: len 8; hex 8000123c47466a74; asc <GFjt;; 37: len 8; hex 8000123c47466a73; asc <GFjs;; 38: len 8; hex 8000123c47466a74; asc <GFjt;; 39: len 8; hex 8000123c47466a73; asc <GFjs;; 40: len 4; hex 80000000; asc ;; 41: len 0; hex ; asc ;; 42: len 5; hex 5452414345; asc TRACE;; 43: len 1; hex 01; asc ;; 44: len 30; hex 54686973206a756b65626f7820697320776f726b696e6720636f72726563; asc This jukebox is working correc;...(truncated); 45: len 8; hex 8000123c47467f8e; asc <GF ;; 46: len 8; hex 8000123a28fd5172; asc :( Qr;; 47: len 4; hex 41d996f4; asc A ;; 48: len 8; hex 8000123a28fefab9; asc :( ;; 49: len 1; hex 03; asc ;; 50: len 4; hex 80000000; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 8462 n bits 88 index `PRIMARY` of table `xxxxxx_jukebox/jukeboxes` trx id 0 149875052 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 51; 1-byte offs FALSE; info bits 0
0: len 4; hex 80000114; asc ;; 1: len 1; hex 80; asc ;; 2: len 6; hex 000008eee90f; asc ;; 3: len 7; hex 00000080181851; asc Q;; 4: len 12; hex 6a756b65626f785f6e616d65; asc jukebox_name;; 5: len 12; hex 6a756b65626f785f6e616d65; asc jukebox_name;; 6: SQL NULL, size 0 ; 7: len 5; hex 332e333143; asc 3.31C;; 8: len 4; hex 80000042; asc B;; 9: len 4; hex 8000001a; asc ;; 10: SQL NULL, size 4 ; 11: SQL NULL, size 4 ; 12: len 4; hex 8000002b; asc +;; 13: len 8; hex 8000123a2968bb5f; asc :)h _;; 14: len 8; hex 80001245cda42f5f; asc E /_;; 15: len 4; hex 8000013b; asc ;;; 16: SQL NULL, size 0 ; 17: len 5; hex 4e53442d31; asc NSD-1;; 18: len 17; hex 30302d36302d30432d30302d38312d4437; asc 00-60-0C-00-81-D7;; 19: SQL NULL, size 0 ; 20: SQL NULL, size 4 ; 21: len 5; hex 3130303234; asc 10024;; 22: len 1; hex 02; asc ;; 23: len 4; hex 00000000; asc ;; 24: len 1; hex 80; asc ;; 25: len 1; hex 80; asc ;; 26: len 1; hex 80; asc ;; 27: len 4; hex 80000000; asc ;; 28: SQL NULL, size 8 ; 29: SQL NULL, size 8 ; 30: SQL NULL, size 8 ; 31: SQL NULL, size 8 ; 32: SQL NULL, size 8 ; 33: SQL NULL, size 8 ; 34: len 8; hex 8000123c47466ab4; asc <GFj ;; 35: len 8; hex 8000123c47466a73; asc <GFjs;; 36: len 8; hex 8000123c47466a74; asc <GFjt;; 37: len 8; hex 8000123c47466a73; asc <GFjs;; 38: len 8; hex 8000123c47466a74; asc <GFjt;; 39: len 8; hex 8000123c47466a73; asc <GFjs;; 40: len 4; hex 80000000; asc ;; 41: len 0; hex ; asc ;; 42: len 5; hex 5452414345; asc TRACE;; 43: len 1; hex 01; asc ;; 44: len 30; hex 54686973206a756b65626f7820697320776f726b696e6720636f72726563; asc This jukebox is working correc;...(truncated); 45: len 8; hex 8000123c47467f8e; asc <GF ;; 46: len 8; hex 8000123a28fd5172; asc :( Qr;; 47: len 4; hex 41d996f4; asc A ;; 48: len 8; hex 8000123a28fefab9; asc :( ;; 49: len 1; hex 03; asc ;; 50: len 4; hex 80000000; asc ;;
*** WE ROLL BACK TRANSACTION (1)