Transaction already has an X lock, but is waiting for an S l
Hi,
I'm using JBoss 3.2.6. We're experiecing deadlock issues with mysql/innodb. Good there be something I'm missing with a jboss configuration that could be causing this? I don't think there is, but I thought I would cover all the bases.
I'm using version Mysql 4.0.22. The log is from a redhat box...
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 a table, one that it already has an X lock on.
Why is it waiting for an S lock, when it already has an X lock? Does it not realize this, or am I interpreting this incorrectly?
We were previously experiencing deadlocks because we were not doing a select for update on a parent row, and when we inserted rows into a child row, we were getting deadlocks because another thread was ahead of that thread in getting an exclusive lock.
We attempted to solve this by first doing a select for update on the parent row, before trying to insert rows into the child table. We thought this would solve our deadlock in this case because we have an exclusive lock, and the request for a shared lock when in inserting into the child table would be granted immediately. These deadlocks below are showing that this is not the case.
What else can we do, to avoid this scenario?
=====================================
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)