Post Jobs

lock的场景浅析

图片 6

MySQL 之 Metadata Locking 研究

MySQL 在 5.5 中引入了 metadata lock. 顾名思义,metadata lock
不是为了保护表中的数据的,而是保护 database
objects(数据库对象)的。包括表结构、schema、存储过程、函数、触发器、mysql的调度事件(events).
要理解 metadata lock 最重要的一点就是:将 metadata
lock放到数据库事务的语义中来理解。metadata lock
的作用就是当一个事务在执行时,事务涉及到的所有元数据(metadata,也就是
database
objects),必须是安全的。比如你在一个事物中select一个table,必须保证该table在你的事物完成之前,她不会被删除了,或者不会被修改了。

 

  1. metadata lock 的作用

 

MySQL uses metadata locking to manage concurrent access to database
objects and to ensure data consistency. Metadata locking applies not
just to tables, but also to schemas and stored programs (procedures,
functions, triggers, and scheduled events).

 

metadata lock管理对database objects的并发访问,保证数据一致性。

 

2.metadata lock 会导致性能损耗和锁争用

 

Metadata locking does involve some overhead, which increases as query
volume increases. Metadata contention increases the more that multiple
queries attempt to access the same objects.

 

metadata lock 的引入导致一定的性能损耗。对同一个database
object的访问越多,就会越导致该对象上的metadata lock的争用。

 

3.

 

Metadata locking is not a replacement for the table definition cache,
and its mutexes and locks differ from the LOCK_open mutex.

 

metadata lock 并不是 为了替代 表定义缓存。其mutex和lock和 LOCK_open
mutex不一样。

 

4.

 

To ensure transaction serializability, the server must not permit one
session to perform a data definition language (DDL) statement on a table
that is used in an uncompleted explicitly or implicitly started
transaction in another session. The server achieves this by acquiring
metadata locks on tables used within a transaction and deferring release
of those locks until the transaction ends. A metadata lock on a table
prevents changes to the table’s structure. This locking approach has the
implication that a table that is being used by a transaction within one
session cannot be used in DDL statements by other sessions until the
transaction ends.

 

正在运行中的事务,必须要在事务开始时获得它要访问的所有的database
objects上的 metadata lock, 然后在事务结束时释放那些database
objects上的metadata lock. 事务和metadata
lock的关系是极其紧密的:有事务必然就必然有metadata
lock,事物结束就释放。metadata lock防止事物中的database objects
被修改,比如阻止事物中的table的结构被修改。所以事务中的database
objects上执行DDL会被阻塞,直到事务结束。

 

5.

 

This principle applies not only to transactional tables, but also to
nontransactional tables. Suppose that a session begins a transaction
that uses transactional table t and nontransactional table nt as
follows:

 

START TRANSACTION;

SELECT * FROM t;

SELECT * FROM nt;

The server holds metadata locks on both t and nt until the transaction
ends. If another session attempts a DDL or write lock operation on
either table, it blocks until metadata lock release at transaction end.
For example, a second session blocks if it attempts any of these
operations:

 

DROP TABLE t;

ALTER TABLE t …;

DROP TABLE nt;

ALTER TABLE nt …;

LOCK TABLE t … WRITE;

metadata
lock不仅仅涉及到事务引擎中的table,同样也适用于非事务引擎中的table.
metadata lock不仅仅阻塞DDL,同时也阻塞 lock table table_name write
语句。

 

6.

 

If the server acquires metadata locks for a statement that is
syntactically valid but fails during execution, it does not release the
locks early. Lock release is still deferred to the end of the
transaction because the failed statement is written to the binary log
and the locks protect log consistency.

 

如果一个sql语句语法正确,但是却执行失败了,其上的metadata
lock并不会马上释放,而是要在事务结束之后才释放。这是为了保证日志的一致性。

 

7.

 

In autocommit mode, each statement is in effect a complete transaction,
so metadata locks acquired for the statement are held only to the end of
the statement.

 

自动提交模式(mysql命令行工具默认是自动提交模式),语句一执行完马上就释放metadata
lock,因为他是自动提交的单语句事务。

 

8.

 

Metadata locks acquired during a PREPARE statement are released once the
statement has been prepared, even if preparation occurs within a
multiple-statement transaction.

 

事务中的metadata
lock直到事务结束才释放,但是有一个特例:事务中的prepare(一般用在存储过程中的动态语句)语句一执行完马上释放对应的metadata
lock.

 

9.

 

Before MySQL 5.5, when a transaction acquired the equivalent of a
metadata lock for a table used within a statement, it released the lock
at the end of the statement. This approach had the disadvantage that if
a DDL statement occurred for a table that was being used by another
session in an active transaction, statements could be written to the
binary log in the wrong order.

 

MySQL 5.5 引入了metadata lock,取代了之前版本中的等价物。

 

