Overview
DDL statements support NO_WAIT
and WAIT
options. If a DDL statement with WAIT
enabled fails to obtain an MDL lock, it will wait for WAIT
seconds before it directly returns the query result. If a DDL statement with NO_WAIT
enabled, it will directly return the query result without waiting for the MDL lock.
SELECT FOR UPDATE statement supports NOWAIT
and SKIP LOCKED
options. If target rows are locked by another transaction, a SELECT FOR UPDATE statement is supposed to wait for the transaction to release the lock. But in some use cases like flash sales, you do not want to wait for a lock. You can use SKIP LOCKED
to skip locked rows (as a result, the locked rows won't be returned in the query result set) or NOWAIT
to return an error without waiting for the lock.
Note that NO_WAIT
and NOWAIT
are different keywords.
Supported Versions
TDSQL-C for MySQL 5.7 (kernel version 2.0.23/2.1.9) or later.
TDSQL-C for MySQL 8.0 (kernel version 3.1.10) or later.
Use Cases
Currently, DevAPI/XPlugin does not support using SKIP LOCKED
or NOWAIT
in SELECT FOR UPDATE/SHARE statements. Note that NO_WAIT
in DDL statements and NOWAIT
in SELECT FOR UPDATE statements are different keywords for historical reasons.
Was this page helpful?