这期内容当中小编将会给大家带来有关MySQL中Lock Tables和Unlock Tables的作用是什么，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

锁定表的语法：

LOCKTABLEStbl_name[ASalias]{READ[LOCAL]|[LOW_PRIORITY]WRITE}[,tbl_name[ASalias]{READ[LOCAL]|[LOW_PRIORITY]WRITE}]...

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

The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 8.11.3, “Concurrent Inserts”.) However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ.

修饰符LOW_PRIORITY用于之前版本的MySQL，它会影响锁定行为,但是从MySQL 5.6.5以后，这个修饰符已经被弃用。如果使用它则会产生警告。

[LOW_PRIORITY]WRITElock:Thesessionthatholdsthelockcanreadandwritethetable.Onlythesessionthatholdsthelockcanaccessthetable.Noothersessioncanaccessituntilthelockisreleased.LockrequestsforthetablebyothersessionsblockwhiletheWRITElockisheld.TheLOW_PRIORITYmodifierhasnoeffect.InpreviousversionsofMySQL,itaffectedlockingbehavior,butthisisnolongertrue.AsofMySQL5.6.5,itisdeprecatedanditsuseproducesawarning.UseWRITEwithoutLOW_PRIORITYinstead.

解锁表的语法：

UNLOCK TABLES

LOCK TABLES为当前会话锁定表。UNLOCK TABLES释放被当前会话持有的任何锁。官方文档“13.3.5 LOCK TABLES and UNLOCK TABLES Syntax”已经对LOCK TALES与UNLOCK TABLES做了不少介绍，下面我们通过一些测试例子来深入的理解一下锁表与解锁表的相关知识点。我们先准备一下测试环境用的表和数据。

mysql>createtabletest(idint,namevarchar(12));QueryOK,0rowsaffected(0.07sec)mysql>insertintotest->select10001,'kerry'unionall->select10002,'richard'unionall->select10003,'jimmy';QueryOK,3rowsaffected(0.05sec)Records:3Duplicates:0Warnings:0mysql>

当前会话(会话ID为61)持有test表的READ锁后，那么当前会话只可以读该表，而不能往表中写入数据，否则就会报“Table 'test' was locked with a READ lock and can't be updated”这样的错误。

注意：如果使用LOCK TABLE WRITE锁定表后，则可以更新数据。详见后面介绍

mysql>selectconnection_id();+-----------------+|connection_id()|+-----------------+|61|+-----------------+1rowinset(0.00sec)mysql>showopentableswherein_use>=1;Emptyset(0.00sec)mysql>locktablestestread;QueryOK,0rowsaffected(0.00sec)mysql>showopentableswherein_use>=1;+----------+-------+--------+-------------+|Database|Table|In_use|Name_locked|+----------+-------+--------+-------------+|MyDB|test|1|0|+----------+-------+--------+-------------+1rowinset(0.01sec)mysql>select*fromtest;+-------+---------+|id|name|+-------+---------+|10001|kerry||10002|richard||10003|jimmy|+-------+---------+3rowsinset(0.00sec)mysql>insertintotest->values(10004,'ken');ERROR1099(HY000):Table'test'waslockedwithaREADlockandcan'tbeupdatedmysql>

其它会话也能查询表test，但是不能修改表，如果执行DML操作的话，则会一直处于被阻塞状态(Waiting for table metadata lock)。

另外，我们测试一下修饰符LOCAL的用途，如下所示：

mysql>createtabletest2(idint,namevarchar(12))engine=MyISAM;QueryOK,0rowsaffected(0.05sec)mysql>insertintotest2->select1001,'test';QueryOK,1rowaffected(0.00sec)Records:1Duplicates:0Warnings:0mysql>selectconnection_id();+-----------------+|connection_id()|+-----------------+|66|+-----------------+1rowinset(0.00sec)mysql>locktablestest2readlocal;QueryOK,0rowsaffected(0.00sec)mysql>select*fromtest2;+------+------+|id|name|+------+------+|1001|test|+------+------+1rowinset(0.00sec)mysql>insertintotest2->select1002,'kkk';ERROR1099(HY000):Table'test2'waslockedwithaREADlockandcan'tbeupdatedmysql>

但是在其它会话当中，你可以看到表test2可以被插入。当然前提是表的存储引擎不能是innodb引擎，否则使用修饰符LOCAL和不用LOCAL是一样的，其它会话无法对表写入。

mysql>selectconnection_id();+-----------------+|connection_id()|+-----------------+|65|+-----------------+1rowinset(0.00sec)mysql>select*fromtest2;+------+------+|id|name|+------+------+|1001|test|+------+------+1rowinset(0.00sec)mysql>insertintotest2->select1002,'kkk';QueryOK,1rowaffected(0.00sec)Records:1Duplicates:0Warnings:0

