Similar function NOWAIT in MySQL

Question

Select for update nowait doesn't work in MySQL 5x (innodb). Is there any function like NOWAIT in MySQL?

Answer 1

When it comes to InnoDB, SELECT FOR UPDATE has the potential for leaving lingering locks. This not a new subject as shown in the Forums:

• http://forums.mysql.com/read.php?22,230883
• http://forums.mysql.com/read.php?22,230883,231439#msg-231439
• http://dev.mysql.com/worklog/task/?id=3597
• https://bugs.eclipse.org/bugs/show_bug.cgi?id=326424

I wrote about this before in the DBA StackExchange

• Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
• Jan 02, 2012 : LOCK IN SHARE MODE
• Mar 18, 2012 : select for update gives error on indexed column
• May 09, 2012 : Transaction Lock Timeouts When Updating a Row
• May 13, 2012 : Cannot update certain rows in innodb tables

Note what the MySQL Documentation said on this:

When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

You best chance it to change innodb_lock_wait_timeout to your liking

Answer 2

MySQL provides the ability to set application-specific locking using the GET_LOCK() function. You can check if the mutex has been taken by using the IS_FREE_LOCK() function.

You can refer this thread for detail: http://forums.mysql.com/read.php?22,45679,45679#msg-45679

Also, Non-blocking queries are not supported by MySQL:

http://lists.mysql.com/mysql/219041