但是metadata lock和她之前的等价物有一个区别:metadata
lock直到事务结束才释放,而她的等价物是语句执行完就马上释放。metadata
lock这样做的目的是为了保证 binary log 顺序的正确。

 

  1. 实验一(lock table xxx write 语句; 实验环境Centos下的mysql5.6.27)

 

首先在终端A执行:

 

mysql> lock table cats write;

Query OK, 0 rows affected (0.01 sec)

然后在终端B执行:

 

select * from cats;

图片 1

你会发现被阻塞了。

 

然后在终端A中执行:

 

mysql> show processlist;

+—-+——+———–+———+———+——+———————————+——————–+

| Id | User | Host  | db  | Command | Time | State   | Info   |

+—-+——+———–+———+———+——+———————————+——————–+

|  1 | root | localhost | ngx_lua | Query   | 2940 | Waiting for table
metadata lock | select * from cats |

|  2 | root | localhost | ngx_lua | Query   |0 | init| show processlist
  |

|  3 | root | localhost | NULL| Sleep   | 2913 | | NULL   |

+—-+——+———–+———+———+——+———————————+——————–+

3 rows in set (0.00 sec)

 

你可以看到 select * from cats 被阻塞的原因是:Waiting for table
metadata lock

 

而那个 Sleep 中的正是 lock table cats wirte 语句。它也持有了 cats 表上的
metadata lock 的,排斥其它任何事务对该metadata lock的申请。

 

到这里你可能会问:说好的MVCC呢?说好的 select
语句可以使用MVCC,不需要用到锁呢?

 

所以也许这是MySQL和Oracle的一个区别。

 

然后我们执行 kill 3 试图将 lock table cats write
这个session杀掉,期望他是否metadata lock:

 

 

mysql> kill 3

-> ;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show processlist;

+—-+——+———–+———+———+——+———————————+——————–+

| Id | User | Host  | db  | Command | Time | State   | Info   |

+—-+——+———–+———+———+——+———————————+——————–+

|  1 | root | localhost | ngx_lua | Query   | 3605 | Waiting for table
metadata lock | select * from cats |

|  2 | root | localhost | ngx_lua | Query   |0 | init| show processlist
  |

+—-+——+———–+———+———+——+———————————+——————–+

2 rows in set (0.00 sec)

 

mysql> unlock table cats;

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ‘cats’ at line 1

mysql> unlock tables;

Query OK, 0 rows affected (0.01 sec)

 

mysql> show processlist;

+—-+——+———–+———+———+——+——-+——————+

| Id | User | Host  | db  | Command | Time | State | Info |

+—-+——+———–+———+———+——+——-+——————+

|  1 | root | localhost | ngx_lua | Sleep   | 3757 |   | NULL |

|  2 | root | localhost | ngx_lua | Query   |0 | init  | show
processlist |

+—-+——+———–+———+———+——+——-+——————+

2 rows in set (0.01 sec)

 

mysql>

 

然而看到,kill 3 并没有使 metadata lock 得到释放。使用了 unlock tables;
语句才释放了 metadata lock。最后那个select 语句也得以执行完成。

 

注意:lock table cats read; 语句并不会一致持有 metadata lock
而阻塞其它语句。

 

  1. 实验二

 

首先在A终端中修改 autocommit 参数:

 

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show session variables like ‘autocommit’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| autocommit| OFF   |

+—————+——-+

1 row in set (0.00 sec)

 

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from cats;

+—-+——+

| id | name |

+—-+——+

|  3 | NULL |

|  2 |  |

|  1 | Andy |

+—-+——+

3 rows in set (0.01 sec)

 

首先修改 session 的 autocommit 参数为 off,
然后开始一个事务。注意该事务一直没有提交。

 

然后在终端B中执行一条DDL语句:

图片 2

发现被阻塞了。而阻塞它的就是那个没有提交的事务,因为他一致持有 metadata
lock,所以导致DDL语句被阻塞。

 

 

mysql> show processlist;

+—-+——+———–+———+———+——+———————————+———————————-+

| Id | User | Host  | db  | Command | Time | State   | Info |

+—-+——+———–+———+———+——+———————————+———————————-+

|  1 | root | localhost | ngx_lua | Query   |0 | init| show processlist
|

|  2 | root | localhost | ngx_lua | Query   |  177 | Waiting for table
metadata lock | alter table cats drop index name |

|  4 | root | localhost | NULL| Sleep   |  322 | | NULL |

+—-+——+———–+———+———+——+———————————+———————————-+

3 rows in set (0.00 sec)

 

手动 commit 之后,DDL的阻塞结束,顺利执行完成。

 

mysql> alter table cats drop index name;

Query OK, 0 rows affected (4 min 30.81 sec)

Records: 0  Duplicates: 0  Warnings: 0

可以看到阻塞了 (4 min 30.81 sec)

 

