Hi
Can somebody help me to figure out why this deadlock happen?
Thanks
=====================================
050405 9:12:17 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 50 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5602, signal count 5593
Mutex spin waits 85361, rounds 152084, OS waits 1557
RW-shared spins 6265, OS waits 2377; RW-excl spins 6234, OS waits 1319
------------------------
LATEST DETECTED DEADLOCK
------------------------
050405 9:11:07
*** (1) TRANSACTION:
TRANSACTION 0 9306677, ACTIVE 0 sec, OS thread id 820 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 39 lock struct(s), heap size 5504, undo log entries 474
MySQL thread id 847, query id 2620165 localhost 127.0.0.1 ales updating
delete from STATEVALUE where STATEVALUE_ID=21102
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 6334 n bits 288 index `PRIMARY` of table `pco/statevalue` trx id 0 9306677 lock_mode X locks rec but not gap
waiting
Record lock, heap no 99 PHYSICAL RECORD: n_fields 8; 1-byte offs TRUE; info bits 0
0: len 8; hex 800000000000526e; asc ? Rn;; 1: len 6; hex 0000008e0253; asc ? S;; 2: len 7; hex 0000000a401a14; asc @ ;; 3: len
10; hex 466c6f617456616c7565; asc FloatValue;; 4: len 8; hex 8000010311328b22; asc ? 2?";; 5: len 8; hex 0000000000003740; asc 7@;;
6: SQL NULL, size 4 ; 7: SQL NULL, size 8 ;
*** (2) TRANSACTION:
TRANSACTION 0 9306707, ACTIVE 0 sec, OS thread id 1964 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
19 lock struct(s), heap size 2496, undo log entries 6
MySQL thread id 848, query id 2620166 localhost 127.0.0.1 ales Updating
update STATEVALUE set STATEVARIABLE_ID=null where STATEVARIABLE_ID=3 and STATEVALUE_ID=21043
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 6334 n bits 288 index `PRIMARY` of table `pco/statevalue` trx id 0 9306707 lock_mode X locks rec but not gap
Record lock, heap no 99 PHYSICAL RECORD: n_fields 8; 1-byte offs TRUE; info bits 0
0: len 8; hex 800000000000526e; asc ? Rn;; 1: len 6; hex 0000008e0253; asc ? S;; 2: len 7; hex 0000000a401a14; asc @ ;; 3: len
10; hex 466c6f617456616c7565; asc FloatValue;; 4: len 8; hex 8000010311328b22; asc ? 2?";; 5: len 8; hex 0000000000003740; asc 7@;;
6: SQL NULL, size 4 ; 7: SQL NULL, size 8 ;
Record lock, heap no 105 PHYSICAL RECORD: n_fields 8; 1-byte offs TRUE; info bits 0
0: len 8; hex 8000000000005287; asc ? R?;; 1: len 6; hex 0000008e0253; asc ? S;; 2: len 7; hex 0000000a4019d7; asc @ ?;; 3: len
10; hex 466c6f617456616c7565; asc FloatValue;; 4: len 8; hex 800001031132903c; asc ? 2 <;; 5: len 8; hex 0000000000003840; asc 8@;;
6: SQL NULL, size 4 ; 7: SQL NULL, size 8 ;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 6334 n bits 288 index `PRIMARY` of table `pco/statevalue` trx id 0 9306707 lock_mode X locks rec but not gap
waiting
Record lock, heap no 84 PHYSICAL RECORD: n_fields 8; 1-byte offs TRUE; info bits 32
0: len 8; hex 8000000000005233; asc ? R3;; 1: len 6; hex 0000008e0235; asc ? 5;; 2: len 7; hex 0000000a572804; asc W( ;; 3: len
10; hex 466c6f617456616c7565; asc FloatValue;; 4: len 8; hex 8000010311328337; asc ? 2?7;; 5: len 8; hex 0000000000003940; asc 9@;;
6: SQL NULL, size 4 ; 7: len 8; hex 8000000000000003; asc ? ;;
*** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 0 9307587 Purge done for trx's n:o < 0 9307587 undo n:o < 0 0 History list length 4 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 1968 MySQL thread id 853, query id 2630752 localhost 127.0.0.1 root show innodb status ---TRANSACTION 0 9281670, not started, OS thread id 2124 MySQL thread id 811, query id 2333394 localhost 127.0.0.1 root -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 579 OS file reads, 99094 OS file writes, 36407 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 8.40 writes/s, 4.30 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 0, seg size 2, is empty Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 290627, used cells 47007, node heap has 51 buffer(s) 86.82 hash searches/s, 139.08 non-hash searches/s --- LOG --- Log sequence number 5 237197555 Log flushed up to 5 237197555 Last checkpoint at 5 237197555 0 pending log writes, 0 pending chkp writes 32252 log i/o's done, 4.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 82291570; in additional pool allocated 481664 Buffer pool size 4480 Free buffers 7 Database pages 4422 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 569, created 5987, written 64601 0.00 reads/s, 0.02 creates/s, 4.24 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread id 1048, state: waiting for server activity Number of rows inserted 998440, updated 550184, deleted 206683, read 6631921 18.48 inserts/s, 23.34 updates/s, 4.78 deletes/s, 45.90 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
