的锁定造成阻塞时,之后再做测试

    查询分析器二:执行

2: READUNCOMMITTED 和 NOLOCK 提示仅适用于数据锁。所有查询(包括那些带有 READUNCOMMITTED 和 NOLOCK 提示的查询)都会在编译和执行过程中获取 Sch-S(架构稳定性)锁。因此,当并发事务持有表的 Sch-M(架构修改)锁时,将阻塞查询。例如,数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。所有并发查询(包括那些使用 READUNCOMMITTED 或 NOLOCK 提示运行的查询)都会在尝试获取 Sch-S 锁时被阻塞。相反,持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。有关锁行为的详细信息,请参阅锁兼容性(数据库引擎)。

--会话 B
SELECT * FROM Orders WHERE OrderID=10248

一、什么是Table Metadata Lock

在MySQL以前的版本中,存在这样一个bug:

Description:
If user1 has an active transaction on a table and then user2 drops this table, then user1 does COMMIT, then in the binlog we have something like:
DROP TABLE t;
BEGIN;
INSERT INTO t ... ;
COMMIT;
which is wrong.

MySQL官方文档链接:
http://bugs.mysql.com/bug.php?id=989

这个bug大致意思是说:当一个会话在主库执行DML操作还没提交时,另一个会话对同一个对象执行了DDL操作如drop table,而由于MySQL的binlog是基于事务提交的先后顺序进行记录的,因此在从库上应用时,就出现了先drop table,然后再向table中insert的情况,导致从库应用出错。

因此,MySQL在5.5.3版本后引入了Metadata lock,只有在事务结束后才会释放Metadata lock,因此在事务提交或回滚前,是无法进行DDL操作的。

MySQL官方文档位置:
http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

    查询分析器一:执行

基础数据表,这些表变更较少
历史数据库修改较少
业务允许出现脏读的情况
数据量超大的表,出于性能考虑,而允许脏读

发生阻塞时,透过以下命令,可看出是哪个进程 session id,阻塞了哪几个进程 session id,且期间经过了多少「毫秒 (ms)」。如下图 3 里 session id = 53 阻塞了 session id = 52 的进程。另透过 SQL Server Profiler 工具,也能看到相同的内容。

三、测试验证

为了进一步确认问题的原因并验证,进行模拟测试:

会话1:显式开启事务,执行SELECT:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----------+
| date     |
+----------+
| 20150616 |
| 20150617 |
| 20150619 |
+----------+
3 rows in set (0.00 sec)

会话2:对test表执行DDL:

mysql> alter table test add index `date`(`date`);

语句被阻塞,show processlist查看状态:

mysql> show processlist;
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
| Id    | User        | Host      | db   | Command | Time   | State                                                                       | Info                                      |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
|    16 | system user |           | NULL | Connect | 540155 | Waiting for master to send event                                            | NULL                                      |
|    17 | system user |           | NULL | Connect | 529732 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                                      |
| 51673 | root        | localhost | test | Sleep   |     55 |                                                                             | NULL                                      |
| 51681 | root        | localhost | test | Query   |      0 | init                                                                        | show processlist                          |
| 51683 | root        | localhost | test | Query   |     29 | Waiting for table metadata lock                                             | alter table test add index `date`(`date`) |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

可以看到alter table语句的状态为Waiting for table metadata lock

会话3:对test表进行查询:

mysql> select * from test;

同样被阻塞:

mysql> show processlist;
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
| Id    | User        | Host      | db   | Command | Time   | State                                                                       | Info                                      |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
|    16 | system user |           | NULL | Connect | 540305 | Waiting for master to send event                                            | NULL                                      |
|    17 | system user |           | NULL | Connect | 529882 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                                      |
| 51673 | root        | localhost | test | Sleep   |    205 |                                                                             | NULL                                      |
| 51681 | root        | localhost | test | Query   |      0 | init                                                                        | show processlist                          |
| 51683 | root        | localhost | test | Query   |    179 | Waiting for table metadata lock                                             | alter table test add index `date`(`date`) |
| 51703 | root        | localhost | test | Query   |     18 | Waiting for table metadata lock                                             | select * from test                        |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
6 rows in set (0.00 sec)

接下来我们将会话1的事务提交,效果如下:

  • 会话1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----------+
| date     |
+----------+
| 20150616 |
| 20150617 |
| 20150619 |
+----------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
  • 会话2:
