Table locking is the poor man’s transaction. In short, MySQL lets you lock down a group of tables so that only a single client can use it. Unlike transactions, you are not limited by the type of table. You cannot, however, roll back any actions taken against a locked table.
Locking has two basic functions:
Enables multiple statements to execute against a group of tables as one unit of work
Enables multiple updates to occur faster under some circumstances
MySQL supports three kinds of locks: read, read local, and write. Both kinds of read locks lock the
table for reading by a client and all other clients. As long as the
lock is in place, no one can write to the locked tables. Read and
read local locks differ in that read local allows a client to execute
INSERT statements as long as no
changes to the MySQL files from outside of MySQL occur while the lock
is held. If changes might occur by agents outside of MySQL, a read
lock is required.
A write lock locks the specified tables against all access—read or write—by any other client. To lock a table, use the following command:
LOCK TABLES ACCOUNT WRITE;
Now that the
ACCOUNT table is locked, you can read
from it and modify the data behind it and be certain that no one else
will change the data you read between your read and write operations:
SELECT @BAL:=BALANCE FROM ACCOUNT WHERE ACCOUNT_ID = 1; UPDATE ACCOUNT SET BALANCE = @BAL * 0.03 WHERE ACCOUNT_ID = 1;
Finally, you need to release the locks:
UNLOCK TABLES; ...