deadlock issue. Why is transaction waiting for an S lock when it already has an X lock?
Posted by: Richard Cook
Date: January 03, 2005 05:49PM
Date: January 03, 2005 05:49PM
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)
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)
Subject
Views
Written By
Posted
deadlock issue. Why is transaction waiting for an S lock when it already has an X lock?
3889
January 03, 2005 05:49PM
2738
January 03, 2005 10:32PM
2713
January 10, 2005 05:00PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.