MENU
Locks
| LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITE UNLOCK TABLES |
||
| Lock Type | Holding Session | Other Sessions |
| READ | Can read Can’t write |
Can read Can’t write |
| WRITE | Can read Can write |
Can’t read Can’t write |
| For a READ lock, no session can update the table. The session holding the lock can read the table but not write it. Other sessions can read the table without explicitly acquiring a READ lock. Multiple sessions can acquire a READ lock for the table at the same time. LOCAL enables non-conflicting INSERT statements by other sessions. If InnoDB is used, READ LOCAL is the same as READ. | ||
| For a WRITE lock, the session that holds the lock can read and write the table. No other session can access it. Lock requests for the table by other sessions block. LOW_PRIORITY is ignored. | ||
| All locks are released automatically when the session terminates. | ||
| LOCK TABLES and UNLOCK TABLES cannot be used within stored programs. The following statements cannot be run while a table is being locked: CREATE TABLE, CREATE VIEW, DROP VIEW, DDL statements on stored functions and procedures and events. | ||
Beginning a transaction releases existing table locks. ROLLBACK does not release table locks.
FLUSH TABLES WITH READ LOCK acquires a global read lock. START TRANSACTION does not release it.
This illustrates the correct way to use LOCK TABLES with transactional tables.
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;