Re: Is this normal?
If I try transaction isolation level READ COMMITTED, the second SELECT
doesn't block, which seems reasonable since it isn't supposed to lock
non-existant rows in that case. But I need the lock for my atomic update.
My workaround for now is to do the INSERT before anything else, and then
catch the duplicate key error when the other thread tries to do the same
INSERT. Once the row exists, all future atomic updates work as expected
without deadlocks, of course.
But I still don't understand why my first method didn't work.
Here is the INNODB STATUS after the deadlock in my original post,
with transaction isolation level REPEATABLE-READ:
=====================================
050108 4:28:31 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 6, signal count 6
Mutex spin waits 3, rounds 40, OS waits 1
RW-shared spins 8, OS waits 4; RW-excl spins 1, OS waits 1
------------------------
LATEST DETECTED DEADLOCK
------------------------
050108 4:27:55
*** (1) TRANSACTION:
TRANSACTION 0 66306, ACTIVE 9 sec, process no 23638, OS thread id 163851 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 2, query id 7 localhost root statistics
select * from T where C = 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 63 n bits 72 index `PRIMARY` of table `test/T` trx id 0 66306 lock_mode X waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;
*** (2) TRANSACTION:
TRANSACTION 0 66305, ACTIVE 19 sec, process no 23636, OS thread id 147466 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320
MySQL thread id 1, query id 8 localhost root update
insert into T set C = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 63 n bits 72 index `PRIMARY` of table `test/T` trx id 0 66305 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; 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 63 n bits 72 index `PRIMARY` of table `test/T` trx id 0 66305 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 0 66307
Purge done for trx's n:o < 0 65870 undo n:o < 0 0
Total number of lock structs in row lock hash table 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 23642, OS thread id 196618
MySQL thread id 4, query id 12 localhost root
show innodb status
---TRANSACTION 0 66306, ACTIVE 45 sec, process no 23638, OS thread id 163851
2 lock struct(s), heap size 320
MySQL thread id 2, query id 7 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
46 OS file reads, 13 OS file writes, 8 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 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 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 2638438
Log flushed up to 0 2638438
Last checkpoint at 0 2638438
0 pending log writes, 0 pending chkp writes
11 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 18644560; in additional pool allocated 871552
Buffer pool size 512
Free buffers 488
Database pages 23
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 22, created 1, written 11
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 23633, id 114696, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================