那么其他会话是否也能读此表呢? 其它会话能否也能锁定该表(LOCK TABLES READ LOCAL)?其它会话是否也能锁定写(LOCK TABLE WRITE)呢?。关于这些疑问，其它会话也能读此表，其它表也能锁定该表(LOCK TABLES READ LOCAL)，但是不能LOCK TABLE WRITE。

对于MyISAM表，现在用的比较少，我们还是用InnoDB类型的表来实验一下，在其中一个会话使用lock table锁定表test，

mysql>selectconnection_id();+-----------------+|connection_id()|+-----------------+|61|+-----------------+1rowinset(0.00sec)mysql>locktabletestread;QueryOK,0rowsaffected(0.00sec)mysql>showopentableswherein_use>=1;+----------+-------+--------+-------------+|Database|Table|In_use|Name_locked|+----------+-------+--------+-------------+|MyDB|test|1|0|

然后在会话62中进行下面测试：

mysql>selectconnection_id();+-----------------+|connection_id()|+-----------------+|62|+-----------------+1rowinset(0.01sec)mysql>select*fromtest;+-------+---------+|id|name|+-------+---------+|10001|kerry||10002|richard||10003|jimmy|+-------+---------+3rowsinset(0.00sec)mysql>locktablestestread;QueryOK,0rowsaffected(0.00sec)mysql>showopentableswherein_use>=1;+----------+-------+--------+-------------+|Database|Table|In_use|Name_locked|+----------+-------+--------+-------------+|MyDB|test|2|0|+----------+-------+--------+-------------+1rowinset(0.00sec)mysql>unlocktables;QueryOK,0rowsaffected(0.00sec)mysql>showopentableswherein_use>=1;+----------+-------+--------+-------------+|Database|Table|In_use|Name_locked|+----------+-------+--------+-------------+|MyDB|test|1|0|+----------+-------+--------+-------------+1rowinset(0.00sec)mysql>locktablestestwrite;

如上测试所示，如果一个会话在一个表上获得一个READ锁后，所有其他会话只能从表中读。不能往表中写，其它会话也可在该表上获取一个READ锁，此时你会在show open tables里面看到in_use的值增加。其实LOCK TABLES READ是一个表锁，而且是共享锁。但是当一个会话获取一个表上的READ锁后，其它会话就不能获取该表的WRITE锁了，此时就会被阻塞，直到持有READ锁的会话释放READ锁。

该会话(会话61)中则可以继续获取WRITE锁。当该会话获取WRITE锁后，其它会话则无法获取READ锁了

mysql>locktabletestwrite;QueryOK,0rowsaffected(0.00sec)

另外需要注意的是，当前会话如果锁定了其中一个表，那么是无法查询其它表的。否则会报“ERROR 1100 (HY000): Table 'worklog' was not locked with LOCK TABLES”错误。

那么我们再来看看WRITE锁吧。测试前，先在上面两个会话中执行 unlock tables命令。然后获得表TEST上的一个WRITE锁，如下所示，当前会话可以读写表TEST

mysql>unlocktables;QueryOK,0rowsaffected(0.00sec)mysql>selectconnection_id();+-----------------+|connection_id()|+-----------------+|61|+-----------------+1rowinset(0.00sec)mysql>showopentableswherein_use>=1;Emptyset(0.00sec)mysql>locktablestestwrite;QueryOK,0rowsaffected(0.00sec)mysql>select*fromtest;+-------+---------+|id|name|+-------+---------+|10001|kerry||10002|richard||10003|jimmy|+-------+---------+3rowsinset(0.00sec)mysql>updatetestsetname='ken'whereid=10003;QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0mysql>

其它会话无法读写表TEST，都会被阻塞，当然也无法获取表TEST的READ锁或WRITE锁。也就是说当一个会话获得一个表上的一个WRITE锁后，那么只有持锁的会话才能READ或WRITE表，其他会话都会被阻止。

mysql>unlocktables;QueryOK,0rowsaffected(0.00sec)mysql>mysql>mysql>showopentableswherein_use>=1;+----------+-------+--------+-------------+|Database|Table|In_use|Name_locked|+----------+-------+--------+-------------+|MyDB|test|1|0|+----------+-------+--------+-------------+1rowinset(0.00sec)mysql>select*fromtest;

