Hello.
The output of INNODB MONITOR is not clear for me (I hope only for me, and someone more skilled could correct me). At: http://dev.mysql.com/doc/mysql/en/innodb-locks-set.html it is said that, SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. You don't have a SERIALIZABLE level, and your > 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" Should not put locks on table `frames`. May be the problem is in the previous sequence of queries? S$ren Ragsdale <[EMAIL PROTECTED]> wrote: > I've been getting the following error in my Python program which > accesses InnoDB tables: > > 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 > ============================ > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
