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]