上面两个实验说明:DDL 语句以及lock table xxx write 和 事务 对 metadata
lock 存在互斥争用。

 

  1. 实验三

 

在终端A(autocommit=off)中执行:

 

mysql> show variables like ‘autocommit’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| autocommit| OFF   |

+—————+——-+

1 row in set (0.00 sec)

 

mysql> update cats set name=’Linus’ where id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

然后在终端B(autocommit=on)中执行:

 

mysql> update cats set name=’strup’ where id=3;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1  Warnings: 0

可以看到终端B并没有被阻塞。

 

即使A,B都是 autocommit=off,
并且都不提交,都存在事务中,也不会相互阻塞。这说明普通的update,select,delete并不会在metadata
lock上争用,也就是多个运行中的事物可以同时持有同一个database
object上的metadata lock(因为这些非DDL语句并不会修改database
objects,它们修改的是表数据而不是表结构).

 

  1. 实验四

 

首先在A终端中,设置 autocommit=off;
然后随便执行一条update,select,delete语句:

 

mysql> show variables like ‘autocommit’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| autocommit| OFF   |

+—————+——-+

1 row in set (0.00 sec)

 

mysql> update uu_test set sex=’M’ where id=1;

 

然后在B终端中执行一条 DDL:

 

alter table uu_test add index(userId);

结果你会发现B终端中的 该条DDL会一直被阻塞,在A查看:

 

mysql> show processlist;

+—-+——+———–+——+———+——+———————————+—————————————+

| Id | User | Host  | db   | Command | Time | State   | Info  |

+—-+——+———–+——+———+——+———————————+—————————————+

|  1 | root | localhost | aazj | Query   |0 | init| show processlist  |

|  2 | root | localhost | aazj | Query   |  351 | Waiting for table
metadata lock | alter table uu_test add index(userId) |

|  4 | root | localhost | NULL | Sleep   | 2900 | | NULL  |

+—-+——+———–+——+———+——+———————————+—————————————+

3 rows in set (0.00 sec)

 

发现是被 A 中为提交事物持有的 metadata lock
所阻塞。可以看到未提交事务的危害有多大!!!!!!它会一直持有 metadata
lock.

 

  1. 实验五:

 

在终端A中执行一个时间较长的DDL:

 

mysql> alter table uu_test add index(user_homeTel);

Query OK, 0 rows affected (12.86 sec)

Records: 0  Duplicates: 0  Warnings: 0

然后在DDL执行完之前,马上在B终端中执行:

 

mysql> update uu_test set user_Sex=’M’ where userId=1;

Query OK, 0 rows affected (0.14 sec)

Rows matched: 256  Changed: 0  Warnings: 0

我们看到DDL的执行期间,并没有阻塞其它事务中的update, select, delete
等等语句。也就是说DDL语句对 metadata lock
的持有是瞬时的,并不会再其执行期间一直持有。这点一定要注意。这就是 DDL
语句和 事务还有lock table xxx
write语句的区别:DDL语句并不会再执行期间一直持有metadata
lock,而是在执行的开始瞬时持有metadata
lock,马上释放;而事务会在事务期间一直持有metadata lock;lock table xxx
write语句也会一直持有metadata lock指定unlock语句发出。

 

  1. 实验六(DDL最大的危害):

 

首先在A终端中设置autocommit=off;
然后随便执行一个select/update/delete语句,一直不提交,占用 metadata
lock:

 

mysql> select userId,user_Sex from uu_test limit 2;

+——–+———-+

| userId | user_Sex |

+——–+———-+

|  1 | M|

|  2 | F|

+——–+———-+

2 rows in set (0.09 sec)

 

然后在终端B中执行一条 DDL,很明显它会被上面的 metadata lock 阻塞:

图片 3

然后我们在C终端中对同一个表uu_test执行随便一条:select/update/delete语句,神奇的情况发生!!!!!

图片 4

可以看到C终端中的对同表uu_test一条select语句尽然被阻塞了!!!!!!

 

看下终端D中的show processlist:

图片 5

可以看到:DDL 语句 alter table uu_test add index(user_QQ) 被
未提交的事务阻塞,然后DDL语句进而阻塞了其后事务中所有的针对同表uu_test的任何语句。以为他们都要获得
metadada
lock。这应该是DDL语句的最大危害之处。同理可以推断:长事物长时间持有
metadata lock, 会阻塞其它DDL语句对metada
lock的互斥申请,然后该DDL语句阻塞其后所有的涉及到该database
objects的所有语句。这里按照我们的正常逻辑,C中的语句应该不会被阻塞才对啊?难道是为了防止DDL语句对metadata
lock的申请,发生饥饿现象。所以才阻塞了C中的语句。或者对metadata
lock的申请维持了一个FIFO的队列?

 

