图片 1

新解决SQL Server并发的方法

  在锁与事务系列里已经写完了上篇中篇,这次写完下篇。这个系列俺自认为是有条不紊的进行,但感觉锁与事务还是有多很细节没有讲到,温故而知新可以为师矣,也算是一次自我提高总结吧,也谢谢大伙的支持。在上一篇的末尾写了事务隔离级别的不同表现,还没写完,只写到了重复读的不同隔离表现,这篇继续写完序列化,快照的不同隔离表现,事务隔离级别的总结。最后讲下事务的死锁,事务的分布式,事务的并发检查。

  您可以在优化数据库查询,升级数据库服务器,但仍不足以保证应用程序良好的数据库性能.这是因为问题可能产生于系统中并发(指同步)地执行查询操作.实际上,在产品化的数据库系统中,同步执行查询可能引发的问题是再多的查询优化也不能解决的.我们可以有最优化的、执行最快的查询,但是,如果这些查询因为并发问题得不到需要的数据,说明优化工作还做得不够充分。

一. 事务隔离不同表现

设置序列化

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

设置行版本控制已提交读

ALTER DATABASE  Test  SET  READ_COMMITTED_SNAPSHOT on; 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

设置快照隔离

ALTER DATABASE Test
SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

1.1 已重复读和序列化与其它事务并发,的区别如下表格: 

可重复读

序列化 其它事务

SET TRANSACTION ISOLATION

LEVEL REPEATABLE READ

SET TRANSACTION ISOLATION

LEVEL SERIALIZABLE

 

begin tran

select count(*) from product

where memberID=9708

这里显示500条数据,事务还没有结束 

begin tran

select count(*) from product

where memberID=9708

这里显示500条数据,事务还没有结束 

 
   

begin tran

insert into product

values(‘test2’,9708)

其它事务里,想增加一条数据。

如果并发的事务是可重复读,

这条数据可以插入成功。

如果并发的事务是序列化,

这条数据插入是阻塞的。

select count(*) from product

where memberID=9708

在事务里再次查询时,发现显示501条数据

 select count(*) from product

where memberID=9708

在事务再次查询时,还是显示500条数据

 

 commit tran

在一个事务里,对批数据多次读取,符合条件

的行数会不一样。

 commit tran

事务结束

 如果并发是可序列化并且commit,

其它事务新增阻塞消失,插入开始执行。

1.2
已提交读、行版本控制已提交读、快照隔离,与其它事务并发,的区别如下表格: 

已提交读

行版本控制已提交读 快照隔离 其它事务

SET TRANSACTION ISOLATION

LEVEL READ COMMITTED 

ALTER DATABASE Test SET
READ_COMMITTED_SNAPSHOT
ON;

SET TRANSACTION ISOLATION
LEVEL READ COMMITTED

ALTER DATABASE TEST SET
ALLOW_SNAPSHOT_ISOLATION
ON;

SET TRANSACTION ISOLATION
LEVEL SNAPSHOT

 

begin tran

select model from product
where sid=9708

得到值为test

begin tran

select model from product
where sid=9708

得到值为test

begin tran

select model from product
where sid=9708

得到值为test

 
     

begin tran
update product set
model=’test1′
where sid=1

select model from product
where sid=9708

事务里再次查询 阻塞

select model from product
where sid=9708

事务里再次查询值为test, 读到行版本

select model from product
where sid=9708
事务里再次查询值为test,读到行版本

 
 阻塞解除,再次查询返回 test1

再次查询 test1
其它事务提交后,这里读到的是新
(修改后的)数据

再次查询 test

其它事务提交后,这里读取还是旧数据
(行版本数据)

 commit tran
 事务里updaate修改 修改成功  事务里updaate修改 修改成功  事务里updaate修改, 修改失败报错

 

  从根本上讲,我们必须保证数据库查询和执行查询的连接必须能够在并发执行中没有错误(如死锁或更新冲突),并能在可接受的等待时间内得到结果.如果不能做到,就要定位并发问题并找出原因:为什么独立运行查询时正确,并发运行时就出问题.

二. 事务总结

   2.1   事务不同隔离级别的优缺点,以及使用场景 如下表格:

隔离级别         

优点

