Select for update nowait doesn't work in MySQL 5x (innodb). Is there any function like NOWAIT in MySQL?
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:
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:
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