mysql> alter table test add index `date`(`date`);
Query OK, 0 rows affected (3 min 49.87 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 会话3:
mysql> select * from test;
+----------+
| date     |
+----------+
| 20150616 |
| 20150617 |
| 20150619 |
+----------+
3 rows in set (1 min 8.27 sec)
  • show processlist:
mysql> show processlist;
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
| Id    | User        | Host      | db   | Command | Time   | State                                                                       | Info             |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
|    16 | system user |           | NULL | Connect | 540411 | Waiting for master to send event                                            | NULL             |
|    17 | system user |           | NULL | Connect | 529988 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 51673 | root        | localhost | test | Sleep   |     55 |                                                                             | NULL             |
| 51681 | root        | localhost | test | Query   |      0 | init                                                                        | show processlist |
| 51683 | root        | localhost | test | Sleep   |    285 |                                                                             | NULL             |
| 51703 | root        | localhost | test | Sleep   |    124 |                                                                             | NULL             |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
6 rows in set (0.00 sec)

可以看到,当会话1提交事务后,会话2和会话3的语句便可以正常执行了,由于被阻塞的原因,因此执行时间分别为 ( 3 min 49.87 sec )( 1 min 8.27 sec )

    NOLOCK能使当前会话的查询,不受其它会话的事务所阻塞。但是这样做,就读取了其它事务的“修改后未提交的”数据。

打开回话三查询阻塞情况:
SELECT wt.blocking_session_id AS BlockingSessesionId
,sp.program_name AS ProgramName
,COALESCE(sp.LOGINAME, sp.nt_username) AS HostName
,ec1.client_net_address AS ClientIpAddress
,db.name AS DatabaseName
,wt.wait_type AS WaitType
,ec1.connect_time AS BlockingStartTime
,wt.WAIT_DURATION_MS/1000 AS WaitDuration
,ec1.session_id AS BlockedSessionId
,h1.TEXT AS BlockedSQLText
,h2.TEXT AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db
ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp
ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
打开会话四:执行

SELECT * FROM sys.dm_tran_locks

四、总结

  • 对于纯SELECT操作来说,完全没有必要添加事务,MySQL的innodb是基于MVCC多版本控制,加事务没有任何意义
  • 需要使用到事务时,也要尽量缩小事务的运行时间,一个事务中不要包含太多的语句

       SELECT * FROM test_main(NOLOCK)

--ROLLBACK -- 不提交也不回滚
打开回话二:执行
SELECT * FROM TEST;

欲解决无限期等待的问题,除了前述的 SET LOCK_TIMEOUT 命令外,还有更省事的做法,如下,在会话 B 的 SQL 语句中,在表名称后面加上 WITH (NOLOCK) 关键字,表示要求  SQL Server,不必去考虑这个表的锁定状态为何,因此也可减少「死锁 (dead lock)」发生的机率。但 WITH (NOLOCK) 不适用 INSERT、UPDATE、DELETE。

二、遇到的Metadata Lock问题

前几天时候,公司的开发同事让对一张表添加字段,由于该表数据量很大,因此使用了pt-online-change-schema工具进行字段的添加,在添加的过程中发现进度非常慢,通过shop processlist发现以及积累了大量的Metadata lock:Waiting for table metadata lock

这些语句很明显是被添加字段的DDL所阻塞,但是DDL又是被谁阻塞了呢?

查询当前正在进行的事务:

mysql> select * from information_schema.innodb_trxG
*************************** 1. row ***************************
                    trx_id: 7202
                 trx_state: RUNNING
               trx_started: 2016-07-20 23:02:53
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 52402
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 360
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

发现一个正在运行的事务,从trx_started字段可以判断出,该事务已经运行了很久,一直没有结束,看来就是这个事务阻塞了添加字段的DDL语句。

根据查询到的trx_started时间以及trx_mysql_thread_id到MySQL的general log中查找,当然前提是开启了general log的功能,在general日志中对应的时间发现该thread执行了语句:

set autocommit=0;

关闭了自动提交,再往下看,oh my god......下面居然是一堆SELECT语句!

好了,终于找到原因,kill掉先:

mysql> kill 52402;
Query OK, 0 rows affected (0.00 sec)

之后便可以正常执行下去了。

    表用最简单的表,自己动手建一个。

SELECT * FROM TEST WITH(NOLOCK);

两种做法的效果类似,让会话 B 即使读到被锁阻塞的记录,也永远不必等待,但可能读到别人未提交的数据。虽然说这种做法让会话 B 不用请求共享锁,亦即永远不会和其他事务发生冲突,但应考虑项目开发实际的需求,若会话 B 要查询的是原物料的库存量,或银行系统的关键数据,就不适合用这种做法,而应改用第一种做法的 SET LOCK_TIMEOUT 命令,明确让数据库抛回等候逾时的错误代号 1222,再自己写代码做处理。

      则显示如下

在使用链接服务器的SQL当中,(NOLOCK)不会生效,WITH(NOLOCK)才会生效


       但最后一行并没有真正存储在数据库中,因为会话一还没有进行提交,我们用NOLOCK就查询出来了。

SELECT * FROM TEST WITH(NOLOCK)--会发现数据马上出来

  • 尽可能让事务轻薄短小、让锁定的时间尽量短,例如把不必要的命令移出事务外,或把一个大量更新的事务,切成多个更新较少的事务,以改善并发性。
  • 将组成事务的 SQL 语句,摆到一个「批 (batch) 处理」,以避免不必要的延迟。这些延迟常由 BEGIN TRAN ... COMMIT TRAN 命令之间的网络 I/O 所引起。
  • 考虑将事务的 SQL 语句写在一个存储过程内。一般来说,存储过程的执行速度会比批处理的 SQL 语句快,且存储过程可降低网络的流量和 I/O,让事务可更快完成。
  • 尽可能频繁地认可 Cursor 中的更新,因为 Cursor 的处理速度较慢,会让锁定的时间较长。
  • 若无必要,使用较宽松的事务隔离级别,如前述的 WITH (NOLOCK) 和 READ UNCOMMITTED。而非为了项目开发方便,全部使用默认的 READ COMMITTED 级别。
  • 避免在事务执行期间,还要等待用户的反馈或交互,这样可能会造成无限期的持有锁定,如同本帖一开始提到的状况,最后造成大量的阻塞和数据库 connection 被占用。
  • 避免事务 BEGIN TRAN 后查询的数据,可能在事务开始之前先被引用。
  • 避免在查询时 JOIN 过多的表 (此指非必要的 JOIN),否则除了性能较差外,也很容易读到正被锁定或阻塞中的表和字段。
  • 应注意在一个没有索引的表上,过量的「行锁」,或一些锁定使用了过多的内存和系统资源时,SQL Server 为了有效地管理这些锁定,会尝试将锁定扩展为整个表的「表锁」,此时会很容易造成其他 process 在访问时的阻塞和等待。

    大家都知道,每新建一个查询,都相当于创建一个会话,在不同的查询分析器里面进行的操作,可以影响到其他会话的查询,极端的情况可能会一直处于阻塞中,哪怕只是一个很简单的查询都“特别慢”。

1: 指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据,其他事务设置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作,但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错,向用户显示从未提交过的数据,或者导致用户两次看到记录(或根本看不到记录)。有关脏读、不可重复读和幻读的详细信息,请参阅并发影响。

类似的功能,也可如下,在 SQL 语句前,先设置「事务隔离级别」为可「脏读 (dirty read)」。

      SELECT * FROM dbo.test_main

SELECT * FROM TEST (NOLOCK);

归根究柢,我们在编程时,就应该避免写出会造成长时间阻塞的 SQL 语句,亦即应最小化锁定争用的可能性,以下为一些建议:

  以前遇到过,但仅限于听同事说加上NOLOCK好一些,今天仔细研究测试了下,终于理解了,那么加与不加到底区别在哪呢?

所以with(nolock)是有利有弊的
大体使用场景:

消息 1222,级别 16,状态 51,第 3 行
已超过了锁请求超时时段。

       SELECT * FROM dbo.test_main

使用with(nolock)时查询不受其他排它锁阻塞

--会话 B
SET LOCK_TIMEOUT 3000
SELECT * FROM Orders WHERE OrderID=10248
--SET LOCK_TIMEOUT -1

        id    value
        1     one
        2     two
        3     three
        4     four

with(nolock)的功能:

SELECT * FROM Orders WHERE OrderID=10250

        5     five

但是:假如由于某种原因,该事务回滚了, SELECT * FROM Book AS b WHERE b.BookName = 'Timmy' AND b.ID = 1 查询到的这边数据就是一条脏数据,又叫无效数据的读出,是指在数据库访问中,事务T1将某一直修改,然后事务T2读取该值,此后T1因为某种原因撤销对该值的修改,这就导致T2所读取到的数据是无效的

日前公司 server-side 有组件,疑似因撰写时 exception-handling 做得不周全,导致罕见的特殊例外发生时,让 SQL Server 的事务未执行到 cmmmit 或 rollback,造成某些表或记录被「锁定 (lock)」。后来又有大量的 request,要透过代码访问这些被锁定的记录,结果造成了严重的长时间「阻塞」,最后有大量 process (进程) 在 SQL Server 呈现「等待中 (WAIT)」的状态。

    查询分析器三:执行

UPDATE TEST SET NAME='Timmy' WHERE ID =1;


       接着执行如下:

这个东西是有利有弊,

所谓的「阻塞」,是指当一个数据库会话中的事务,正在锁定其他会话事务想要读取或修改的资源,造成这些会话发出的请求进入等待的状态。SQL Server 默认会让被阻塞的请求无限期地一直等待,直到原来的事务释放相关的锁,或直到它超时 (根据 SET LOCK_TIMEOUT,本文后续会提到)、服务器关闭、进程被杀死。一般的系统中,偶尔有短时间的阻塞是正常且合理的;但若设计不良的程序,就可能导致长时间的阻塞,这样就不必要地锁定了资源,而且阻塞了其他会话欲读取或更新的需求。遇到这种情况,可能就需要手工排除阻塞的状态,而本文接下来要介绍两种排除阻塞的做法。

本文由必威发布于必威-数据,转载请注明出处:的锁定造成阻塞时,之后再做测试

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。