NOWAIT equivalent in MySQL 5.7

Question

MySQL 8.0 has a new feature to allow a transaction to timeout immediately by throwing an error if it tried to access records that are locked by another transaction instead of waiting for that transaction to commit, or waiting for the time-out.

Is there a way to achieve the same effect in MySQL 5.7?

Answer 1

Server-side SELECT statement timeouts allows:

SET SESSION MAX_EXECUTION_TIME=2000;

Not exactly the same, but close.

Answer 2

MAX_EXECUTION_TIME can work for SELECT statements. I needed this to work with an INSERT statement:

SET SESSION INNODB_LOCK_WAIT_TIMEOUT = 2;

To make it work for a single INSERT statement, I did this:

SET @saved_lock_wait = @@SESSION.INNODB_LOCK_WAIT_TIMEOUT;
SET SESSION INNODB_LOCK_WAIT_TIMEOUT = 2;
-- The query...
SET SESSION INNODB_LOCK_WAIT_TIMEOUT = @saved_lock_wait;