缺点 使用场景
未提交读                      读数据的时候,不申请共享锁,所以不会被阻塞 读到的数据,可能会脏读,不一致。 如做年度,月度统计报表,数据不一定要非常精确
已提交读       比较折中,而且是推荐的默认设置 有可能会阻塞,在一个事务里,多次读取相同的数据行,得到的结果可能不同。 一般业务都是使用此场景
可重复读 在一个事务里,多次读取相同的数据行,得到的结果可保证一致、 更严重的阻塞,在一个事务里,读取符合某查询的行数,会有变化(这是因为事务里允许新增)  如当我们在事务里需要,多次统计查询范围条件行数, 做精确逻辑运算时,需要考虑逻辑是否会前后不一致.
可序列化 最严重格的数据保护,读取符合某查询的行数,不会有变化(不允许新增)。 其它事务的增,删,改,查 范围内都会阻塞  如当我们在写事务时,不用考虑新增数据带来的逻辑错误。
行版本控制已提交读

阻塞大大减少(读与读不阻塞,读与写不阻塞)

阻塞减少,能读到新数据
大多情况下行版本控制的已提交读比快照隔离更受欢迎:
1、RCSI比SI占用更少的tempdb空间 。
2、RCSI支持分布式事务,而SI不支持 。
3、RCSI不会产生更新冲突 。
4、RCSI无需再应用程序端作任何修改。唯一要更改的只是一个数据库选项。

写与写还是会阻塞,行版本是存放在tempdb里,数据修改的越多,需要

存储的信息越多,维护行版本就

需要越多的的开销

如果默认方式阻塞比较严重,推荐用行版本控制已提交读,改善性能
快照隔离

阻塞大大减少(读与读不阻塞,读与写不阻塞)

阻塞减少,有可能读到旧数据
1、不太可能由于更新冲突而导致事务必须回滚得情况
2、需要基于运行时间长、能保证时间点一致性的多语句来生成报表的情况

维护行版本需要额外开销,且可能读到旧的数据 允许读取稍微比较旧版本信息的情况下

  2.2 锁的隔离级别(补充)

    了解了事务的隔离级别,锁也是有隔离级别的,只是它针对是单独的sql查询。下面包括显示如下

     select  COUNT(1) from dbo.product(HOLDLOCK)

HOLDLOCK

在该表上保持共享锁,直到整个事务结束,而不是在语句执行完立即释放所添加的锁。

与SERIALIZABLE一样

NOLOCK

不添加共享锁和排它锁,仅应用于SELECT语句

与READ UNCOMMITTED一样

PAGLOCK

指定添加页锁(否则通常可能添加表锁)。 

READPAST

跳过已经加锁的数据行, 仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作

ROWLOCK

使用行级锁,而不使用粒度更粗的页级锁和表级锁

建议中用在UPDATE和DELETE语句中。

TABLOCKX

表上使用排它锁, 这个锁可以阻止其他事务读或更新这个表的数据

UPDLOCK

指定在读表中数据时设置更新锁(update lock)而不是设置共享锁,作用是允许用户先读取数据(而且不阻塞其他用户读数据),并且保证在后来再更新数据时,这一段时间内这些数据没有被其他用户修改

  解决并发问题的过程是一个基于经验的过程.模仿产品环境并不容易,从许多同步连接中预测查询操作并发时的行为也很难.这就是为什么必须经常保留系统状态,才能在并发问题发生时诊断问题的原因.

五.分布式事务

      分布式事务是跨越两个或多个称为资源管理器的服务器。
称为事务管理器的服务器组件必须在资源管理器之间协调事务管理。在 .NET
Framework 中,分布式事务通过 System.Transactions 命名空间中的 API
进行管理。 如果涉及多个永久资源管理器,System.Transactions API
会将分布式事务处理委托给事务监视器,例如 Microsoft 分布式事务协调程序
(MS DTC),在Windows服务里该服务叫Distributed Transaction Coordinator
默认未启动。

  在sql server里 分布式是通过BEGIN DISTRIBUTED TRANSACTION
的T-SQL来实现,是分布式事务处理协调器 (MS DTC) 管理的 Microsoft 分布式事务的起点。执行 BEGIN
DISTRIBUTED TRANSACTION 语句的 SQL Server
数据库引擎的实例是事务创建者。并控制事务的完成。 当为会话发出后续 COMMIT TRANSACTION 或 ROLLBACK
TRANSACTION 语句时,控制事务实例请求 MS DTC
在所涉及的所有实例间管理分布式事务的完成(事务级别的快照隔离不支持分布式事务)。