mysql>selectconnection_id();+-----------------+|connection_id()|+-----------------+|63|+-----------------+1rowinset(0.00sec)mysql>showprocesslist;+----+------+-----------+------+---------+------+---------------------------------+--------------------+|Id|User|Host|db|Command|Time|State|Info|+----+------+-----------+------+---------+------+---------------------------------+--------------------+|61|root|localhost|MyDB|Sleep|86||NULL||62|root|localhost|MyDB|Query|40|Waitingfortablemetadatalock|select*fromtest||63|root|localhost|MyDB|Query|0|init|showprocesslist||64|root|localhost|MyDB|Sleep|2551||NULL|+----+------+-----------+------+---------+------+---------------------------------+--------------------+4rowsinset(0.00sec)

UNLOCK TABLES释放被当前会话持有的任何锁，但是当会话发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前会话锁定的所有表会隐式被解锁。下面我们也可以测试看看

mysql>locktablestestread;QueryOK,0rowsaffected(0.00sec)mysql>showopentableswherein_use>=1;+----------+-------+--------+-------------+|Database|Table|In_use|Name_locked|+----------+-------+--------+-------------+|MyDB|test|1|0|+----------+-------+--------+-------------+1rowinset(0.00sec)mysql>locktablesworklogread;QueryOK,0rowsaffected(0.00sec)mysql>showopentableswherein_use>=1;+----------+---------+--------+-------------+|Database|Table|In_use|Name_locked|+----------+---------+--------+-------------+|MyDB|worklog|1|0|+----------+---------+--------+-------------+1rowinset(0.00sec)mysql>

那么我们如何在当前会话锁定多个表呢?如下所示：

mysql>showopentableswherein_use>=1;Emptyset(0.00sec)mysql>locktablestestread,worklogread;QueryOK,0rowsaffected(0.00sec)mysql>showopentableswherein_use>=1;+----------+---------+--------+-------------+|Database|Table|In_use|Name_locked|+----------+---------+--------+-------------+|MyDB|worklog|1|0||MyDB|test|1|0|+----------+---------+--------+-------------+2rowsinset(0.00sec)mysql>

另外，还有一些细节问题，LOCK TABLES是否可以为视图、触发器、临时表加锁呢?

mysql>createtabletest2(idint,sexbit);QueryOK,0rowsaffected(0.06sec)mysql>insertintotest2->select10001,1unionall->select10002,0unionall->select10003,1;QueryOK,3rowsaffected(0.02sec)Records:3Duplicates:0Warnings:0mysql>createviewv_test->as->selectt1.id,t1.name,t2.sex->fromtestt1leftjointest2t2ont1.id=t2.id;QueryOK,0rowsaffected(0.01sec)mysql>locktablesv_testread;QueryOK,0rowsaffected(0.00sec)mysql>showopentableswherein_use>=1;+----------+-------+--------+-------------+|Database|Table|In_use|Name_locked|+----------+-------+--------+-------------+|MyDB|test2|1|0||MyDB|test|1|0|+----------+-------+--------+-------------+2rowsinset(0.00sec)mysql>

如上测试所示，对于VIEW加锁，LOCK TABLES语句会为VIEW中使用的所有基表加锁。对触发器使用LOCK TABLE，那么就会锁定触发器中所包含的全部表(any tables used in triggers are also locked implicitly)

mysql>unlocktables;QueryOK,0rowsaffected(0.00sec)mysql>createtemporarytabletmpliketest;QueryOK,0rowsaffected(0.04sec)mysql>showopentableswherein_use>=1;Emptyset(0.00sec)mysql>selectdatabase();+------------+|database()|+------------+|MyDB|+------------+1rowinset(0.00sec)mysql>select*fromtmp;Emptyset(0.00sec)mysql>insertintotmp->select1001,'kerry';QueryOK,1rowaffected(0.01sec)Records:1Duplicates:0Warnings:0mysql>

LOCK TABLES 与 UNLOCK TABLES只能为自己获取锁和释放锁，不能为其他会话获取锁，也不能释放由其他会话保持的锁。一个对象获取锁，需具备该对象上的SELECT权限和LOCK TABLES权限。LOCK TABLES语句为当前会话显式的获取表锁。最后，关于LOCK TABLES与事务当中锁有那些异同，可以参考官方文档：

LOCK TABLES and UNLOCK TABLES interact with the use of transactions as follows:

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.

·UNLOCK TABLES implicitly commits any active transaction, but only if LOCK TABLES has been used to acquire table locks. For example, in the following set of statements,UNLOCK TABLES releases the global read lock but does not commit the transaction because no table locks are in effect:

上述就是小编为大家分享的MySQL中Lock Tables和Unlock Tables的作用是什么了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。