然后我们在A终端中执行提交:commit; 然后 B 中的DDL语句立即获得 metadata
lock,然后又马上释放;然后C中的 select 也成功获得metadata lock.
B中的DDL语句因为执行时间长,它会在C执行完之后,才执行完成。这也说明了DDL语句对metadata
lock的持有是瞬时的,并不会在执行期间一直持有(不然C也不会再B之前执行完成)。

 

  1. 总结:

 

1)metadata lock保护的是元数据,也就是database
object(表结构等元数据),而不是表中的数据;

 

2)每一个在运行中的事务涉及到的database object,都必须获得metadata
lock,然后在事务结束时进行释放(parepare语句除外);

 

3)DDL 语句以及lock table xxx write 和 事务 对 metadata lock
存在互斥争用;

 

普通的update,select,delete并不会在metadata
lock上争用,也就是多个运行中的事物可以同时持有同一个database
object上的metadata lock.

 

4)mysql终端默认是autocommit=on,千万不要将mysql工具默认修改成autocommit=off;
而JDBC连接默认是 autocommit=off的;

 

5)metadata lock
因为每一个事务都要先获得,事物结束时释放,所以MySQL中一定不要有大事务,特别是运行时间比较长的事物;

 

不然会导致对metadata
lock的长期占用。会阻塞其它事务中任何涉及到该database
object的DDL语句和lock table … write语句;

 

6)DDL 语句和 事务还有lock table xxx write语句的区别:

 

DDL 语句并不会再执行期间一直持有metadata
lock,而是只在执行的开始瞬时持有metadata lock,马上释放;

 

而事务会在事务期间一直持有metadata lock;lock table xxx
write语句也会一直持有metadata lock直到unlock语句解锁。

 

7)长的事物 和 lock table … write语句会长时间持有 metadata lock;
所以在执行DDL语句之前,要使用show processlist语句看DDL语句涉及到的table

 

 是否被某个长时间运行的事物所访问。不然DDL语句会存在一直被 metadata lock
所阻塞的危险。可怕的不是DDL,而是长事务。

 

8)mysql命令行工具中执行的 DDL 语句不会受到 autocommit=on/off
的影响,DDL 语句自动开始事务,结束时自动提交事物;

 

9)DDL语句的最大危害之处:

 

未提交事物或者长事务,它们会长时间持有 metadata lock,
会阻塞其后的DDL语句对metada lock的互斥申请,

 

然后该DDL语句对metadata lock的互斥申请,会阻塞其后所有的涉及到该database
objects的所有语句,因为它们也要申请metadata lock。

之 Metadata Locking 研究 MySQL 在 5.5
中引入了 metadata lock. 顾名思义,metadata lock
不是为了保护表中的数据的,而是保护 database objects(数据库…

MySQL版本为5.6.12。

在进行alter table操作时,有时会出现Waiting for table metadata
lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for
table metadata
lock的状态,后续对TableA的任何操作(包括读)都无法进行,也会在Opening
tables的阶段进入Waiting for table metadata
lock的队列。如果是产品环境的核心表出现了这样的锁等待队列,就会造成灾难性的后果。

造成alter table产生Waiting for table metadata
lock的原因其实很简单,一般是以下几个简单的场景:

 

场景一:

通过show
processlist可以看到TableA上有正在进行的操作(包括读),此时alter
table语句无法获取到metadata 独占锁,会进行等待。

这是最基本的一种情形,这个和mysql 5.6中的online ddl并不冲突。一般alter
table的操作过程中(见下图),在after create步骤会获取metadata
独占锁,当进行到altering
table的过程时(通常是最花时间的步骤),对该表的读写都可以正常进行,这就是online
ddl的表现,并不会像之前在整个alter
table过程中阻塞写入。(当然,也并不是所有类型的alter操作都能online的,具体可以参见官方手册:)

图片 6
 

场景二:

通过show
processlist看不到TableA上有任何操作,但实际上存在有未提交的事务,可以在information_schema.innodb_trx中查看到。在事务没有完成之前,TableA上的锁不会释放,alter
table同样获取不到metadata的独占锁。

 

场景三:

通过show
processlist看不到TableA上有任何操作,在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对TableA进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。

官方手册上对此的说明如下:

If the server acquires metadata locks for a statement that is
syntactically valid but fails during execution, it does not release the
locks early. Lock release is still deferred to the end of the
transaction because the failed statement is written to the binary log
and the locks protect log consistency.

也就是说除了语法错误,其他错误语句获取到的锁在这个事务提交或回滚之前,仍然不会释放掉。because
the failed statement is written to the binary log and the locks protect
log consistency
但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志。

 

总之,alter
table的语句是很危险的,在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter
table的维护任务,在无人监管的时候运行,最好通过lock_wait_timeout设置好超时时间,避免长时间的metedata锁等待。

 

 

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图