在执行T-sql里
查询多个数据库主要是通过引用链接服务器的分布式查询,下面添加了RemoteServer链接服务器

USE AdventureWorks2012;  
GO  
BEGIN DISTRIBUTED TRANSACTION;  
-- Delete candidate from local instance.  
DELETE AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
-- Delete candidate from remote instance.  
DELETE RemoteServer.AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT TRANSACTION;  
GO  

  大多数情况下并发问题是一个SQL
Server进程(或任务)等待某种资源,而这种资源还没有被其他进程释放.如果两个进程串行,就不会有冲突;当它们并行时,对某种资源的冲突就产生了.这些等待大多由对用户资源的锁请求造成的.

六.事务死锁

   6.1
在关系型数据库里都有死锁的概念,在并发访问量高时,事务里或者T-sql大批量操作(特别是修改删除结果集),都有可能导致死锁。死锁是由两个互相阻塞的线程组成也称为抱死。sql
server死锁监视器进程会定期检查死锁,默认间隔为5秒,会自动判断将回滚开销影响最少的事务作为死锁牺牲者,并收到1025
错误,消息模板来自master.dbo.sysmessages表的where
error=1205。当发生死锁时要了解两方进程的sessionid各是多少,
各会话的查询语句,冲突资源是什么。请查看死锁的分析排查

   会产生死锁的资源主要是:锁
(就是上篇讲的数据行,页,表等资源),其它的死锁包括如:1.
工作者线程调度程序或CLR同步对象。2.两个线程需要更多内存,但获得授权前一个必须等待另一个。3.同一个查询的并行线程。4.多动态结果集(MARS)资源线程内部冲突。这四种很少出现死锁,重点只要关注锁资源带来的死锁。

    6.2 下面事务锁资源产生死锁的原理:

     1. 事务T1和事务T2 分别占用共享锁RID第1行和共享锁RID第2行。

     2. 事务T1更新RID2试图获取X阻塞,事务T2更新RID2试图获取X阻塞。

     3.  事务各自占有共享锁未释放,而要申请对方X锁会排斥一切锁

图片 1

 6.3 死锁与阻塞的区别

  阻塞是指:当一个事务请求一个资源尝试获取锁时,被其它事务锁定,请求的事务会一直等待,直到其它事务把该锁释放,这就发生了阻塞,默认情况sqlserver会一直等下去。所以阻塞往往能持续很长时间,这对程序的并发性能影响很大。

  死锁是两个或多个进程之间的相互等待,一般在5秒就会检测出来,消除死锁。并发性能不像阻塞那么严重。

  阻塞是单向的,互相阻塞就变成了死锁。

 6.3 尽量避免死锁的方法

  按同一顺序访问对象

  避免事务中的用户交互

  保持事务简短

  合理使用隔离级别

  调整语句的执行计划,减少锁的申请数目。  

  在本章中,我们将会学习到解决并发问题的技术.概括地讲,就是诊断和解决以下情况:锁、阻塞和死锁.基本的方法简单明了:

七.事务并发检查

  在检查并发方面,有很多种方式像原来的如sp_who,sp_who2等系统存储过程,perfmon计数器,sql
Trace/profiler工具等,检测和分析并发问题,还包括sql server
2005以及以上的:

   DMV  特别是sys.dm_os_wait_stats和sys.dm_os_waiting_tasks
,这里简单讲下并发检查

        例如:查询用户会话的相关信息

     SELECT  blocking_session_id FROM sys.dm_os_waiting_tasks
WHERE session_id>50

    blocking_session_id 阻塞会话值有时为负数: 

    -2 :被阻塞资源属于孤立分布式事务。

    -3: 被阻塞资源属于递延恢复事务。

    -4: 对于锁存器等待,内锁存器状态转换阻止了session的识别。

  例如:下面查询阻塞超5秒的等待

      SELECT blocking_session_id FROM sys.dm_os_waiting_tasks
WHERE wait_duration_ms>5000

  例如:只关注锁的阻塞,可以查看sys.dm_tran_locks
    SELECT * FROM sys.dm_tran_locks WHERE request_status=’wait’

        通过sys.dm_exec_requests查看用户请求

        通过sqlDiag.exe收集运行系统的信息

        通过errorlog里打开跟踪标识1222 来分析死锁

        通过sys.sysprocess 检测阻塞。

       

  ● 识别并发问题;

  ● 分析和找出原因;

  ● 修改并解决问题.

  使用这种方法需要我们熟悉相当多的背景资料.本章假设读者已经阅读或已经熟悉Kalen
