I'm using MySQL 4.0.15 standard - InnoDB and I got a deadlock. I don't see why !
How Can I find the reason ? It seems to occurs on delete statments.
Regards, Christophe
Here is my InnoDB status :
=====================================
Per second averages calculated from the last 51 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 539, signal count 189
Mutex spin waits 800, rounds 10240, OS waits 328
RW-shared spins 370, OS waits 188; RW-excl spins 35, OS waits 23
------------------------
LATEST DETECTED DEADLOCK
------------------------
031104 21:39:32
*** (1) TRANSACTION:
TRANSACTION 0 119492, ACTIVE 7 sec, process no 3109, OS thread id 188446 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 27 lock struct(s), heap size 2496, undo log entries 42
MySQL thread id 38, query id 12022 localhost 127.0.0.1 root updating
DELETE FROM SLIDE_REVISION_PREDECESSOR WHERE REVISION_ID = 101
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 192 n bits 72 table test/SLIDE_REVISION_PREDECESSOR index PRIMARY trx id 0 119492 lock_mode X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;;
*** (2) TRANSACTION:
TRANSACTION 0 120070, ACTIVE 4 sec, process no 3095, OS thread id 131089 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
43 lock struct(s), heap size 5504, undo log entries 21
MySQL thread id 24, query id 12050 localhost 127.0.0.1 root Updating
update SLIDE_URI set PARENT_URI_ID = 1 where URI_ID = 101
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 192 n bits 72 table test/SLIDE_REVISION_PREDECESSOR index PRIMARY trx id 0 120070 lock mode S
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 45 n bits 312 table test/SLIDE_URI index PRIMARY trx id 0 120070 lock_mode X locks rec but not gap waiting
Record lock, heap no 245 RECORD: info bits 0 0: len 4; hex 80000065; asc ...e;; 1: len 6; hex 00000001d2c4; asc ......;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 0 120984
Purge done for trx's n:o < 0 120974 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 120878, not started, process no 3112, OS thread id 200720
MySQL thread id 41, query id 16537 localhost 127.0.0.1 root
---TRANSACTION 0 120764, not started, process no 3111, OS thread id 196640
MySQL thread id 40, query id 16625 localhost 127.0.0.1 root
---TRANSACTION 0 120916, not started, process no 3110, OS thread id 192543
MySQL thread id 39, query id 17002 localhost 127.0.0.1 root
---TRANSACTION 0 120809, not started, process no 3108, OS thread id 184349
MySQL thread id 37, query id 16538 localhost 127.0.0.1 root
---TRANSACTION 0 120898, not started, process no 3107, OS thread id 180252
MySQL thread id 36, query id 17001 localhost 127.0.0.1 root
---TRANSACTION 0 120920, not started, process no 3106, OS thread id 176155
MySQL thread id 35, query id 17004 localhost 127.0.0.1 root
---TRANSACTION 0 120052, not started, process no 3105, OS thread id 172058
MySQL thread id 34, query id 11808 localhost 127.0.0.1 root
---TRANSACTION 0 120070, not started, process no 3095, OS thread id 131089
MySQL thread id 24, query id 12050 localhost 127.0.0.1 root
---TRANSACTION 0 119640, not started, process no 3087, OS thread id 98329
MySQL thread id 16, query id 10305 localhost 127.0.0.1 root
---TRANSACTION 0 119645, not started, process no 3086, OS thread id 94232
MySQL thread id 15, query id 10616 localhost 127.0.0.1 root
---TRANSACTION 0 120810, not started, process no 3085, OS thread id 90135
MySQL thread id 14, query id 16539 localhost 127.0.0.1 root
---TRANSACTION 0 120918, not started, process no 3084, OS thread id 86038
MySQL thread id 13, query id 17003 localhost 127.0.0.1 root
---TRANSACTION 0 119987, not started, process no 3082, OS thread id 77844
MySQL thread id 11, query id 11686 localhost 127.0.0.1 root
---TRANSACTION 0 119810, not started, process no 3081, OS thread id 73747
MySQL thread id 10, query id 11306 localhost 127.0.0.1 root
---TRANSACTION 0 119808, not started, process no 3080, OS thread id 69650
MySQL thread id 9, query id 11625 localhost 127.0.0.1 root
---TRANSACTION 0 120065, not started, process no 3077, OS thread id 57359
MySQL thread id 6, query id 11946 localhost 127.0.0.1 root
---TRANSACTION 0 120965, not started, process no 3075, OS thread id 49165
MySQL thread id 4, query id 16559 localhost 127.0.0.1 root
---TRANSACTION 0 120983, not started, process no 2967, OS thread id 45068
MySQL thread id 3, query id 17043 localhost root
---TRANSACTION 0 0, not started, process no 2960, OS thread id 40971
MySQL thread id 2, query id 17046 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 118021, not started, process no 2955, OS thread id 36874
MySQL thread id 1, query id 14 localhost root
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (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
219 OS file reads, 233 OS file writes, 135 OS fsyncs
0.02 reads/s, 16384 avg bytes/read, 4.41 writes/s, 2.49 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 1111, node heap has 2 buffer(s)
854.10 hash searches/s, 1745.46 non-hash searches/s
---
LOG
---
Log sequence number 0 121662756
Log flushed up to 0 121662756
Last checkpoint at 0 121662756
0 pending log writes, 0 pending chkp writes
127 log i/o's done, 2.27 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17648088; in additional pool allocated 1028864
Buffer pool size 512
Free buffers 355
Database pages 155
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 113, created 42, written 251
0.02 reads/s, 0.82 creates/s, 4.90 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 2297, id 28680, state: waiting for server activity
Number of rows inserted 4898, updated 100, deleted 4728, read 54155
96.04 inserts/s, 1.96 updates/s, 92.70 deletes/s, 1061.74 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]
