Hi,
I'd really appreciate help with reading this db deadlock notice.
Essentially, I'd like to understand:
-what holds the lock that TX2 is waiting on index `token` of table
`eviltwin/user_sessions`? I thought it'd be TX1 (hence deadlock ?), yet
it appears that TX1 holds no locks....
-if the answer to the above cannot be found in attached report, is there
a setting I should turn on the server to get the necessary info next
time this deadlock occurs?
Our setup: MySQL 5, InnoDB, Repeatable Read
relevant columns in table user_sessions:
-id (PK)
-token (unique, nullable)
-serverSessionId (FK, nullable)
TX1 does:
-select for update on a given 'token'
-set serverSessionId to null
TX2 does:
-select for update on the same 'token'
-delete selected record
TX2 seems to start a little earlier, and succeed on 'select for update'.
TX1 then tries to do the same and is forced to wait on a lock. This I
understand.
But why does TX2 need to wait on a lock to deleted already 'selected for
update' user_session? Who's holding that lock?
thanks,
-nikita
*************************** 1. row ***************************
Status:
=====================================
070402 12:24:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 29 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 6313003, signal count 5099409
Mutex spin waits 819355967, rounds 1424470313, OS waits 4424501
RW-shared spins 1000685, OS waits 629116; RW-excl spins 760423, OS waits 175362
------------------------
LATEST DETECTED DEADLOCK
------------------------
070402 12:22:41
*** (1) TRANSACTION:
TRANSACTION 0 12012950, ACTIVE 0 sec, process no 7328, OS thread id 1161120096
starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216
MySQL thread id 93849, query id 1913636508 lf20.eviltwinstudios.net
192.168.100.163 eviltwin Sending data
select usersessio0_.id as id37_, usersessio0_.hibernateVersion as
hibernat2_37_, usersessio0_.serverSessionId as serverSe3_37_,
usersessio0_.userId as userId37_, usersessio0_.loginTime as loginTime37_,
usersessio0_.logoutTime as logoutTime37_, usersessio0_.boot as boot37_,
usersessio0_.token as token37_, usersessio0_.macAddressMD5 as macAddre9_37_,
usersessio0_.expirationTime as expirat10_37_, usersessio0_.creationDate as
creatio11_37_ from user_sessions usersessio0_ where
usersessio0_.token='82ff6193-1216-449a-9e33-5426fb8e10ef' for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 130062 n bits 144 index `PRIMARY` of table
`eviltwin/user_sessions` trx id 0 12012950 lock_mode X locks rec but not gap
waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 13; compact format; info bits
32
0: len 30; hex 37653530373061382d393537362d343363352d623138342d656266633763; asc
7e5070a8-9576-43c5-b184-ebfc7c;...(truncated); 1: len 6; hex 000000b74d95; asc
M ;; 2: len 7; hex 000000803c29ee; asc <) ;; 3: len 4; hex 80000001; asc
;; 4: len 30; hex 39373939383430622d323938372d346630382d613539342d663736303831;
asc 9799840b-2987-4f08-a594-f76081;...(truncated); 5: len 30; hex
34343431316237362d386663312d343332652d386332342d653837646433; asc
44411b76-8fc1-432e-8c24-e87dd3;...(truncated); 6: len 8; hex 80001241013060af; asc
A 0` ;; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 30; hex
38326666363139332d313231362d343439612d396533332d353432366662; asc
82ff6193-1216-449a-9e33-5426fb;...(truncated); 10: len 30; hex
626536376165633236613163636132616465666661616135373430646130; asc
be67aec26a1cca2adeffaaa5740da0;...(truncated); 11: len 8; hex 80001241014ee557;
asc A N W;; 12: len 8; hex 80001241013060af; asc A 0` ;;
*** (2) TRANSACTION:
TRANSACTION 0 12012949, ACTIVE 0 sec, process no 7328, OS thread id 1182153056
updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1216, undo log entries 3
MySQL thread id 93773, query id 1913636516 lf20.eviltwinstudios.net
192.168.100.163 eviltwin updating
delete from user_sessions where id='7e5070a8-9576-43c5-b184-ebfc7c288d69' and
hibernateVersion=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 130062 n bits 144 index `PRIMARY` of table
`eviltwin/user_sessions` trx id 0 12012949 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 13; compact format; info bits
32
0: len 30; hex 37653530373061382d393537362d343363352d623138342d656266633763; asc
7e5070a8-9576-43c5-b184-ebfc7c;...(truncated); 1: len 6; hex 000000b74d95; asc
M ;; 2: len 7; hex 000000803c29ee; asc <) ;; 3: len 4; hex 80000001; asc
;; 4: len 30; hex 39373939383430622d323938372d346630382d613539342d663736303831;
asc 9799840b-2987-4f08-a594-f76081;...(truncated); 5: len 30; hex
34343431316237362d386663312d343332652d386332342d653837646433; asc
44411b76-8fc1-432e-8c24-e87dd3;...(truncated); 6: len 8; hex 80001241013060af; asc
A 0` ;; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 30; hex
38326666363139332d313231362d343439612d396533332d353432366662; asc
82ff6193-1216-449a-9e33-5426fb;...(truncated); 10: len 30; hex
626536376165633236613163636132616465666661616135373430646130; asc
be67aec26a1cca2adeffaaa5740da0;...(truncated); 11: len 8; hex 80001241014ee557;
asc A N W;; 12: len 8; hex 80001241013060af; asc A 0` ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 130063 n bits 240 index `token` of table
`eviltwin/user_sessions` trx id 0 12012949 lock_mode X locks rec but not gap
waiting
Record lock, heap no 159 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 38326666363139332d313231362d343439612d396533332d353432366662;
asc 82ff6193-1216-449a-9e33-5426fb;...(truncated); 1: len 30; hex
37653530373061382d393537362d343363352d623138342d656266633763; asc
7e5070a8-9576-43c5-b184-ebfc7c;...(truncated);
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 12013755
Purge done for trx's n:o < 0 12013741 undo n:o < 0 0
History list length 12
Total number of lock structs in row lock hash table 0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
