澳门游艺场9159-9159金沙游戏场

但这些在线操作并非真正的在线操作,关于LCK锁

作者: 澳门游艺场  发布:2019-09-26

 一.  概述

  这次介绍实例级别资源等待LCK类型锁的等待时间,关于LCK锁的介绍可参考 “sql server 锁与事务拨云见日”。下面还是使用sys.dm_os_wait_stats 来查看,并找出耗时最高的LOK锁。

select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'LCK%' 
order by  wait_time_ms desc

 查出如下图所示:

9159金沙游戏场 1

   1.  分析介绍

   重点介绍几个耗时最高的锁含义:

    LCK_M_IX: 正在等待获取意向排它锁。在增删改查中都会有涉及到意向排它锁。
  LCK_M_U: 正在等待获取更新锁。 在修改删除都会有涉及到更新锁。
  LCK_M_S:正在等待获取共享锁。 主要是查询,修改删除也都会有涉及到共享锁。
  LCK_M_X:正在等待获取排它锁。在增删改中都会有涉及到排它锁。
  LCK_M_9159金沙游戏场 ,SCH_S:正在等待获取架构共享锁。防止其它用户修改如表结构。
  LCK_M_SCH_M:正在等待获取架构修改锁 如添加列或删除列 这个时候使用的架构修改锁。

      下面表格是统计分析

锁类型 锁等待次数 锁等待总时间(秒) 平均每次等待时间(毫秒) 最大等待时间
LCK_M_IX 26456 5846.871 221 47623
LCK_M_U 34725 425.081 12 6311
LCK_M_S 613 239.899 391 4938
LCK_M_X 4832 77.878 16 4684
LCK_M_SCH_S 397 77.832 196 6074
LCK_M_SCH_M 113 35.783 316 2268

  注意: wait_time_ms 时间里,该时间表包括了signal_wait_time_ms信号等待时间,也就是说wait_time_ms不仅包括了申请锁需要的等待时间,还包括了线程Runnable 的信号等待。通过这个结论也能得出max_wait_time_ms 最大等待时间不仅仅只是锁申请需要的等待时间。

 

2. 重现锁等待时间

--  重置
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);  

 9159金沙游戏场 2

--  会话1 更新SID=92525000, 未提交
begin tran 
update [dbo].[PUB_StockTestbak] set model='mmtest' where sid=92525000

-- 会话2 查询该ID, 由于会话1更新未提交 占用x锁,这里查询将阻塞
select * from [PUB_StockTestbak] where sid=92525000

   手动取消会话2的查询,占用时间是61秒,如下图:

9159金沙游戏场 3

  再来统计资源等待LCK,如下图 :

9159金沙游戏场 4

  总结:可以看出资源等待LCK的统计信息还是非常正确的。所以找出性能消耗最高的锁类型,去优化是很有必要。比较有针对性的解决阻塞问题。

3. 造成等待的现象和原因

现象:

  (1)  用户并发越问越多,性能越来越差。应用程序运行很慢。

  (2)  客户端经常收到错误 error 1222 已超过了锁请求超时时段。

  (3)  客户端经常收到错误 error 1205 死锁。

  (4)  某些特定的sql 不能及时返回应用端。

原因:

  (1) 用户并发访问越多,阻塞就会越来越多。

  (2) 没有合理使用索引,锁申请的数量多。

  (3) 共享锁没有使用nolock, 查询带来阻塞。 好处是必免脏读。

  (4) 处理的数据过大。比如:一次更新上千条,且并发多。

  (5) 没有选择合适的事务隔离级别,复杂的事务处理等。

4.  优化锁的等待时间

   在优化锁等待优化方面,有很多切入点 像前几篇中有介绍 CPU和I/O的耗时排查和处理方案。 我们也可以自己写sql来监听锁等待的sql 语句。能够知道哪个库,哪个表,哪条语句发生了阻塞等待,是谁阻塞了它,阻塞的时间。

  从上面的平均每次等待时间(毫秒),最大等待时间 作为参考可以设置一个阀值。 通过sys.sysprocesses 提供的信息来统计, 关于sys.sysprocesses使用可参考"sql server 性能调优 从用户会话状态分析"。 通过该视图 监听一段时间内的阻塞信息。可以设置每10秒跑一次监听语句,把阻塞与被阻塞存储下来。

   思想如下:

-- 例如 找出被阻塞会话ID 如时间上是2秒 以及谁阻塞了它的会话ID
SELECT spid,blocked #monitorlock FROM sys.sysprocesses 
where blocked>0 and    waittime>2000 

