1. Transaction A obtains an exclusive lock on a set of rows which we will call R1.
2. Transaction B obtains an exclusive lock on another set of rows which we will call R2.
3. Transaction A requests (but obviously doesn't acquire) an exclusive lock on R2.
4. Transaction B requests (but obviously doesn't acquire) an exclusive lock on R1.
5. Classic deadlock!
There are various ways of dealing with this (with timeouts and graph traversal seemingly popular). As you can see, neither transaction can go forward. Thus, the almighty InnoDB will think for a moment, decide which transaction it has something personal against, laugh at it and force it to ROLLBACK.
That transaction can then try again if it doesn't feel totally small and humiliated.
Hope this helps!
Regards,
Chris
Dr. Frank Ullrich wrote:
Hi,
Dmitry Anikin schrieb:
Suppose some user issued 'select ... for update', then went for coffee-break (to think hard on what he really wants to update in that row). Another client tries to update the same row and I don't want him to wait, just immediately return an error, so he could do some other useful task meanwhile. I haven't found any no_wait option for locks in the manual :(.
There's a variable innodb_lock_wait_timeout, though, but
unfortunately I can't assign 0 to it (min. value is 1).
Still, 1 second time-out can be bearable (although I'd
appreciate a way to reduce it to zero) but what disturbs
me is that I've read in the manual that deadlock-removing
What you describe is basically not a deadlock situation! A deadlock means that two sessions wait for each other in such a way that neither can proceed before the other one has finished its "transaction".
Regards, Frank.
algorithm aborts transaction which it thinks is most suitable for aborting (not last-in-first-aborted). Since time-out feature has something to do with deadlocks can I be absolutely sure that WAITING transaction will be aborted and not that which issued the lock? And also it would be fine to have non-destructive means to determine whether some row has been locked so I may just skip (postpone) some updates without rollback of whole transaction. Is it possible?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
