{"id":3521,"date":"2015-05-17T22:34:42","date_gmt":"2015-05-18T06:34:42","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3521"},"modified":"2024-01-05T03:30:39","modified_gmt":"2024-01-05T10:30:39","slug":"mysql-table-locking","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-table-locking\/","title":{"rendered":"MySQL Table Locking"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL locking for cooperating table accesses between sessions.<\/p>\n\n\n\n

A lock is a flag associated with a table. MySQL allows a client session to explicitly acquire a table lock to prevent other sessions from accessing the same table during a specific period.<\/p>\n\n\n\n

A client session can acquire or release table locks only for itself. A client session cannot acquire or release table locks for other client sessions.<\/p>\n\n\n

\n
\"MySQL<\/figure>\n<\/div>\n\n\n

Before we move on, let’s create a table<\/a> named messages<\/code> for practicing with the table locking statements.<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> messages ( \n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>, \n    message VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL LOCK TABLES statement<\/h2>\n\n\n\n
The following LOCK TABLES<\/code> statement explicitly acquires a table lock:<\/p>\n\n\n
LOCK<\/span> TABLES<\/span> table_name [READ<\/span> | WRITE]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the name of the table that you want to lock after the LOCK TABLES<\/code> keywords. In addition, you specify the type of lock, either  READ<\/code> or WRITE<\/code>.<\/p>\n\n\n\n
MySQL allows you to lock multiple tables by specifying a list of comma-separated table names with lock types that you want to lock after the LOCK TABLES<\/code> keywords:<\/p>\n\n\n
LOCK<\/span> TABLES<\/span> table_name1 [READ<\/span> | WRITE], \n            table_name2 [READ<\/span> | WRITE],\n             ... ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL UNLOCK TABLES statement<\/h2>\n\n\n\n
The UNLOCK TABLES statement releases any table locks held by the current session:<\/p>\n\n\n
UNLOCK<\/span> TABLES<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
READ Locks<\/h2>\n\n\n\n
A READ<\/code> lock has the following features:<\/p>\n\n\n\n