-- 通过while或游标来一行行获取临时表的 会话ID,阻塞ID,通过exec动态执行来获取sql语句文本 进行存储
exec('DBCC INPUTBUFFER('+@spid+')') 

exec('DBCC INPUTBUFFER('+@blocked+')') 

 

在今天的文章里,我想谈下在线索引重建操作( Online Index Rebuild operations),它们在SQL Server 2014里有怎样的提升。我们都知道,自SQL Server 2005开始引入了在线索引重建操作。但这些在线操作并非真正的在线操作,因为在操作开始时,SQL Server需要获得共享表锁(Shared Table Lock (S) ),在操作结束时需要在对应表上获得架构修改锁(Schema Modification Lock (Sch-M) )。因此这些操作是真正的在线操作,只是营销技巧(marketing trick)。但是,亲,“在线”肯定比“部分在线”好听多了。

with(nolock)的功能:

尽管如此,SQL Server 2014还是在在线索引重建的开始和结束发生的阻塞做了一些改进。因此,在你执行在线索引重建时,你可以定义所谓的锁优先级(Lock Priority)。来看看下面的代码,你会看到起作用的新语法: 

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

 1 ALTER INDEX idx_Col1 ON Foo REBUILD
 2 WITH
 3 (
 4    ONLINE = ON
 5    (
 6       WAIT_AT_LOW_PRIORITY 
 7       (
 8          MAX_DURATION = 1, 
 9          ABORT_AFTER_WAIT = SELF
10       )
11    )
12 ) 
13 GO

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

当阻塞情况发生时,你可以用WAIT_AT_LOW_PRIORITY关键字定义如何处理。使用第1个属性MAX_DURATION指定你想要等待的时间——这里是分钟,不是秒!用ABORT_AFTER_WAIT属性你指定哪个会话需要被SQL Server回滚。SELF意味着那个ALTER INDEX REBUILD语句会回滚,当你指定BLOCKERS时,阻塞的会话会回滚。当然,当没有阻塞发生时,在线索引重建操作会立即执行。因此这里你只能配置当阻塞情况发生时要怎么处理。

3: 不能为通过插入、更新或删除操作修改过的表指定 READUNCOMMITTED 和 NOLOCK。SQL Server 查询优化器忽略 FROM 子句中应用于 UPDATE 或 DELETE 语句的目标表的 READUNCOMMITTED 和 NOLOCK 提示。

好了,我们来实操下。我们新建一个数据库,一个简单的表和一个聚集索引。 

这个东西是有利有弊,

 1 -- Creates a new database
 2 CREATE DATABASE Test
 3 GO
 4 
 5 -- Use the database
 6 USE Test
 7 GO
 8 
 9 -- Create a simple table
10 CREATE TABLE Foo
11 (
12     Col1 INT IDENTITY(1, 1) NOT NULL,
13     Col2 INT NOT NULL,
14     Col3 INT NOT NULL
15 )
16 GO
17 
18 -- Create a unique Clustered Index on the table
19 CREATE UNIQUE CLUSTERED INDEX idx_Col1 ON Foo(Col1)
20 GO
21 
22 -- Insert a few test records
23 INSERT INTO Foo VALUES (1, 1), (2, 2), (3, 3)
24 GO

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

 为了触发阻塞,我在不同的会话开始一个新的事务,但不提交:

举个例子:模拟事务正在进行
打开回话一:执行

1 BEGIN TRANSACTION
2 
3 UPDATE Foo SET Col2 = 2
4 WHERE Col1 = 1

SELECT @@spid查看会话ID --查询当前会话

这意味着我们在需要修改的记录上获得排它锁(Exclusive Lock (X)),在对应的页上获得意向排它锁(Intent-Exclusive Lock (IX)),在表本身获得意向排它锁(Intent-Exclusive Lock (IX))。我们刚刚在SQL Server里创建了典型的锁定层次(locking hierarchy):表=>页=>记录。在表级别的意向排它锁(IX Lock)和在线索引重建操作需要的共享锁(Shared Lock)是不兼容的——典型的锁/阻塞情形发生了。当你现在执行在线索引重建操作时,会发生阻塞:

BEGIN TRAN

 

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

1 ALTER INDEX idx_Col1 ON Foo REBUILD
2 WITH
3 (
4    ONLINE = ON
5 )
6 GO

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

本文由澳门游艺场9159发布于澳门游艺场,转载请注明出处:但这些在线操作并非真正的在线操作,关于LCK锁

关键词: