Hi again,
I ran into some dead locking that was unexpected, & I basically think the "insert error -> share lock" behavior is problematic. I would think that either a insert error does not acquire a row lock ( equivalent of a basic select ), or that it would acquire an exclusive row lock as if the insert really happened.
Note that I do not need a fix for the dead lock situation, I have one by basically doing a "select for update" to create a critical section ahead of time, but I am writing up this email suggesting the InnoDB locking behavior could be improved.
The dead locks seems to come from the fact that I would have code like this:
update ( separate transaction ) ... begin work insert ignore ... ( share lock acquired upon error ) update commit
I get a dead lock like this:
client 2 insert ignore/error - share lock acquired client 1 update - request exclusive lock, wait client 2 update - request exclusive lock *deadlock*, client 2 transaction killed
What is counter-intuitive for me about this is that the insert ignore gets escalated to a "share lock" when the insert error occurs. I understand that this behavior is as documented at
http://dev.mysql.com/doc/mysql/en/InnoDB_Locks_set.html
with this entry:
INSERT INTO ... VALUES (...) sets an exclusive lock on the inserted row. Note that this lock is not a next-key lock and does not prevent other users from inserting to the gap before the inserted row. If a duplicate-key error occurs, *a shared lock on the duplicate index record is set*.
but again, I would think that this would be handled more gracefully with either an exclusive lock, or no lock at all. I believe then that "client 2" would not have deadlocked because it already had the lock at insert time that it later needed for the later update.
Put another way, for an insert command to come back with a share lock is counterintuitive, even it is during an error condition, but this error condition is no error at all with "insert ignore".
BTW, I have the READ-COMMITTED mode set in my.cnf if it matters. Also for documentation purposes, I have included below the output from "show innodb status" regarding one such deadlock.
Regards,
Josh ________________________________________________________________________ Josh Chamas, Founder | NodeWorks - http://www.nodeworks.com Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com http://www.chamas.com | Apache::ASP - http://www.apache-asp.org
------------------------ LATEST DETECTED DEADLOCK ------------------------ 040701 17:57:19 *** (1) TRANSACTION: TRANSACTION 0 26896, ACTIVE 0 sec, process no 21491, OS thread id 122896 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id 10422, query id 145918 gate 192.168.0.10 dmoz Updating -- ( update low_priority dmoz_track.clients set last_visit = now(), ip_address = '192.168.0.10', user_agent_id = '13', num_visits = num_visits + 1 where client_id = 'VIMHu+tRy/sioy+kgxQBfw' -- ) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 1082 n bits 88 index `PRIMARY` of table `dmoz_track/clients` trx id 0 26896 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 8; 1-byte offs TRUE; info bits 0 0: len 22; hex 56494d48752b7452792f73696f792b6b677851426677; asc VIMHu+tRy/sioy+kgxQBfw;; 1: len 6; hex 00000000690a; asc i ;; 2: len 7; hex 0000000301191e; asc ;; 3: len 8; hex 8000123a16dd69ba; asc : i ;; 4: len 4; hex 8000000d; asc ;; 5: len 8; hex 8000123a16dfe7a7; asc : ;; 6: len 12; hex 3139322e3136382e302e3130; asc 192.168.0.10;; 7: len 4; hex 80001473; asc s;;
*** (2) TRANSACTION: TRANSACTION 0 26894, ACTIVE 0 sec, process no 20890, OS thread id 114703 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL thread id 10421, query id 145919 gate 192.168.0.10 dmoz Updating -- ( update low_priority dmoz_track.clients set last_visit = now(), ip_address = '192.168.0.10', user_agent_id = '13', num_visits = num_visits + 1 where client_id = 'VIMHu+tRy/sioy+kgxQBfw' -- ) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 1082 n bits 88 index `PRIMARY` of table `dmoz_track/clients` trx id 0 26894 lock mode S locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 8; 1-byte offs TRUE; info bits 0 0: len 22; hex 56494d48752b7452792f73696f792b6b677851426677; asc VIMHu+tRy/sioy+kgxQBfw;; 1: len 6; hex 00000000690a; asc i ;; 2: len 7; hex 0000000301191e; asc ;; 3: len 8; hex 8000123a16dd69ba; asc : i ;; 4: len 4; hex 8000000d; asc ;; 5: len 8; hex 8000123a16dfe7a7; asc : ;; 6: len 12; hex 3139322e3136382e302e3130; asc 192.168.0.10;; 7: len 4; hex 80001473; asc s;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 1082 n bits 88 index `PRIMARY` of table `dmoz_track/clients` trx id 0 26894 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 8; 1-byte offs TRUE; info bits 0 0: len 22; hex 56494d48752b7452792f73696f792b6b677851426677; asc VIMHu+tRy/sioy+kgxQBfw;; 1: len 6; hex 00000000690a; asc i ;; 2: len 7; hex 0000000301191e; asc ;; 3: len 8; hex 8000123a16dd69ba; asc : i ;; 4: len 4; hex 8000000d; asc ;; 5: len 8; hex 8000123a16dfe7a7; asc : ;; 6: len 12; hex 3139322e3136382e302e3130; asc 192.168.0.10;; 7: len 4; hex 80001473; asc s;;
*** WE ROLL BACK TRANSACTION (2)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]