OperationalError: 1213 Deadlock found when trying to get lock; try restarting transaction
I've already tried all the basic steps that the documentation seems to recommend:
- Set my TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED for session and globally
- used "UPDATE LOW PRIORITY" to encourage waiting for locks to become clear
- COMMIT immediately before the UPDATE to drop all locks
What's puzzling is that this deadlock requires *three* active clients to manifest. Two of these clients can hit the server without problems, but if that number is increased only two active clients remain deadlock-free. Any hints or suggestions for further reading would be appreciated.
Here's what I get from SHOW INNODB STATUS after a deadlock. What's especially puzzling is that what I'm seeing seems to violate the very definition of deadlock. Transaction 1 is holding no locks (since it just committed) and is attempting to acquire a lock on only one row of one table for an update. You need at least two locks to create circularity.
----------------------------------------------------------------------
=====================================
050427 12:31:50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 96, signal count 93
Mutex spin waits 657, rounds 8130, OS waits 31
RW-shared spins 83, OS waits 37; RW-excl spins 40, OS waits 28
------------------------
LATEST DETECTED DEADLOCK
------------------------
050427 12:31:37
*** (1) TRANSACTION:
TRANSACTION 0 23662, ACTIVE 1 sec, OS thread id 25389056 updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 326, query id 13839 localhost sherman Updating
UPDATE LOW_PRIORITY frames SET frame_state="run" WHERE frame_jid=9 AND frame_lid=0 AND frame_frame=2 AND frame_tile=6 AND frame_state="ready"
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 393 n bits 648 index `frame_state_key` of table `sherman/frames` trx id 0 23662 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 577 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 32
0: len 1; hex 04; asc ;; 1: len 4; hex 0000002f; asc /;; 2: len 1; hex 00; asc ;; 3: len 2; hex 0010; asc ;; 4: len 1; hex 00; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 23665, ACTIVE 0 sec, OS thread id 25530880 fetching rows, thread declared inside InnoDB 283
mysql tables in use 1, locked 1
17 lock struct(s), heap size 2496
MySQL thread id 328, query id 13853 localhost sherman Copying to tmp table
CREATE TEMPORARY TABLE t_active_layers (PRIMARY KEY(tal_jid,tal_lid)) SELECT DISTINCT frame_jid as tal_jid, frame_lid as tal_lid FROM frames WHERE frame_state="ready"
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 393 n bits 648 index `frame_state_key` of table `sherman/frames` trx id 0 23665 lock mode S
Record lock, heap no 577 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 32
0: len 1; hex 04; asc ;; 1: len 4; hex 0000002f; asc /;; 2: len 1; hex 00; asc ;; 3: len 2; hex 0010; asc ;; 4: len 1; hex 00; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1717 n bits 864 index `frame_state_key` of table `sherman/frames` trx id 0 23665 lock mode S waiting
Record lock, heap no 78 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 32
0: len 1; hex 03; asc ;; 1: len 4; hex 00000009; asc ;; 2: len 1; hex 00; asc ;; 3: len 2; hex 0002; asc ;; 4: len 1; hex 06; asc ;;
*** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 0 23695 Purge done for trx's n:o < 0 23695 undo n:o < 0 0 History list length 31 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 25577472 MySQL thread id 330, query id 13958 localhost soren SHOW INNODB STATUS ---TRANSACTION 0 23661, not started, OS thread id 25566208 MySQL thread id 329, query id 13852 localhost sherman ---TRANSACTION 0 23665, not started, OS thread id 25530880 MySQL thread id 328, query id 13853 localhost sherman -------- 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 45 OS file reads, 2582 OS file writes, 1804 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 2.22 writes/s, 0.67 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 34679, used cells 15981, node heap has 22 buffer(s) 0.89 hash searches/s, 2.89 non-hash searches/s --- LOG --- Log sequence number 0 106306834 Log flushed up to 0 106306834 Last checkpoint at 0 106306834 0 pending log writes, 0 pending chkp writes 1673 log i/o's done, 0.22 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 18368350; in additional pool allocated 1046784 Buffer pool size 512 Free buffers 27 Database pages 463 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 52, created 411, written 2038 0.00 reads/s, 0.00 creates/s, 2.22 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread id 25323008, state: waiting for server activity Number of rows inserted 97284, updated 52406, deleted 267, read 6584134 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 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]
