MySQL锁表_LOCK TABLES_UNLOCK TABLES

mysql5.6

参考文档：http://dev.mysql.com/doc/refman/5.6/en/lock-tables.html

锁表和解锁表的基本语法

LOCK TABLES    
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
    
UNLOCK TABLES

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

MySQL锁表的目的

    MySQL enables client sessions to acquire table locks explicitly（明白地，明确地） for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires 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 be acquired for base tables or views. You must have 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 automatically. If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly.

UNLOCK TABLES

UNLOCK TABLES explicitly（明确的） releases any table locks held by the current session. LOCK TABLES implicitly（隐式的） releases any table locks held by the current session before acquiring new locks.

Another use for UNLOCK TABLES is to release the global read lock acquired with the FLUSH TABLES WITH READ LOCK statement, which enables you to lock all tables in all databases. 

WRITE LOCK 和 READ LOCK的作用

A table lock only protects against inappropriate（不恰当的，不合适的） 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 are not permitted. TRUNCATE TABLE operations are not transaction-safe, so an error occurs if the session attempts one during an active transaction or while holding a READ lock.

示例一：

该示例下的会话的事务都是自动提交的

会话一：

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>

在这个会话中，锁住表people，然后读取数据，此时在另外一个会话中，执行如下sql语句：

会话二：

mysql> use local_database;
Database changed
mysql> 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;

当给表people上read lock时，正常执行，也能正常unlock table，但是当给people表上write lock 时，发现当前会话被阻塞。。。

此时在会话一种执行如下sql：

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

mysql>

好的，当执行完这句sql后，在会话二中的阻塞会消失。。

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

mysql>

我们可以知道，一个会话中的read lock会阻塞另一个会话中的write lock，但不会阻塞read lock。。。

我们可以这样总结：

• 表上的write lock会阻塞其他会话中write lock 和 read lock

• 表上的read lock只会阻塞其他会话中write lock，而不会阻塞read lock

而行级锁也是同样的道理

我之前写的这篇博客也是同样的道理：http://my.oschina.net/xinxingegeya/blog/215417

继续探索：

在会话二中给表加了write lock，在会话二进行如下操作：

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)

这些都是没问题的，删除表的操作就不演示了。。。。

当给表加了read lock 时，进行如下操作：

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‘ was locked with a READ lock and can‘t be updated
mysql>

可以看到删除操作不允许，虽然数据库没有id=2的记录，但表被read lock了，所以不允许操作。。。

下面这些操作也不被允许：

mysql> drop table people;
ERROR 1099 (HY000): Table ‘people‘ was locked with a READ lock and can‘t be updated
mysql> truncate people;
ERROR 1099 (HY000): Table ‘people‘ was locked with a READ lock and can‘t be updated
mysql>

========END=========