Delaney写的Inside SQL Server 2005:The Storage
Engine第8章“锁和并发”中的内容(见本书参考书目中的引用:其他资源和引用).确切地说,那个章节包含必需的资料,关于SQL
Server 2005中事务、隔离级别和锁等解决并发问题需要的内容.

  1、解决并发问题的新工具

  SQL Server
2005增加了一些重要的解决并发问题的新工具.除了新增的工具,它仍然包含原来的一些工具,如sp_who2等系统存储过程、Perfmon计数器,解决并发问题的SQL
Trace/Profiler工具等.

  为了检测和分析并发问题,SQL Server 2005包括了以下新工具:

  DMV,特别是sys.dm_os_wait_stats和sys.dm_os_waiting_tasks.

  加强的SQLDiag.exe功能,收集运行系统的信息.

  基于行版本的隔离选项(“快照”和“已提交读快照”隔离级别).

  新的1222跟踪标识和SQL Trace检测分析死锁的“死锁图表”事件类.

  SQL
Trace/Profiler中的被阻进程报告事件类,和检测长阻塞的“被阻塞进程阈值”服务器配置选项.

  新计数器 “SQLServer:Transactions
Perfmon”对象,用来检测“快照隔离”事务中的更新冲突,计算在tempdb中版本存储的使用量.

  目前,新工具中有两个最基本的工具值得注意:用于检测的sys.dm_os_waiting_tasks
DMV和为了决议的基于行版本的隔离级别.在这里我们只概括地进行介绍,讲到解决问题的特定技术时再介绍更多的细节.

  (1)新阻塞检测工具:sys.dm_os_waiting_tasks

  sys.dm_os_waiting_tasks是SQL Server
2005检测并发问题阻塞类型最重要的新工具.这个DMV显示所有任务的等待信息.并不是所有等待任务都是阻塞的,因为任务可能等待I/O或内存授权.当一个任务等待另一个任务相对长时间时,它就会被阻塞.当一个任务A被另一个任务B阻塞,阻塞任务B将在视图中显示.

  这个视图的优点之一是它列出了任务和进程.任务表现出比SPID(服务器进程ID)更详细的SQL
Server执行细节.如果进程是并行的,一个给定的SPID可能包含一些同步任务;如果spid不是并行的,任务就只有一个spid.

  导致任务等待的原因有很多,许多和并发没有任何关系.所以从视图中过滤出不相干的行很重要,从而可以更好地关注由于资源锁定引起的阻塞问题.

  (2)新阻塞决议工具:基于行版本控制的隔离级别

  另一个主要的新工具由使用基于行版本的“快照隔离”选项组成,它减少了锁的数量,从而减少阻塞和死锁.确切地说,行版本可以减少或去除事务内外部的共享锁.因此,由于发行共享锁而可能被阻塞的查询和事务就不再被阻塞了.和使用NOLOCK提示不同,这些查询只会读取已提交的数据.

  使用基于行版本的隔离级别在某些方面有一定作用,包括tempdb中由于行版本增长的活动、使用某个或全部“快照隔离”选项时触发器需要的代码改动,以及使用SNAPSHOT隔离级别时DDL选项与可能丢失的更新之间的冲突.

  2、并发问题的类型

  当一个SQL Server进程或任务在等待时,它通常在等待某种被另一个SQL
Server锁定的资源.最可能遇到的并发问题和锁有关,包括下面的内容:

  ● 锁定

  ● 阻塞

  ● 死锁

  ● tempdb使用

  ● 更新冲突

  前3种与以下内容相关:锁定是SQL
Server基本的并发机制,阻塞发生于一个任务必须等待被另一个任务锁定的资源时,死锁发生于任务之间互相阻塞时.

  列表中后面两种产生于使用行版本.一般来说,我们可能看到增长的tempdb活动与行版本使用有关,可能还有一些性能问题与之相关.(我们可能也需要定位其他与tempdb相关的并发问题,例如与高频率创建临时表格相关的高tempdb活动性.)此外,当使用写数据库事务的“快照”隔离级别时,我们可能发现更新冲突的案例。

http://www.bkjia.com/Mysql/434486.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/434486.htmlTechArticle您可以在优化数据库查询,升级数据库服务器,但仍不足以保证应用程序良好的数据库性能.这是因为问题可能产生于系统中并发(指同步)地执