MySQL Forums
Forum List  »  InnoDB

Strange Deadlock on single insert statement
Posted by: Christos Pavlides
Date: January 02, 2009 07:07AM

Hi guys, I did not know where else to look for so I am posting this here hoping that someone can help me out.

Basically I have an SP that logs some financial transactions, this is the main SP of the system thus every transaction has to call this. I issue a begin transaction almost as soon as the SP starts then I am doing a couple of updates, then the main insert in the transaction table and last but not least I update a couple of counters (statistichosttransaction) about the transactions that are flowing through a particular system. This function is supposed to update a counter based on the primary key (which is made up of a few columns). Part of the key is a specially formatted date/time column that forces the update to update 0 rows and when I find out using ROW_COUNT() that no row has been updated I insert a new row. (ISOLATION LEVEL is repeatable read)

Now there are a couple of issues with my logic, that I try to explain later but the thing tha bothers me most is that I got the following deadlock where I do not believe it is possible to deadlock. How can code like the following deadlock by two inserts?

BEGIN TRANSACTION;
.
.
.
UPDATE statistichosttransaction SET counter = counter + 1
WHERE pk = pk

if (ROW_COUNT() < 1) then
insert into statistichosttransaction blah, blah
end if;

.
.
.

COMMIT;






"Type","Name","Status"
"InnoDB","","
=====================================
090102 11:06:50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 69851, signal count 47459
Mutex spin waits 0, rounds 3696078, OS waits 25302
RW-shared spins 29574, OS waits 12813; RW-excl spins 83484, OS waits 20504
------------------------
LATEST DETECTED DEADLOCK
------------------------
081230 10:42:23
*** (1) TRANSACTION:
TRANSACTION 0 188831, ACTIVE 0 sec, OS thread id 3316 inserting
mysql tables in use 1, locked 1
LOCK WAIT 18 lock struct(s), heap size 2496, 11 row lock(s), undo log entries 2
MySQL thread id 1870, query id 26670265 192.168.100.1 root update
INSERT INTO statistichosttransaction
(.....)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 38793 n bits 96 index `PRIMARY` of table `...`.`statistichosttransaction` trx id 0 188831 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 0 188832, ACTIVE 0 sec, OS thread id 4084 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
18 lock struct(s), heap size 2496, 11 row lock(s), undo log entries 2
MySQL thread id 1897, query id 26670268 192.168.100.1 root update
INSERT INTO statistichosttransaction
(.....)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 38793 n bits 96 index `PRIMARY` of table `...`.`statistichosttransaction` trx id 0 188832 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 38793 n bits 96 index `PRIMARY` of table `...`.`statistichosttransaction` trx id 0 188832 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 0 204030
Purge done for trx's n:o < 0 204029 undo n:o < 0 0
History list length 6
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 3608
MySQL thread id 2470, query id 28908904 127.0.0.1 root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 0, not started, OS thread id 3540
MySQL thread id 2467, query id 28908898 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 (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (write thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 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
3708 OS file reads, 108032 OS file writes, 1489 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: size 1, free list len 0, seg size 2,
60 inserts, 60 merged recs, 5 merges
Hash table size 2310107, used cells 3793, node heap has 5 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 870479430
Log flushed up to 0 870479430
Last checkpoint at 0 870479430
0 pending log writes, 0 pending chkp writes
95020 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 567063386; in additional pool allocated 2097024
Dictionary memory allocated 646072
Buffer pool size 32000
Free buffers 22030
Database pages 9965
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 3698, created 6267, written 12628
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
1 read views open inside InnoDB
Main thread id 1728, state: waiting for server activity
Number of rows inserted 117961, updated 259496, deleted 0, read 2246118
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
"

Options: ReplyQuote


Subject
Views
Written By
Posted
Strange Deadlock on single insert statement
3484
January 02, 2009 07:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.