关于 MySQL中lock tables和unlock tables

栏目: 数据库 · 发布时间: 7年前

内容简介:关于 MySQL中lock tables和unlock tables

LOCK TABLES    tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}    [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...UNLOCK TABLESLOCK TABLES可以锁定用于当前线程的表。如果表被其它线程锁定,则造成堵塞,直到可以获取所有锁定为止。UNLOCK TABLES可以释放被当前线程保持的任何锁定。当线程发布另一个LOCK TABLES时,或当与服务器的连接被关闭时,所有由当前线程锁定的表被隐含地解锁。

表锁定只用于防止其它客户端进行不正当地读取和写入。保持锁定(即使是读取锁定)的客户端可以进行表层级的操作,比如DROP TABLE。

注意,下面是对事务表使用LOCK TABLES的说明:

·        在尝试锁定表之前,LOCK TABLES不是事务安全型的,会隐含地提交所有活性事务。同时,开始一项事务(例如,使用START TRANSACTION),会隐含地执行UNLOCK TABLES

·        对事务表(如InnoDB)使用LOCK TABLES的正确方法是,设置AUTOCOMMIT=0并且不能调用UNLOCK TABLES,直到您明确地提交事务为止。当您调用LOCK TABLES时,InnoDB会内部地取其自己的表锁定,MySQL取其自己的表锁定。InnoDB在下一个提交时释放其表锁定,但是,对于MySQL,要释放表锁定,您必须调用UNLOCK TABLES。您不应该让AUTOCOMMIT=1,因为那样的话,InnoDB会在调用LOCK TABLES之后立刻释放表锁定,并且很容易形成死锁定。注意,如果AUTOCOMMIT=1,我们根本不能获取InnoDB表锁定,这样就可以帮助旧的应用软件避免不必要的死锁定。

·        ROLLBACK不会释放 MySQL 的非事务表锁定。

要使用LOCK TABLES,您必须拥有相关表的LOCK TABLES权限和SELECT权限。

使用LOCK TABLES的主要原因是仿效事务,或在更新表时加快速度。这将在后面进行更详细的解释。

如果一个线程获得对一个表地READ锁定,该线程(和所有其它线程)只能从该表中读取。如果一个线程获得对一个表的WRITE锁定,只有保持锁定的线程可以对表进行写入。其它的线程被阻止,直到锁定被释放时为止。

READ LOCAL和READ之间的区别是,READ LOCAL允许在锁定被保持时,执行非冲突性INSERT语句(同时插入)。但是,如果您正打算在MySQL外面操作数据库文件,同时您保持锁定,则不能使用READ LOCAL。对于InnoDB表,READ LOCAL与READ相同。

当您使用LOCK TABLES时,您必须锁定您打算在查询中使用的所有的表。虽然使用LOCK TABLES语句获得的锁定仍然有效,但是您不能访问没有被此语句锁定的任何的表。同时,您不能在一次查询中多次使用一个已锁定的表——使用别名代替,在此情况下,您必须分别获得对每个别名的锁定。

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;mysql> INSERT INTO t SELECT * FROM t;ERROR 1100: Table 't' was not locked with LOCK TABLESmysql> INSERT INTO t SELECT * FROM t AS t1;如果您的查询使用一个别名引用一个表,那么您必须使用同样的别名锁定该表。如果没有指定别名,则不会锁定该表。

mysql> LOCK TABLE t READ;mysql> SELECT * FROM t AS myalias;ERROR 1100: Table 'myalias' was not locked with LOCK TABLES相反的,如果您使用一个别名锁定一个表,您必须使用该别名在您的查询中引用该表。

mysql> LOCK TABLE t AS myalias READ;mysql> SELECT * FROM t;ERROR 1100: Table 't' was not locked with LOCK TABLESmysql> SELECT * FROM t AS myalias;WRITE锁定通常比READ锁定拥有更高的优先权,以确保更新被尽快地处理。这意味着,如果一个线程获得了一个READ锁定,则另一个线程会申请一个WRITE锁定,后续的READ锁定申请会等待,直到WRITE线程获得锁定并释放锁定。您可以使用LOW_PRIORITY WRITE锁定来允许其它线程在该线程正在等待WRITE锁定时获得READ锁定。只有当您确定最终将有一个时机,此时没有线程拥有READ锁定时,您才应该使用LOW_PRIORITY WRITE锁定。

LOCK TABLES按照如下方式执行:

1.    按照内部定义的顺序,对所有要被锁定的表进行分类。从用户的角度,此顺序是未经定义的。

2.    如果使用一个读取和一个写入锁定对一个表进行锁定,则把写入锁定放在读取锁定之前。

3.    一次锁定一个表,直到线程得到所有锁定为止。

该规则确保表锁定不会出现死锁定。但是,对于该规则,您需要注意其它的事情:

如果您正在对一个表使用一个LOW_PRIORITY WRITE锁定,这只意味着,MySQL等待特定的锁定,直到没有申请READ锁定的线程时为止。当线程已经获得WRITE锁定,并正在等待得到锁定表清单中的用于下一个表的锁定时,所有其它线程会等待WRITE锁定被释放。如果这成为对于应用程序的严重的问题,则您应该考虑把部分表转化为事务安全型表。

您可以安全地使用KILL来结束一个正在等待表锁定的线程。

注意,您不能使用INSERT DELAYED锁定任何您正在使用的表,因为,在这种情况下,INSERT由另一个线程执行。

通常,您不需要锁定表,因为所有的单个UPDATE语句都是原子性的;没有其它的线程可以干扰任何其它当前正在执行的 SQL 语句。但是,在几种情况下,锁定表会有好处:

·        如果您正在对一组MyISAM表运行许多操作,锁定您正在使用的表,可以快很多。锁定MyISAM表可以加快插入、更新或删除的速度。不利方面是,没有线程可以更新一个用READ锁定的表(包括保持锁定的表),也没有线程可以访问用WRITE锁定的表(除了保持锁定的表以外)。

有些MyISAM操作在LOCK TABLES之下更快的原因是,MySQL不会清空用于已锁定表的关键缓存,直到UNLOCK TABLE被调用为止。通常,关键缓存在每个SQL语句之后被清空。

·        如果您正在使用MySQL中的一个不支持事务的存储引擎,则如果您想要确定在SELECT和UPDATE之间没有其它线程,您必须使用LOCK TABLES。本处所示的例子要求LOCK TABLES,以便安全地执行:

·                mysql> LOCK TABLES trans READ, customer WRITE;·                mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;·                mysql> UPDATE customer·                    ->    SET total_value=sum_from_previous_statement·                    ->    WHERE customer_id=some_id;·                mysql> UNLOCK TABLES;如果没有LOCK TABLES,有可能另一个线程会在执行SELECT和UPDATE语句之间在trans表中插入一个新行。

通过使用相对更新(UPDATE customer SET value=value+new_value)或LAST_INSERT_ID()函数,您可以在许多情况下避免使用LOCK TABLES。

通过使用用户层级的顾问式锁定函数GET_LOCK()和RELEASE_LOCK(),您也可以在有些情况下避免锁定表。这些锁定被保存在服务器中的一个混编表中,使用pthread_mutex_lock() 和pthread_mutex_unlock(),以加快速度。

要了解更多有关锁定规则的说明

您可以使用FLUSH TABLES WITH READ LOCK语句锁定位于所有带有读取锁定的数据库中的所有表。如果您有一个可以及时拍摄快照的文件系统,比如Veritas,这是获得备份的一个非常方便的方式。

注释:如果您对一个已锁定的表使用ALTER TABLE,该表可能会解锁。

注意:LOCK TABLES 命令与事务处理之间的交互作用,是比较复杂的。在某些服务器版本上可能会导致产生一些不可预料的行为。因此建议,除非是在一个事务中使用LOCK TABLES,同时AUTOCOMMIT模式被禁止的,否则,无论使用何种存储引擎,都不要用LOCK TABLES 命令 。

本文永久更新链接地址 http://www.linuxidc.com/Linux/2017-06/144705.htm


以上所述就是小编给大家介绍的《关于 MySQL中lock tables和unlock tables》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Measure What Matters

Measure What Matters

John Doerr / Portfolio / 2018-4-24 / GBP 19.67

In the fall of 1999, John Doerr met with the founders of a start-up he’d just given $11.8 million, the biggest investment of his career. Larry Page and Sergey Brin had amazing technology, entrepreneur......一起来看看 《Measure What Matters》 这本书的介绍吧!

随机密码生成器
随机密码生成器

多种字符组合密码

RGB HSV 转换
RGB HSV 转换

RGB HSV 互转工具

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具