http://blog.csdn.net/zyz511919766/article/details/16342003
http://blog.csdn.net/zyz511919766/article/category/1095365
1语法
LOCK TABLES tbl_name[[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...
lock_type:READ[LOCAL]| [LOW_PRIORITY] WRITE
UNLOCK TABLES
2简单使用
一个session只能为自己获取锁和释放锁，不能为其他session获取锁，也不能释放由其他session保持的锁。
要为一个对象获取锁，需具备该对象上的SELECT权限和LOCK TABLES权限。LOCK TABLES语句为当前session显式的获取表锁。可以为tables获取锁，也可以为view，trigger获取锁。对于为view加锁，LOCK TABLES语句会为view中使用的所有基表加锁，对于trigger同样如此。UNLOCK TABLES显示的释放当前session锁保持的锁。另外通过LOCK TABLEs语句为当前session获取新锁前会隐式的释放当前session之前的所有锁。
mysql> show open tables from test; +----------+---------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+---------------------+--------+-------------+ | test | rr | 0 | 0 | | test | innodb_lock_monitor | 0 | 0 | | test | t | 0 | 0 | | test | t1 | 0 | 0 | | test | user | 0 | 0 | +----------+---------------------+--------+-------------+ 5 rows in set (0.00 sec)
mysql> lock table t read; Query OK, 0 rows affected (0.00 sec) mysql> show open tables from test; +----------+---------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+---------------------+--------+-------------+ | test | rr | 0 | 0 | | test | innodb_lock_monitor | 0 | 0 | | test | t | 1 | 0 | | test | t1 | 0 | 0 | | test | user | 0 | 0 | +----------+---------------------+--------+-------------+ 5 rows in set (0.01 sec)
mysql> lock table t1 read; Query OK, 0 rows affected (0.00 sec) mysql> show open tables from test; 表t的锁释放 +----------+---------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+---------------------+--------+-------------+ | test | rr | 0 | 0 | | test | innodb_lock_monitor | 0 | 0 | | test | t | 0 | 0 | | test | t1 | 1 | 0 | | test | user | 0 | 0 | +----------+---------------------+--------+-------------+ 5 rows in set (0.01 sec)
mysql> insert into t select 2; ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES mysql> insert into t1 select 10,10; ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updat
mysql> insert into rr select 10; ERROR 1100 (HY000): Table 'rr' was not locked with LOCK TABLES
会话2：
mysql> insert into t1 select 10,10; ^CCtrl-C -- sending "KILL QUERY 2" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted
mysql> insert into t select 10,10; Query OK, 1 row affected (0.18 sec) Records: 1 Duplicates: 0 Warnings: 0
UNLOCK TABLES还还以用来释放FLUSH TABLES WITH READ LOCKS获取的全局锁，该锁锁定所有库的所有表。（稍后详细介绍）
对非临时表执行LOCK TABLES不会影响对临时表的访问。因为该类表可以且仅可以由创建它的session访问且不管什么类型的锁都对其没有影响。
mysql> create temporary table tmp like t; Query OK, 0 rows affected (0.22 sec) mysql> show create table tmp; CREATE TEMPORARY TABLE `tmp` ( `id` int(11) DEFAULT NULL, `val` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 mysql> select * from tmp; Empty set (0.00 sec) mysql> lock table t read; Query OK, 0 rows affected (0.01 sec) mysql> insert into rr select 10,10; ERROR 1100 (HY000): Table 'rr' was not locked with LOCK TABLES
mysql> insert into tmp select 10,10; 临时表可以插入 Query OK, 1 row affected (0.18 sec) Records: 1 Duplicates: 0 Warnings: 0
但是对临时表执行LOCK TABLES，可以对其他非临时表产生影响。
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> lock table tmp read; Query OK, 0 rows affected (0.01 sec) mysql> select * from r; ERROR 1100 (HY000): Table 'r' was not locked with LOCK TABLES
mysql> select * from tmp; +------+------+ | id | val | +------+------+ | 10 | 10 | +------+------+ 1 row in set (0.00 sec) mysql> insert into rr select 100,100; 非临时表也产生影响了 ERROR 1100 (HY000): Table 'rr' was not locked with LOCK TABLES
mysql> insert into tmp select 100,100; 临时表照样可以插入 Query OK, 1 row affected (0.17 sec) Records: 1 Duplicates: 0 Warnings: 0
3获取锁的规则
有下列可用的锁类型
Ø READ[LOCAL] lock
ü 保持该锁的session可以读取加锁的表，但不能写，对于没有加锁的表不能读也不能写。
会话1：
mysql> lock table rr read; Query OK, 0 rows affected (0.00 sec) mysql> select * from rr; +------+------+------+------+------+------+ | a | xx | xxx | xxxx | zz | rr | +------+------+------+------+------+------+ | 1 | NULL | NULL | NULL | NULL | NULL | +------+------+------+------+------+------+ 1 row in set (0.17 sec) mysql> select * from t; //没加锁的表也不能读与写 ERROR 1100 (HY000): Table 'r' was not locked with LOCK TABLES mysql> insert into rr select 1,2,3,4,5,6; ERROR 1099 (HY000): Table 'rr' was locked with a READ lock and can't be updated
另外一个会话是可以查询的，插入的 ，但rr 表不可插入
mysql> select * from t; +------+------+ | id | val | +------+------+ | 1 | 3 | | 2 | 4 | | 5 | 6 | | 5 | 6 | | 8 | 9 | | 5 | 6 | | 8 | 9 | +------+------+ 7 rows in set (0.00 sec) mysql> insert into t select 100,100; Query OK, 1 row affected (0.18 sec) Records: 1 Duplicates: 0 Warnings: 0
ü 多个session可同时为同一个表获取READ锁
session1
mysql> lock tables rr read; Query OK, 0 rows affected (0.00 sec) mysql> show open tables from test; +----------+---------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+---------------------+--------+-------------+ | test | rr | 1 | 0 | | test | innodb_lock_monitor | 0 | 0 | | test | t | 0 | 0 | | test | t1 | 0 | 0 | | test | user | 0 | 0 | +----------+---------------------+--------+-------------+ 5 rows in set (0.01 sec)
session2
mysql> lock tables rr read; Query OK, 0 rows affected (0.00 sec) mysql> show open tables from test; +----------+---------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+---------------------+--------+-------------+ | test | rr | 2 | 0 | | test | innodb_lock_monitor | 0 | 0 | | test | t | 0 | 0 | | test | t1 | 0 | 0 | | test | user | 0 | 0 | +----------+---------------------+--------+-------------+ 5 rows in set (0.01 sec)
session3
mysql> lock tables rr read; Query OK, 0 rows affected (0.00 sec) mysql> show open tables from test; +----------+---------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+---------------------+--------+-------------+ | test | rr | 3 | 0 | | test | innodb_lock_monitor | 0 | 0 | | test | t | 0 | 0 | | test | t1 | 0 | 0 | | test | user | 0 | 0 | +----------+---------------------+--------+-------------+ 5 rows in set (0.01 sec)
ü 当前session只能读取明确获取了READ锁的表，不能更新该表，也不能读取和更新其他没有获取READ锁的表。其他的session可以读取没有在当前session中明确获取READ锁的表，当然也可以读取获取了READ锁的表。但是更新在其他session中获取了READ锁的表会被阻塞，可以正常更新其他的表。
session1(当前session)
mysql> select * from rr; +------+------+------+------+------+------+ | a | xx | xxx | xxxx | zz | rr | +------+------+------+------+------+------+ | 1 | NULL | NULL | NULL | NULL | NULL | +------+------+------+------+------+------+ 1 row in set (0.01 sec) mysql> select * from t; ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES
mysql> insert into rr select 1,2,3,4,5,6; ERROR 1099 (HY000): Table 'rr' was locked with a READ lock and can't be updated
session2
mysql> select * from rr; +------+------+------+------+------+------+ | a | xx | xxx | xxxx | zz | rr | +------+------+------+------+------+------+ | 1 | NULL | NULL | NULL | NULL | NULL | +------+------+------+------+------+------+ 1 row in set (0.01 sec) mysql> select * from t; +------+------+ | id | val | +------+------+ | 1 | 3 | | 2 | 4 | | 5 | 6 | | 5 | 6 | | 8 | 9 | | 5 | 6 | | 8 | 9 | | 100 | 100 | +------+------+ 8 rows in set (0.01 sec) mysql> insert into rr select 1,2,3,4,5,6;
等待
mysql> insert into rr select 1,2,3,4,5,6; ^CCtrl-C -- sending "KILL QUERY 2" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted mysql> insert into t select 1,2; Query OK, 1 row affected (0.19 sec) Records: 1 Duplicates: 0 Warnings: 0
LOCAL修饰符可以允许在其他session中对在当前session中获取了READ锁的的表执行插入。但是当在保持锁时若使用server外的线程来操纵数据库则不能使用READ LOCAL。另外，对于InnoDB表，READ LOCAL与READ相同。
Ø [LOW_PRIORITY]WRITE LOCK
ü 保持该锁的session可读/写获取了WRITE锁的表，但不可以读/写其他表。其他的session可以读取和更新没有在当前session中获取WRITE锁的表，但其他session在读取和更新在当前session中获取了WRITE锁的表时会阻塞。
mysql> lock table rr write; Query OK, 0 rows affected (0.00 sec) mysql> select * from t; ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES
mysql> insert into rr select 1,2,3,4,5,6; Query OK, 1 row affected (0.19 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t select 1,2; ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES
不能同时在多个session中为同一个table获得WRITE锁。
mysql> lock table rr write;
等待
LOW_PRIORITY修饰符用于之前版本的MySQL，影响锁定行为。但在MySQL5.6.5之后不再使用该选项。
需在单条LOCK TABLES语句中获取所有所需的锁，因为每次重新执行LOCK TABLES语句会释放之前获取的锁。一旦保持了获取的锁，该session只能访问锁定的表。但是，INFORMATION_SCHEMA数据库是个例外。
不能在单条查询中使用相同的名称多次引用锁定的表。需使用别名，且为别名获取单独的锁。可以看到会在a表上加两个锁。第一次执行INSERT因为包含了对锁定的表的同一名称的多次引用而发生错误。第二次执行INSERT操作使用了别名不会发生上述问题（这里显示的问题是主键冲突的问题）
mysql> show open tables from test; +----------+---------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+---------------------+--------+-------------+ | test | rr | 0 | 0 | | test | innodb_lock_monitor | 0 | 0 | | test | t | 0 | 0 | | test | t1 | 0 | 0 | | test | user | 0 | 0 | +----------+---------------------+--------+-------------+ 5 rows in set (0.00 sec) mysql> lock table rr write ,rr as a1 read; Query OK, 0 rows affected (0.00 sec) mysql> show open tables from test; +----------+---------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+---------------------+--------+-------------+ | test | rr | 2 | 0 | | test | innodb_lock_monitor | 0 | 0 | | test | t | 0 | 0 | | test | t1 | 0 | 0 | | test | user | 0 | 0 | +----------+---------------------+--------+-------------+ 5 rows in set (0.00 sec) mysql> insert into rr select * from rr; ERROR 1100 (HY000): Table 'rr' was not locked with LOCK TABLES
mysql> insert into rr select * from rr as a1; Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0
如果语句通过别名引用表，那么锁定表时需使用相同的别名。
mysql> lock table rr read; Query OK, 0 rows affected (0.00 sec) mysql> select * from a as a1; ERROR 1100 (HY000): Table 'a1' was not locked with LOCK TABLES mysql> lock table rr as a1 read; Query OK, 0 rows affected (0.00 sec) mysql> select * from rr as a1; +------+------+------+------+------+------+ | a | xx | xxx | xxxx | zz | rr | +------+------+------+------+------+------+ | 1 | NULL | NULL | NULL | NULL | NULL | | 1 | 2 | 3 | 4 | 5 | 6 | | 1 | NULL | NULL | NULL | NULL | NULL | | 1 | 2 | 3 | 4 | 5 | 6 | +------+------+------+------+------+------+ 4 rows in set (0.01 sec)
WRITE lock优先级通常高于READ lock，这意味着若一个session为表获取了一个READ锁，之后另一session为该表请求WRITE锁，那么后续的READ锁请求会一直等到请求WRITE锁的session获取锁并释放锁后才能满足。
LOCKTABLE语句获取锁的过程
Ø 按内部定义的顺序排序将要被锁定的表
Ø 若一个表将要获取READ和WRITE锁，则将WRITE锁请求放在READ锁请求前
Ø 逐个锁定表
释放锁的规则
Ø 一个session锁保持的锁被释放时所有的锁同时全部释放。
Ø session可通过UNLOCK TABLES语句明确的释放其保持的锁。
Ø 若已经保持有锁的session通过LOCK TABLES语句来获得新的锁，那么原来已经存在的锁在新锁获得前会被隐式释放。
Ø 若session开启了事务，那么会隐式的执行UNLOCK TABLES语句释放其所有的锁。
Ø 若session连接断开，无论是正常断开还是异常断开，服务器会隐式的释放该session保持的所有锁。即使客户端重新连接也不会再重新获得这些锁。
Ø 若在锁定的表上使用ALTER TABLE语句，则该表会变为未锁定的。
参考