sOlOHsU's Blogβ

灯火阑珊处

MySQL锁表与解锁

LOCK TABLES

锁表语法

1
2
3
4
5
6
7

  LOCK TABLES
      tbl_name [[AS] alias] lock_type
      [, tbl_name [[AS] alias] lock_type] ...
  
  lock_type:
      READ [LOCAL]
    | [LOW_PRIORITY] WRITE

会话只能为自己获取、释放锁，不能为其他会话获取锁，也不能释放其他会话持有的锁。

LOCK TABLES隐式的为当前会话获取表锁。表锁的对象可以是表也可以是视图。

获取视图锁时，LOCK TABLES命令会自动锁定所有与该视图相关的基本表。

使用LOCK TABLES显示的锁定一个表时，这个表的触发器中涉及到的表也会被隐式的锁定，锁的类型取决于触发器中对该表进行了何种操作。

表锁只是用来防止其他会话进行不恰当的操作，持有锁的会话，即使持有的仅仅是读锁，也可以进行诸如DROP TABLE之类的表级操作。而由于TRUNCATE操作不是事务安全的，所以在活动的事务或者持有表锁时尝试执行TRUNCATE操作时会报错。

另外，虽然可以对临时表执行锁表操作，但是实际上会被自动忽略掉，不执行任何操作。因为临时表只对当前会话（也就是创建它的会话）是可见的。

锁的类型

READ [LOCAL]锁

  • 持有读锁的会话可以读表，但是不能写表。
  • 多个会话可以同时获得同一个表的读锁。
  • 其他的会话不用获取该表的读锁也可以读取该表。
  • LOCAL修饰符允许其他会话执行无冲突的插入语句。但是，如果是在服务器之外的进程上进行操作时，就不能使用READ LOCAL了。另外，对于使用InnoDB引擎的表来说，READ LOCAL与READ没有任何区别。

[LOW_PRIORITY] WRITE锁

  • 持有写锁的会话可以读表也可以写表。
  • 只有持有写锁的会话可以访问该表。写锁释放前其他会话都不能对该表进行访问。
  • 表被写锁锁定时，其他会话对该表的锁定请求将会被阻塞。
  • LOW_PRIORITY修饰符已被弃用（当前版本为5.7）。直接使用WRITE即可。

锁表语句会一直阻塞直到获取到所有需要的锁。 需要对表进行锁定的会话必须一次性的获取到所有它需要锁，因为一旦锁表语句执行完成，该会话就只能访问被锁的表了。

1
2
3
4
5
6
7
8
9

   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

INFORMATION_SCHEMA数据库中的表是个例外。即使有会话对这些表进行了锁定，其他会话依然可以继续访问。

在一条查询语句中无法多次对锁定的表使用同一个名字进行引用。这种情况下可以使用表的别名，并且对每一个别也要加一个锁：

1
2
3
4

   mysql> LOCK TABLE t WRITE, t AS t1 READ;
  mysql> INSERT INTO t SELECT * FROM t;
  ERROR 1100: Table 't' was not locked with LOCK TABLES
  mysql> INSERT INTO t SELECT * FROM t AS t1;

不对表的别名进行锁定的话，就无法使用别名对该表进行访问：

1
2
3

   mysql> LOCK TABLE t READ;
  mysql> SELECT * FROM t AS myalias;
  ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

相反，如果只对别名进行了锁定，则只能使用别名对其进行访问：

1
2
3
4

   mysql> LOCK TABLE t AS myalias READ;
  mysql> SELECT * FROM t;
  ERROR 1100: Table 't' was not locked with LOCK TABLES
  mysql> SELECT * FROM t AS myalias;

写锁通常相对读锁有更高的优先级，以确保更新操作尽快得到处理。这就意味着如果同时有写锁和读锁两个请求，那么写锁会先得到响应。LOCK TABLES获取锁的过程如下：

  1. 使用内部定义的顺序对所有将被加锁的表进行排序，这一步对于用户来说是透明的。
  2. 如果一个表同时有读锁和写锁两个请求，将写锁请求放到读锁请求之前。
  3. 每次锁定一个表直到当前会话获取到所有的需要的锁，并保证不会产生死锁。

对分表进行锁定和解锁时，会锁定或者解锁整个表。

UNLOCK TABLES

解锁语法

1

  UNLOCK TABLES

使用UNLOCK TABLES将显示的释放掉由当前会话持有的所有表锁。

LOCK TABLES会在获取新锁之前隐式的释放掉当前会话持有的所有表锁。

会话开始一个事务（例如使用START TRANSACTION）时，会隐式的执行UNLOCK TABLES。

无论会话正常退出或者异常终止，服务器都会隐式的释放掉该会话持有的所有锁。客户端重新连接后，这些锁不会再生效。另外，如果客户端断开时正在执行一个事务，服务器会自动回滚该事务，客户端重连后，新会话将会自动设置为自动提交模式。因此，最好禁用客户端的auto-reconnec。在启用自动重连的情况下，重连后客户端其实已经丢失了所有的锁和事务，但是不会被通知。而当禁用掉自动重连时，一旦连接断开，在一个将被执行的语句处会报错。这样一来客户端就可以检测到这个错误，并采取适当的措施，比如重新获取需要的锁，重新执行之前的事务。

在被锁定的表上执行ALTER TABLE语句后，将使该表变为未锁定状态。如果对该表再执行一条ALTER TABLE语句，就会报错：Table ‘tbl_name’ was not locked with LOCK TABLES。这种情况下可以在执行完第一条ALTER TABLE后，重新对该表进行锁定。

UNLOCK TABLES还可以用来释放之前通过FLUSH TABLES WITH READ LOCK获得的全局读锁。