MySQL lock table _lock tables_unlock TABLES

MySQL lock table _lock TABLES_unlock TABLES

mysql5.6

Reference Document: Http://dev.mysql.com/doc/refman/5.6/en/lock-tables.html

The basic syntax of the lock table and the lock table

LOCK TABLES tbl_name [[as] alias] lock_type [, Tbl_name [[as] alias] lock_type] ... UNLOCK TABLES

Lock_type:read [LOCAL] | [Low_priority] WRITE

Purpose of the MySQL lock table

MySQL enables client sessions to acquire table locks explicitly (unambiguously, explicitly) for the purpose of cooperating with other SES Sions for access to tables, or to prevent and sessions from modifying tables during periods when a session requires excl Usive (separate, exclusive) access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

LOCK TABLES

LOCK TABLES explicitly acquires table locks for the current client session. Table locks can acquired for base tables or views. You must has the LOCK TABLES privilege, and the SELECT privilege for each object to be locked.

For view locking, LOCK TABLES adds all base TABLES used in the view to the set of TABLES to be locked and locks them a utomatically. If you lock a table explicitly with lock TABLES, any TABLES used in triggers is also locked implicitly.

UNLOCK TABLES

UNLOCK TABLES explicitly (clear) releases any table locks held by the current session. LOCK TABLES implicitly (implicit) releases any table locks held by the current session before acquiring new locks.

Another use for UNLOCK TABLES are to release the global read-lock acquired with the FLUSH TABLES with read lock St Atement, which enables you to lock all tables on all databases.

The role of WRITE lock and READ lock

A table lock only protects against inappropriate (inappropriate, unsuitable) reads or writes by other sessions. a session holding a WRITE lock can perform table-level operations such as DROP table or TRUNCATE table. for Sessions holding a READ lock, DROP table and TRUNCATE table operations is not permitted. TRUNCATE TABLE Operations is not transaction-safe, so a error occurs if the session attempts one during an active Transa Ction or while holding a READ lock.

Example one:

The transactions for the session under this example are automatically committed

Session One:

Mysql> lock tables people read; Query OK, 0 rows Affected (0.00 sec) mysql> Select * from people;+-----------+------------+-----------+| person_id | first_name |         Last_Name |+-----------+------------+-----------+| 1 | 1111 |         1111 | | 2 | 2222 | 2222 |+-----------+------------+-----------+2 rows in Set (0.00 sec) mysql>

In this session, lock the table people and then read the data, and in another session, execute the following SQL statement:

Session Two :

mysql> use local_database;database changedmysql> lock tables people  Read query ok, 0 rows affected  (0.00 sec) mysql> unlock tables; query ok, 0 rows affected  (0.00 sec) mysql> select * from  people;+-----------+------------+-----------+| person_id | first_name | last_name  |+-----------+------------+-----------+|         1 |  1111       | 1111      | |          2 | 2222        | 2222      |+-----------+------------+-----------+2 rows  in set  (0.00 sec) mysql> lock tables people write;

When read lock is people on the table, normal execution can also unlock table normally, but when write lock is given to the people table, it is found that the current session is blocked ...

At this point in the session one executes the following SQL:

mysql> unlock tables; Query OK, 0 rows Affected (0.00 sec) mysql>

Okay, when you're done with this SQL, the blocking in session two disappears:

Mysql> lock tables people write; Query OK, 0 rows affected (50.40 sec) mysql>

As we can see, read lock in one session blocks write lock in another session, but does not block read lock ...

We can summarize this:

• Write lock on the table blocks write lock and read lock in other sessions

• Read lock on the table will only block write lock in other sessions without blocking the read lock

and row-level locks are the same thing.

I wrote this blog before the same reason: http://my.oschina.net/xinxingegeya/blog/215417

Continue to explore:

Write lock is added to the table in session two, and the following actions are performed in session two:

Mysql> lock tables people write; Query OK, 0 rows affected (50.40 sec) mysql> select * from people;+-----------+------------+-----------+| person_id | first_name |         Last_Name |+-----------+------------+-----------+| 1 | 1111 |         1111 | | 2 | 2222 | 2222 |+-----------+------------+-----------+2 rows in Set (0.00 sec) mysql> Delete from people where person_id = 2; Query OK, 1 row affected (0.15 sec)

None of this is a problem, the operation to delete the table does not show ....

When you add read lock to the table, do the following:

Mysql> lock tables people read; Query OK, 0 rows Affected (0.00 sec) mysql> Delete from people where person_id = 2; ERROR 1099 (HY000): Table ' People ' is locked with a READ lock and can ' t be updatedmysql>

You can see that the delete operation is not allowed, although the database does not have id=2 records, but the table is read lock, so the operation is not allowed ...

The following actions are also not allowed:

mysql> drop table people; ERROR 1099 (HY000): Table ' People ' is locked with a READ lock and can ' t be updatedmysql> truncate people; ERROR 1099 (HY000): Table ' People ' is locked with a READ lock and can ' t be updatedmysql>

========end=========

MySQL lock table _lock tables_unlock TABLES