Re: Can I really have no_wait row-locks in MySQL+InnoDB?
Dmitry, we have to consider implementing NOWAIT and SKIP LOCKED clauses to SQL statements. The latter would be useful in implementing transactional queues. But there are lots of items in the TODO list. Do not expect these soon. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Dmitry Anikin [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 15, 2003 11:26 AM Subject: Can I really have no_wait row-locks in MySQL+InnoDB? 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 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]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
mos wrote: At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 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 Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) No no no!!! You've got it all wrong! You go and find the user who had the least involvement in causing the deadlock and scapegoat them! Either that, or you use it as an excuse for doing one of the following things: 1. Getting the purchase of more hardware approved. 2. Getting the purchase of MySQL Pro and InnoDB Hot Backup approved 3. Getting a MySQL support contract purchase approved 4. Getting a raise for resolving the situation with poise, grace and subtlety (Eg: Shutting it down, blaming users, blaming SCO...) Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
At 01:14 AM 12/16/2003, you wrote: mos wrote: At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 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 Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) No no no!!! You've got it all wrong! You go and find the user who had the least involvement in causing the deadlock and scapegoat them! Either that, or you use it as an excuse for doing one of the following things: 1. Getting the purchase of more hardware approved. 2. Getting the purchase of MySQL Pro and InnoDB Hot Backup approved 3. Getting a MySQL support contract purchase approved 4. Getting a raise for resolving the situation with poise, grace and subtlety (Eg: Shutting it down, blaming users, blaming SCO...) Regards, Chris Chris, Hot Dang you're good! :-) I didn't see this in any of this in Paul Dubois's books. Maybe it could be added in a future version??? Call the section The Blame Game and put it into the Appendix called MySQL Survivor Game..Be the last one to be voted off the island.. bg Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
mos wrote: At 01:14 AM 12/16/2003, you wrote: mos wrote: At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 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 Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) No no no!!! You've got it all wrong! You go and find the user who had the least involvement in causing the deadlock and scapegoat them! Either that, or you use it as an excuse for doing one of the following things: 1. Getting the purchase of more hardware approved. 2. Getting the purchase of MySQL Pro and InnoDB Hot Backup approved 3. Getting a MySQL support contract purchase approved 4. Getting a raise for resolving the situation with poise, grace and subtlety (Eg: Shutting it down, blaming users, blaming SCO...) Regards, Chris Chris, Hot Dang you're good! :-) I didn't see this in any of this in Paul Dubois's books. Maybe it could be added in a future version??? Call the section The Blame Game and put it into the Appendix called MySQL Survivor Game..Be the last one to be voted off the island.. bg Mike I think it would be better to base it on the prophetic vision of how reality television is set to evolve: Series 7. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I really have no_wait row-locks in MySQL+InnoDB?
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 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]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
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] -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 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]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 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 Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
