利用索引降低并发事务引起的锁

时常，来自不同连接的线程会对同一张表进行读/更新操作，这种并发操作会导致阻塞，同时SQL Server会自动处理以防止脏读。然而，有种情景很常见，那就是每个连接要读/更新的行互相排斥，换句话说，就是各个连接读/更新的行没有交集。在这片文章中，将像大家展示如何恰当地使用索引来降低阻塞的发生，以便多个读/更新能够同时操作同一张表。

创建TEST表如下：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TEST](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[NAME] [nvarchar](20) NULL,
	[TEL] [varchar](20) NULL
) ON [PRIMARY]

GO

塞入两笔记录：

INSERT TEST(NAME,TEL)
SELECT N'阿三','12345678901'
UNION ALL
SELECT N'李四','23456789012'

开启两个查询，分别执行如下T-SQL：

BEGIN TRAN
	UPDATE TEST  WITH(ROWLOCK)
	SET TEL = '11111111111'
	WHERE ID=1

BEGIN TRAN
	UPDATE TEST  WITH(ROWLOCK)
	SET TEL = '22222222222'
	WHERE ID=2

这是我们可以发现第一个T-SQL执行后的状况如下：

然而，第二个T-SQL执行时就pending在那里：

通过sp_lock查看：

由上图可以看出，资源1:498424:0被spid（56）排他锁定（X），而迫使spid（52）对其更新（U）等待（WAIT），因此就是我们看到的执行第二个更新事务时，一直处于等待状态，因为排他锁（X）没有释放。 

关于锁模式的说明如下，更多信息可参考官网（http://technet.microsoft.com/zh-cn/library/ms175519.aspx）。

锁模式	说明
共享 (S)	用于不更改或不更新数据的读取操作，如 SELECT 语句。
更新 (U)	用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排他 (X)	用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。 确保不会同时对同一资源进行多重更新。
意向	用于建立锁的层次结构。 意向锁包含三种类型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。
架构	在执行依赖于表架构的操作时使用。 架构锁包含两种类型：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
大容量更新 (BU)	在向表进行大容量数据复制且指定了 TABLOCK 提示时使用。
键范围	当使用可序列化事务隔离级别时保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。

 关于锁定资源的说明如下，更多信息可参考（http://technet.microsoft.com/zh-cn/library/ms189849(v=SQL.105).aspx）

资源	说明
RID	用于锁定堆中的单个行的行标识符。
KEY	索引中用于保护可序列化事务中的键范围的行锁。
PAGE	数据库中的 8 KB 页，例如数据页或索引页。
EXTENT	一组连续的八页，例如数据页或索引页。
HoBT	堆或 B 树。 用于保护没有聚集索引的表中的 B 树（索引）或堆数据页的锁。
TABLE	包括所有数据和索引的整个表。
FILE	数据库文件。
APPLICATION	应用程序专用的资源。
METADATA	元数据锁。
ALLOCATION_UNIT	分配单元。
DATABASE	整个数据库。

 

对于这种这种并发操作，我们可以通过索引来改善锁：

 如下创建主键索引：

ALTER TABLE [dbo].[TEST] ADD CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
 (
      [ID] ASC
 )WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE  =OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  =ON,ALLOW_PAGE_LOCKS  =ON)ON [PRIMARY]
 GO

这时我们再来执行两个更新事务时，我们发现，均能执行成功，并有没有出现更新（U）等待（WAIT）的的情况。 

执行第一个：

 执行第二个：

 查看sp_lock，在Type这一栏，并没有出现RID，而出现了KEY，并且KEY的资源不一样，排他锁的状态都是GRANT，基本互不影响。

从上面的演示说明可以看出，正确的索引设置有助于降低并发事务引起的锁。