MySql中表锁Lock命令和unlock命令

傷城~ 2022-04-11 03:29 1311阅读 0赞

MySQL LOCK 命令的语法如下

LOCK TABLE table1 locktype, table2 locktype ...

对一个表解锁：

UNLOCK TABLE\[S\]

如：

lock table car write；

select \*　from car;

unlock table；

解锁命令不需要额外的参数，UNLOCK 将解除该用户所有的锁。

因为 LOCK 锁阻止其他用户的访问，所以应该尽可能快的执行 UNLOCK 命令。

### MySQL LOCK 类型 ###

MySQL LOCK 锁的类型有四种，每一种锁定的范围不同：

READ

所有的用户只能读取被锁表，不能对表进行修改（包括执行 LOCK 的用户），当表不存在 WRITE 写锁时 READ 读锁被执行。

READ LOCAL

除了允许 INSERT 命令以外执行的锁与 READ 相同。

WRITE

除了当前用户被允许读取和修改被锁表外，其他用户的所有访问被完全阻止。一个 WRITE 写锁被执行仅当所有其他锁取消时。

LOW PRIORITY WRITE

低优先级的读锁，在等待时间内（等待其他锁取消），其他用户的访问将被认为是执行了 READ 读锁，因此将增加等待时间

注意：MySQL的lock不能用于函数和存储过程中

在同一个连接当中，执行了一次lock table之后，如果再次执行lock table，那么之前被锁住的表就自动解锁了。

下面这张图可以证明：

![1355304756_5056.jpg][]

\-----------------------------------------------------------------

A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access t2 because it was not locked in the LOCK TABLES statement:

[MySQL][]> LOCK TABLES t1 READ;  
mysql> SELECT COUNT(\*) FROM t1;  
\+----------+  
| COUNT(\*) |  
\+----------+  
|        3 |  
\+----------+  
mysql> SELECT COUNT(\*) FROM t2;  
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

\-------------------------------------------------

在mysql中，如果使用了lock tables tb\_name \[,tb\_name\] read|write ，那么在unlock tables之前，都只能是用lock住的这几个表了。使用其他表的时候会提示没有被lock。

unlock tables可以用在这里解锁被lock住的表，也可以用在解锁flush tables with read lock；这个命令中来解锁。

\-------------------------------------------------

在获得 锁的过程中，lock table 命令可能会锁定比你指定的更多的表。这是因为，如果你的表中有trigger，那么为了功能能正常进行，在trigger中涉及的表也会被lock。[参考这里][Link 1]

\-------------------------------------------------------------------------------------------------

获得锁的过程

LOCK TABLES acquires locks as follows:  
Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.  
If a table is to be locked with a read and a write lock, put the write lock request before the read lock request.  
Lock one table at a time until the session gets all locks.  
This policy ensures that table locking is deadlock free.

-------------------------------------------------------------------------------------------

释放锁的条件：

***Rules for Lock Release***

When the table locks held by a session are released, they are all released at the same time. A session can release its locks explicitly, or locks may be released implicitly under certain conditions.

*  A session can release its locks explicitly with [`UNLOCK TABLES`][UNLOCK TABLES].
 *  If a session issues a [`LOCK TABLES`][UNLOCK TABLES] statement to acquire a lock while already holding locks, its existing locks arereleased implicitly before the new locks are granted.
 *  If a session begins a transaction (for example, with [`START TRANSACTION`][START TRANSACTION]), an implicit [`UNLOCK TABLES`][UNLOCK TABLES] is performed, which causes existing locks to be released.
 *  If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional). If the client reconnects, the locks will no longer be in effect.
 *   In addition, if the client had an active transaction, the server rolls back the transaction upon disconnect, and if reconnect occurs, the new session begins with autocommit enabled. For this reason, clients may wish to disable auto-reconnect. With auto-reconnect in effect, the client is not notified if reconnect occurs but any table locks or current transaction will have been lost. With auto-reconnect disabled, if the connection drops, an error occurs for the next statement issued. The client can detect the error and take appropriate action such as reacquiring the locks or redoing the transaction.【最好禁用客户端的“自动重连”机制，因为如果自动重连功能打开，那么断开的连接自动重连上之后是不会通知客户端的，那么此时之前得到的lock已经不存在了。从而会造成一些未知错误。】

# 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之前的所有锁。

![20131115163554781][]

![20131115163559046][]

![20131115163602359][]

![20131115163606859][]

UNLOCK TABLES还还以用来释放FLUSH TABLES WITH READ LOCKS获取的全局锁，该锁锁定所有库的所有表。（稍后详细介绍）

对非临时表执行LOCK TABLES不会影响对临时表的访问。因为该类表可以且仅可以由创建它的session访问且不管什么类型的锁都对其没有影响。

![20131115163611093][]

但是对临时表执行LOCK TABLES，可以对其他非临时表产生影响。

![20131115163615640][]

# 3获取锁的规则 #

**有下列可用的锁类型**

Ø  READ\[LOCAL\] lock

ü  保持该锁的session可以读取加锁的表，但不能写，对于没有加锁的表不能读也不能写。

![20131115163933734][]

ü  多个session可同时为同一个表获取READ锁

session1

![20131115163938984][]

session2

![20131115163944218][]

session3

![20131115163950015][]

ü  当前session只能读取明确获取了READ锁的表，不能更新该表，也不能读取和更新其他没有获取READ锁的表。其他的session可以读取没有在当前session中明确获取READ锁的表，当然也可以读取获取了READ锁的表。但是更新在其他session中获取了READ锁的表会被阻塞，可以正常更新其他的表。

session1(当前session)

![20131115163954890][]

![20131115164000421][]

session2

![20131115164006171][]

![20131115164013203][]

![20131115164018687][]

LOCAL修饰符可以允许在其他session中对在当前session中获取了READ锁的的表执行插入。但是当在保持锁时若使用server外的线程来操纵[数据库][MySQL]则不能使用READ LOCAL。另外，对于InnoDB表，READ LOCAL与READ相同。

Ø  \[LOW\_PRIORITY\]WRITE LOCK

ü  保持该锁的session可读/写获取了WRITE锁的表，但不可以读/写其他表。其他的session可以读取和更新没有在当前session中获取WRITE锁的表，但其他session在读取和更新在当前session中获取了WRITE锁的表时会阻塞。

![20131115164024625][]

![20131115164029578][]

不能同时在多个session中为同一个table获得WRITE锁。

![20131115164034656][]

LOW\_PRIORITY修饰符用于之前版本的[MySQL][]，影响锁定行为。但在MySQL5.6.5之后不再使用该选项。

需在单条LOCK TABLES语句中获取所有所需的锁，因为每次重新执行LOCK TABLES语句会释放之前获取的锁。一旦保持了获取的锁，该session只能访问锁定的表。但是，INFORMATION\_SCHEMA数据库是个例外。

不能在单条查询中使用相同的名称多次引用锁定的表。需使用别名，且为别名获取单独的锁。可以看到会在a表上加两个锁。第一次执行INSERT因为包含了对锁定的表的同一名称的多次引用而发生错误。第二次执行INSERT操作使用了别名不会发生上述问题（这里显示的问题是主键冲突的问题）

![20131115164039375][]

如果语句通过别名引用表，那么锁定表时需使用相同的别名。

![20131115164044281][]

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语句，则该表会变为未锁定的。

参考文献：

[https://blog.csdn.net/caomiao2006/article/details/52080451][https_blog.csdn.net_caomiao2006_article_details_52080451]

[1355304756_5056.jpg]: https://img-my.csdn.net/uploads/201212/12/1355304756_5056.jpg
[MySQL]: http://lib.csdn.net/base/14
[Link 1]: http://dev.mysql.com/doc/refman/5.1/en/lock-tables-and-triggers.html
[UNLOCK TABLES]: http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html
[START TRANSACTION]: http://dev.mysql.com/doc/refman/5.1/en/commit.html
[20131115163554781]: /images/20220411/40d06a42075748c09ef453b728fe4856.png
[20131115163559046]: /images/20220411/bf692be9cf7f42cba5289a683ac6d028.png
[20131115163602359]: /images/20220411/a2ce05c211a74ce497d449a9c35703ed.png
[20131115163606859]: /images/20220411/6c87c4d0d01d4942bc92bfeb3d027af0.png
[20131115163611093]: /images/20220411/0013e324e2ec4d88993b98fdfedad82d.png
[20131115163615640]: /images/20220411/9320a35a133f491989f2a38fc1462754.png
[20131115163933734]: /images/20220411/fc21b2512c1f4175a97bde5e9139f09d.png
[20131115163938984]: /images/20220411/0f300249401f4dd8abdec84262e2aa1a.png
[20131115163944218]: /images/20220411/bf87fae386564d6c90806279c88f2671.png
[20131115163950015]: /images/20220411/fe89b875f91c4fb990c7609a1e2a648c.png
[20131115163954890]: /images/20220411/21268811fbcf4691aa9c6e3b6dedcfbf.png
[20131115164000421]: /images/20220411/cefc81a3d12b4c06b734aa8df4ba519a.png
[20131115164006171]: /images/20220411/41be8da2410f4b2bac5511768ba2edf9.png
[20131115164013203]: /images/20220411/9a5c6dffe9cd4ccdb0563a15fd5cc18e.png
[20131115164018687]: /images/20220411/dd2c4c88540848f5997bfbdd4b89e6bc.png
[20131115164024625]: /images/20220411/dc9e749c5a5645beb3c062210d9ffb59.png
[20131115164029578]: /images/20220411/5fedcf07c26d41cfaa4c8b9f90c8697a.png
[20131115164034656]: /images/20220411/6a21c6cc92e64cd2868bf11ae6f05185.png
[20131115164039375]: /images/20220411/3bf9169586f94c39a693c20bd6f6bedd.png
[20131115164044281]: /images/20220411/0519060f56774d8d91a6a7e0d2538f69.png
[https_blog.csdn.net_caomiao2006_article_details_52080451]: https://blog.csdn.net/caomiao2006/article/details/52080451