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

原创 2013年12月09日 10:17:50

时常,来自不同连接的线程会对同一张表进行读/更新操作,这种并发操作会导致阻塞,同时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,基本互不影响。

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

 

为什么索引能快速查找数据行及索引也涉及锁的机制

1、索引之所以能快速查找数据,就是因为比如B树索引就是利用二叉树(这里确切的说是B树)[这种数据结构及在此基础上的算法]能进行快速高效查找的特点。故而Oracle设计出了索引这种数据对象。 2、当...
  • haiross
  • haiross
  • 2013年10月11日 10:11
  • 1192

如何应对并发-关于数据索引

从整体架构来说分很多部分,比如常见的,存储层的i/o优化,网络层负载均衡,通讯层的连接池等等,不过我这里不讲这些。不讲这些的原因第一呢,是这些我基本都不太会;第二呢,是在实践过程中发现,特别是创业公司...

建唯一索引保证并发时,多线程插入数据的业务上的一致性

在项目中遇到并发导入excel数据到同一张表, 最初没有设置unique索引,导致在多线程并发导入excel数据的时候,业务上要求供应商代码不重复的字段,出现了重复,在这张数据表维护的时候,只有逻辑...

多线程引起多事务导致索引争用引起数据库死锁

背景及现象          线上生产环境在某些时候经常性的出现数据库操作死锁,导致业务人员无法进行操作。经过DBA的分析,是某一张表的insert操作和delete操作发生了死锁。简单介绍下数据库...

MySQL之事务、索引、锁

Mysql之事务、锁、索引 事务 锁 索引 事务概念

事务的隔离级别,加锁的细节,以及两者之间的关系。 同时也说明了 索引 与 锁 之间的关系,以及死锁成因的简化的情况

背景   MySQL/InnoDB的加锁分析,一直是一个比较困难的话题。我在工作过程中,经常会有同事咨询这方面的问题。同时,微博上也经常会收到MySQL锁相关的私信,让我帮助解决一...
  • AinUser
  • AinUser
  • 2017年06月27日 12:06
  • 308

SQL课件--十、事务、索引、视图

  • 2012年03月10日 10:06
  • 1.51MB
  • 下载

高并发场景系列(一) 利用redis实现分布式事务锁,解决高并发环境下减库存

问题描述:某电商平台,首发一款新品手机,每人限购2台,预计会有10W的并发,在该情况下,如果扣减库存,保证不会超卖 方案一 利用数据库机制,通过对记录进行锁定,再进行操作  SELECT * fr...

mysql 笔记 六 存储引擎 事务 索引 存储过程

存储引擎 一部电影 mp4 wma  avi  flv  各种格式 数据也有多种管理方式,那么这些不同的管理方式 就是存储引擎的概念 Create table aaa ( ) Engine ...
  • ebw123
  • ebw123
  • 2012年10月06日 14:34
  • 1771
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:利用索引降低并发事务引起的锁
举报原因:
原因补充:

(最多只允许输入30个字)