Fixed: "ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set" in MySQL

ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set isn't really a failure. You asked MySQL not to wait for a locked row, and the row was locked. The fix is deciding what your code does in that case.

What I Was Doing

I wanted to work on a product, but only if nobody else had it, so I used NOWAIT, on MySQL 9.7.4 in Docker:

mysql

SELECT Id, QuantityOnHand FROM products WHERE Id = 3 FOR UPDATE NOWAIT;

What I Expected

The row, locked for me.

What I Got

Output

ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

What I Checked

Who held it. Another session had taken it first, and hadn't finished:

mysql: terminal 1

START TRANSACTION;
SELECT Id, QuantityOnHand FROM products WHERE Id = 3 FOR UPDATE;

What NOWAIT promises. The locking reads page describes the two options for not waiting. With NOWAIT, a locked row makes the statement fail instead of waiting.

Why It Happens

Without NOWAIT, the statement would wait for the row, for up to 50 seconds by default. With it, MySQL reports the lock immediately, and the error is how it does that.

The Fix

Choose what should happen when the row is taken:

You want	Do this
To tell the user it's busy	Catch error 3572, and answer "someone else is working on this"
To try again shortly	Catch it, wait a moment, and run the statement again
To work on something else	Use SKIP LOCKED, which leaves the locked rows out
To wait your turn	Remove NOWAIT

Here's the second one. The other session finishes:

mysql: terminal 1

COMMIT;

And the same statement runs again:

mysql

SELECT Id, QuantityOnHand FROM products WHERE Id = 3 FOR UPDATE NOWAIT;

After the Fix

Output

+----+----------------+
| Id | QuantityOnHand |
+----+----------------+
|  3 |             50 |
+----+----------------+
1 row in set (0.000 sec)

Common Mistakes

Mistake	What happens	Do this instead
Treating it as a server problem	Nothing's wrong with the server	Handle it in your code
Retrying in a tight loop	It hammers the server for nothing	Wait a little between tries
Using NOWAIT with SKIP LOCKED	MySQL allows only one of them	Pick one
Forgetting the rest of the transaction	Earlier statements are still pending	Commit or roll back deliberately

Troubleshooting

Symptom	Likely cause	Fix
It fails every time	Something holds the row for a long time	See who's blocking
You get ERROR 1205 and not this	There's no NOWAIT, so it waited and timed out	The fix

FAQ

Does the error end my transaction?

No. Statements before it stay in the transaction, so you still have to commit or roll back.

Conclusion

ERROR 3572 is NOWAIT doing what you asked: the row was taken, and MySQL said so at once. Catch it and tell the user, try again later, use SKIP LOCKED, or take NOWAIT out and wait.

Additional Resources

• Locking Reads — NOWAIT and SKIP LOCKED.

Related

• How to Skip Locked Rows in MySQL with SKIP LOCKED and NOWAIT
• How to Lock a Row in MySQL with SELECT FOR UPDATE
• How to See Which MySQL Query Is Blocking Another