TencentDB for MySQL
- Release Notes and Announcements
- Announcements
- Product Introduction
- Database Architecture
- Database Instance
- Kernel Features
- Kernel Version Release Notes
- Functionality Features
- Performance Features
- Parallel Query
- Security Features
- Stability Features
- Purchase Guide
- Getting Started
- Connecting to MySQL Instance
- Database Audit
- Audit Rule Template
- MySQL Cluster Edition
- Maintenance Management Instance
- Operation Guide
- Instance Management and Maintenance
- Instance Upgrade
- CPU Elastic Expansion
- Read-Only/Disaster Recovery Instances
- Database Proxy
- Database Proxy Kernel Features
- Database Proxy Management
- Automatic Read/Write Separation Feature
- Connection Pool Feature
- Other Features
- Account Management
- Database Management Center (DMC)
- Parameter Configuration
- Network and Security
- Backup and Rollback
- Restoring Database from Backup File
- Monitoring and Alarms
- Operation Logs
- Practical Tutorial
- Calling MySQL APIs in Python
- White Paper
- Performance White Paper
- Performance Test Report
- Troubleshooting
- Connections
- API Documentation
- Data Import APIs
- Rollback APIs
- Parameter APIs
- Making API Requests
- Instance APIs
- Database Proxy APIs
- Database Audit APIs
- Security APIs
- Account APIs
- Backup APIs
- Database APIs
- Monitoring APIs
- Log-related API
- FAQs
- Backup
- Connection and Login
- Instance Upgrade
- Performance and Memory
- Service Agreement
- Reference
NOWAIT
Last updated: 2024-07-22 11:31:50
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 statements support
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
NO_WAIT and WAIT in DDL statements are supported in kernel version MySQL 5.7 20171130 and above.NOWAIT and SKIP LOCKED in SELECT FOR UPDATE statements are supported in kernel version MySQL 5.7 20200630 and above (not just limited to MySQL 8.0 that natively supports the feature).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.Instructions
SELECT FOR UPDATE NOWAIT/SKIP LOCKED
###############session 1###############MySQL [test]> create table t1(seat_id int, state int, primary key(seat_id)) engine=innodb;Query OK, 0 rows affected (0.03 sec)MySQL [test]> INSERT INTO t1 VALUES(1,0), (2,0), (3,0), (4,0);Query OK, 4 rows affected (0.01 sec)Records: 4 Duplicates: 0 Warnings: 0MySQL [test]> begin;Query OK, 0 rows affected (0.01 sec)MySQL [test]> SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE;+---------+-------+| seat_id | state |+---------+-------+| 1 | 0 || 2 | 0 |+---------+-------+2 rows in set (0.00 sec)###############session 2###############MySQL [test]> SET SESSION innodb_lock_wait_timeout=1;Query OK, 0 rows affected (0.00 sec)MySQL [test]> SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionMySQL [test]> SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT;ERROR 5010 (HY000): Do not wait for lock.MySQL [test]> SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;+---------+-------+| seat_id | state |+---------+-------+| 3 | 0 || 4 | 0 |+---------+-------+2 rows in set (0.00 sec)MySQL [test]> SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE NOWAIT;ERROR 5010 (HY000): Do not wait for lock.MySQL [test]> SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE SKIP LOCKED;+---------+-------+| seat_id | state |+---------+-------+| 3 | 0 || 4 | 0 |+---------+-------+2 rows in set (0.00 sec)MySQL [test]> commit;Query OK, 0 rows affected (0.00 sec)
SELECT FOR SHARE NOWAIT/SKIP LOCKED
###############session 1###############MySQL [test]> begin;Query OK, 0 rows affected (0.01 sec)MySQL [test]> SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;+---------+-------+| seat_id | state |+---------+-------+| 1 | 0 || 2 | 0 |+---------+-------+2 rows in set (0.00 sec)###############session 2###############MySQL [test]> SET SESSION innodb_lock_wait_timeout=1;Query OK, 0 rows affected (0.00 sec)MySQL [test]> begin;Query OK, 0 rows affected (0.00 sec)MySQL [test]> SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionMySQL [test]> SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionMySQL [test]> SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE NOWAIT;ERROR 5010 (HY000): Do not wait for lock.MySQL [test]> SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR SHARE SKIP LOCKED;+---------+-------+| seat_id | state |+---------+-------+| 3 | 0 || 4 | 0 |+---------+-------+2 rows in set (0.00 sec)MySQL [test]> commit;Query OK, 0 rows affected (0.00 sec)
NO_WAIT and WAIT in DDL statements
ALTER TABLE `table` [NO_WAIT | WAIT [n]] `command`;DROP TABLE `table` [NO_WAIT | WAIT [n]];TRUNCATE TABLE `table` [NO_WAIT | WAIT [n]];OPTIMIZE TABLE `table` [NO_WAIT | WAIT [n]];RENAME TABLE `table_src` [NO_WAIT | WAIT [n]] TO `table_dst`;CREATE INDEX `index` ON `table.columns` [NO_WAIT | WAIT [n]];CREATE FULLTEXT INDEX `index` ON `table.columns` [NO_WAIT | WAIT [n]];CREATE SPATIAL INDEX `index` ON `table.columns` [NO_WAIT | WAIT [n]];DROP INDEX `index` ON `table` [NO_WAIT | WAIT [n]];
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No