SQL中的事务与锁定.docx
《SQL中的事务与锁定.docx》由会员分享，可在线阅读，更多相关《SQL中的事务与锁定.docx（81页珍藏版）》请在冰点文库上搜索。
SQL中的事务与锁定
SQL2005中的事务与锁定（一到九整合版）
一、并发及并发控制模型
对于这个我在<>一文有所提及，你可以通过如下链接进行访问：
SQL2005数据库引擎结构（三）并有一起的意思，显然就是多个的意思啦，光书面来理解并发就是多个东西同时发生，在数据库并发就是多个进程同时取、存数据库里数据的能力。
着眼我们开发的系统，当然是激动态的并互不打架的并发用户进程越多并发能力就越强大啦，你想想看好多的网上购物系统，如果没有并发处理的能力，那么在上面登记的用户信息、商品有库存信息及用户帐户信息很难保证正确性和一致性，比如一个物品本身库存只有100个，结果如果100人同时在线进行预定，库存就有可能搞一个100－1的效果出来。
很显然对上述的例子我们希望一个进程在修改库存数据时必须阻止其它进程读或修改数据，或是正在读的用户进程限制其它活动的用户进程进行读或修改的操作，这样一来势必造成系统的并发性能下降，但是如果不采用这种办法又无法保证数据正确性和一致性。
那怎么解决这个问题呢，办法只有通过不同的并发模式来管理这些并发事件。
我们下面来理解并发控制的模式、并发下可能发生的非一致数据行为，即并发副作用，并由模式及数据行为引入事务及相关的5个隔离等级等概念，进而来理解不同隔离等级下并发实现的机理，显然我们自己也就可以回答上面这个问题了。
并发控制模式：
一般并发控制模式有两种：
积极并发（又称乐观并发）和消极并发（又称悲观并发）。
积极并发是SQL2005才引入的新模式，在2005以前的版本其实只有唯一的并发模式即：
消极并发。
那什么是消极并发呢？
消极并发就是SQLSERVER默认行为是进程以获取锁的方式来阻止其它进程对正在使用的数据进行修改的能力。
对于数据库来说对数据修改的操作进程肯定很多，这些进程肯定都会去影响其它进程读取数据的能力，反之，对数据进行读时加上锁也一定会影响其它进程修改数据的能力，简而言之，就是读取与修改数据之间是冲突的、互相阻塞的。
乐观并发是SQL2005利用一个行版本控制器的新技术来处理上述的冲突。
行版本控制器在当前进程读取数据时生成旧版本的数据，使得其它请求读的进程能看到当前进程一开始读取时的数据状态，并且不受当前进程或其它进程对数据进行修改的影响。
简而言之读与修改之间是不冲突的，但是修改与修改之间还是冲突的。
对于这两种并发模式两个进程同时请求数据修改必然会冲突的，除此以外的差别在于一个是在冲突发生前进行控制，另一个在冲突发生了进行协调处理。
这好比生活一样，两种方式就是两种不同的人生，一种消极怠工一种积极向上。
二、并发下可能发生的并发副作用：
丢失更新、脏读、不可重复读、幻影。
为了把这些可能发生的并发副作用说清楚，我们先“布置”一个场景：
这是一个卖工艺石头的小商店，平时在前场完成交易，客户凭单据到后场领取石头，AMM和BMM是营业员，她们平时掌握库存数是通过大厅里的一块LED显示牌得之，并且在各自完成一笔交易后修改LED显示，以保证数据的实时性。
在这个场景下我们来观察可能发生的行为：
1、 丢失更新：
丢失更新估计是所有数据库用户都不想发生的情况，什么是丢失更新呢？
丢失更新是当2个或两个以上的用户进程同时读取同样的数据后又企图修改原来的数据时就会发生。
好在上述场景下，大厅LED显示牌显示当前库存1000，这时同时有两个客户上门了，AMM和BMM满面春风接待，比如AMM卖出1个，BMM呢卖出了10个，AMM处理完业务后赶紧把LED显示数修改为1000 － 1 ＝ 999个，几乎同一时间BMM处理完自己的业务后习惯性的把LED显示数修改为1000 － 10 ＝ 990 个，这时老板从后场过来，看着LED有点不爽，大吼一声：
现在还有多少存货呀？
，AMM说我卖了1个，BMM说我是10个，不过两个人都傻眼了，LED显示怎么是990呢？
原来BMM在更改时把AMM做的更改搞丢了，这就是丢失更新。
显然对老板和营业员来说都是必须回避不能发生的事。
2、 脏读
很显然，在上面的例子里因为AMM和BMM事先因为不知道对方已经修改了柜台存货，所以才造成了存货数目显示错误，出了问题我们要想办法解决问题，英明的老板说了，你们随便哪个在谈一笔生意时先把客户意向购卖石头数扣掉，如果最后客户不要你再改回头，两个MM对老板的英明决定表示等赞同，可是问题还是发生了，怎么回事呢，还是假设柜台存货1000个石头，AMM有一笔生意正在谈着，顾客意向要600块石头，AMM赶紧把LED显示修改为400。
这时BMM也很兴奋因为她已经谈成一笔700块石头的生意，所以呢BMM抬头一看，好嘛，还有400块可卖，完了BMM的生意做不成了，只好向客户表达歉意。
BMM只能让老板进货，可是老板一看LED显示还有1000块怎么你的700块生意做不成了呢？
哦，因为最后AMM的600块生意没做成。
嘿嘿，也就是BMM错误的读取了AMM修改的数据，完成了一次“脏读”操作。
脏读也就是一个用户进程读取了另一个用户进程修改过但没有正式提交的数据，这时导致了数据不一样的情形发生了。
因为A用户进程是无法确认另一个B用户进程在自己提交数据前是否修改过数据，这是数据库系统默认情况下必须回避的。
3、 不可重复读
不可重复读又称不一致分析，不过，个人以为似乎不一致分析更让人好理解一点，但是大部分地方称不可重复读。
不可重复读是指一个用户进程两次读取数据得到不同样的数据。
比如那个英明的老板吧，他知道要盘点，掌握库存的变化，忙得満头大汗，终于计出库存数来，比如说1000吧，或是当他跑到大厅一看LED显示牌却只有了900，显然这一次的检查库存的过程中两次得到库存数不一样，原因就是AMM在老板从后场走到前场的过程中做了一担生意，卖出100块。
嘿嘿，老板气又不是不气又不是，这AMM真可爱，做生意挺两下呀！
显然在一个用户进程两次读取数据间隔内另一个用户进程修改了数据，这就是不可重复读。
4、 幻影
幻影，嘿嘿，我们不是经常无视BS自己的人吗？
你无视他并不代表他不BS你吧，这个BS你的人就成了幻影，嘿嘿开个玩笑。
这种情况多数在查询带谓词时结果集内部分数据变化的时候发生，如果谓词限定下在一个交易里两次同一查询的结果集不同，那些不同的行或行集就是幻影。
比方说英明的老板到大厅走走，顺便请大家吃饭，数数人数，BMM，。
。
。
。
一路数过去，发现有10人，呵呵，正好一桌，，通知好她们后老板回办会室拿人民币，回到前场看见AMM，再一数11人，晕，刚才怎么看到AMM ？
AMM也知道了老板请客没数到他，很是生气，这时AMM就成了幻影。
以上是四种并发副作用只是一个交易事务里或事务间可能发生的异常的非一致的数据行为（记好并发副作用和不一致的数据行为术语，这在以后会经常提及），其实还是有好多的行为是我们所期望的，那么我们期望的行为是什么呢，下面我们在事务里来介绍。
我们可以通过隔离级别来设定一个合适级别以决定上述上种数据行为哪些是允许的。
那什么是交易事务，什么又是隔离等级呢？
三、事务
事务是数据库一笔交易的基本单元，存于两种并发模型中。
又分为显式事务和隐式事务。
显式事务是显式的开始一个事务并显式的滚回或提交事务，除了显式的事务还有隐式的了，隐式事务是数据库自己根据情况完成的事务处理，如单独的select、update、delete、select语句。
作为一个事务，它能保证数据库的数据一致性及重做。
提到事务不得不提及事务的ACID属性：
原子性、一致性、隔离性及持久性。
不管是显式还是隐式的，都必须维持这四个属性。
原子性：
一个事务是一个整体，要不全部提交，要不全部中止。
意思就是要不全部成功提交到数据，要不全部回滚恢复事务开始前的状态。
比方我们做一个入库操作，在这个事务里，审核入库单和修改库存作为一个整体，要不单据变成审核过同时库存增加相应的值，要不就是单据未审核同时库存不变。
一致性：
一致性要求事务保证数据在逻辑上正确，处理的结果不是一个不确定的状态，什么是不确定状态呢，比如说我们完成一个库存减少的操作，如果没有一个出货单据那么这个库存的当前修改就是一个不确定状态，因为你无法知道减少的东东到哪儿去了。
隔离性：
这个隔离和锁定有关，以后在说锁的过程中会提到这些，你先记住这个就行。
持久性：
持久很显然是要求正确提交的修改必须保证长久存在，不会因为关机或掉电把这笔交易丢掉。
进行中的事务发生故障那事务就完全撤销，像没有发生一样，如果事务提交的确认已经反馈给应用程序发生故障，那么这些日志利用先写技术，在启动恢复阶段自动完成相应的动作保证事务的持久性。
（这个在前面的引擎组件有过介绍哦。
）
四、隔离等级
首先来说说隔离，隔离是一个事务必须与其他事务所进行的资源或数据更改相隔开，显然隔离等级就是相隔的程度了吧。
在说隔离级别不得不提及锁的概念，但是在本单不提及锁，在以后听章节里再作说明，大家只要有个印象就行。
在这儿我们必须明白两件事：
1，隔离级别不会影响进程获得数据修改的排它锁，并且这个锁会保存到事务结束。
相对于读进程来说，隔离级别就是对读操作的一个保护级别，保护读操作受其它事务影响的程序。
2，较低的隔离级别可以增强许多用户同时访问数据的能力，但也增加了用户可能遇到的并发副作用（例如脏读或丢失更新）的数量。
相反，较高的隔离级别减少了用户可能遇到的并发副作用，却需要太多的系统资源及一个事务阻塞其他事务的可能性。
应平衡应用程序的完整性要求与相应隔离级别的系统开销，在此基础上选择相应的隔离级别。
最高隔离级别（可序列化）保证事务在每次重复读取操作时都能准确检索到相同的数据，但需要通过执行某种级别的锁定来完成此操作，而锁定可能会影响其他用户进程。
最低隔离级别（未提交读）可以检索其他事务已经修改但未提交的数据。
在未提交读中，所有并发副作用都可能发生，但因为没有读锁定或修改阻塞读取，所以开销最少。
不同的隔离级别决定我们有哪些数据副作用可以发生，而并发模型决定不同隔离等级下如何来限制这些数据行为或如何协调这数据行为。
好，那我们来关注一下不同隔离等级下如何限制这些行为的发生。
未提交读（uncommittedRead）：
字面理解一下，修改了的未提交数据可以读取。
准确点：
一个用户进程可以读取另一个用户进程修改却未提交的数据。
SQLSERVER对这个等级下的读操作不需要获得任何锁就可以读取数据，因为不需要锁所以不会和其它任何进程互相阻塞，自然而然能读取其它进程修改了的却未提交数据。
显然这不是我们理想的一种模式，但是它却有了高并发性，因为读操作没有锁不会影响其它进程的读或写操作。
在这种级别下，除了丢失更新（上一讲中的数据可能发生的行为）外，其它行为都有可能发生，冒着数据不一致的风险来避免修改的进程阻塞读取的进程，事务的一致性肯定是得不到保障，显然这是消极并发模式下的回避阻塞频繁的一种解决方案。
未提交读那肯定是不适合于股票、金融系统的，但是在一些趋势分析的系统里，要求的只是一种走向，准确性可以不是那么严格时，这个级别因并发性能超强成为首选。
已提交读（ReadCommitted）：
它和未提交读相反，已提交读级别保证一个进程不可能读到另一个进程修改但未提交的数据。
这个级别是引擎默认的级别，也是2005乐观并发模式下支持的级别，也就是说已提交读可是乐观的也可以是悲观的，那究竟当前库是属于哪个并发模型下的已提交读呢，这取决于一个READ_COMMITED_SNAPSHOT数据库配置项，并且缺省是悲观并发控制的。
这个配置项决定已提交读级别下事务使用锁定还是行版本控制，并很显然行版本控制是乐观并发模式，锁定是悲观并发模式。
我们来点角本看看：
--设置已提交读隔离使用行版本控制
ALTERDATABASEtestcsdnSETREAD_COMMITTED_SNAPSHOTON
GO
--查看当前已提交读隔离并发模型
selectname,database_id,is_read_committed_snapshot_on fromsys.databases
/*
name database_idis_read_committed_snapshot_on
-------------------- ----------------------------------------
master 1 0
tempdb 2 0
model 3 0
msdb 4 0
ReportServer$SQL2005 5 0
ReportServer$SQL2005TempDB 6 0
TestCsdn 7 1 --current
（7行受影响）
*/
--设置已提交读隔离使用锁定
ALTERDATABASEtestcsdnSETREAD_COMMITTED_SNAPSHOTOFF
GO
--查看已提交读隔离并发模型
selectname,database_id,is_read_committed_snapshot_on fromsys.databases
/*
name database_idis_read_committed_snapshot_on
-------------------- ----------------------------------------
master 1 0
tempdb 2 0
model 3 0
msdb 4 0
ReportServer$SQL2005 5 0
ReportServer$SQL2005TempDB 6 0
TestCsdn 7 0--curret
（7行受影响）
*/
已提交读在逻辑上保证了不会读到不实际存在的数据。
悲观并发下的已提交读，当进程要修改数据时会在数据行上申请排它锁，其它进程（无论是读还是写）必须等到排它锁释放才可以使用这些数据。
如果进程仅是读取数据时会使用共享锁，其它进程虽然可以读取数据但是无法更新数据，必须等到共离锁释放（共享锁在数据处理完即释放，比如行共享锁在当前数据行数据处理完就自动释放，不会在整个事务内保留发。
）。
乐观并发的已提交读，也确保不会读到未提交的数据，不是通过锁定的方式来实现，而是通过行版本控制器生成行的提前交的数据版本，被修改的数据虽然仍然锁定，但是其它进程可以可以读取更新前版本数据。
可重复读（RepeatableRead）：
这也是一个悲观并发的级别。
可重复读比已提交读要求更严格，在已提交读的基础上增加了一个限制：
获取的共享锁保留到事务结束。
在这个限制下，进程在一个事务里两交次读取的数据一致，也就是不会读取到其它进程修改了数据。
在这儿我们提到共享锁会保留到事务结束，那得申明一下无论哪种级别及并发模型，排它锁是一定要保留到事务结束的。
在可重复读级别共享锁同样也会保留到事务结束。
那么这种对数据安全的保证是通过增加共享保留的开销为代价的，也就是只要开始一个事务，其它用户进程是不可能修改数据的，显而易见的系统的并发性和性能必然下降。
这似乎是我们想像中的一种级别，虽然这个级别暂时无法回避幻影读，而且我们也默许并发及性能下降，那只有对程序员对事务的控制有严格的要求：
事务要短并尽量不要人为因素的干扰，减少潜在的锁竞争。
快照（SnapShot）：
乐观并发级别。
这是2005新增加的一个隔离级别。
快照级别与使用乐观并发的已提交读差不多，差别在于行版控制器里的数据版本有多早，这个在以后讲锁时再说。
这个级别保证了一个事务读取的数据是事务开始时就在数据库逻辑上确认并符合一致性的数据。
读操作不会要求共享锁定，如果要求的数据已经排它，就会通过行版本控制器读取最近的符合一致性的数据。
可串行化：
是目前最严谨、最健壮的一个级别，属于悲观并发。
它防止幻影的发生，回避了以前所有意外行为的发生。
可串行化意味着系统按进程进入队列的顺序依次、序列化的执行的结果与事务同时运行得到一致的结果。
这个最健壮的级别显然共享锁也是随事务开始随事务结束，并通过锁定部分不存在的数据（即索引键范围锁定）来回避幻影的发生。
在前面的两篇里我从纯理论上把事务相关的知识作了一个梳理，有人看了一定觉得无味了吧，好这一篇我们加入一点T-SQL语句把前面所说有东东关联起来，我们人为产生锁定来理解不同的意外数据行为在不同隔离等级下的表现，顺便再重温一下意外数据行及隔离等级，让大家对交易事务有一个直观的认识。
在进行实例前不得不先介绍一点锁的知识，注意这儿只是简单的说一下，不作深入讨论。
我们根据用户访问资源的行为先归纳出几种锁，这几种锁在下面的实例里会出现，它们为：
共享锁定、排它锁定、更新锁定及用意向这个限定词限定的三种锁（意向共享、意向排它、意向更新），当当然还有其它的模式，我们在下一篇再说。
意向锁的存在是解决死锁的发生，保证进程在申请锁定前确定当前数据是否存在不兼容性的锁定。
先对上面提到的锁作一个简单的描述，更详细的下面再说。
共享锁定发生在查询记录时，直观就是我们select啦，但是并不是只有select才有共享锁定。
一个查询记录的语句必须在没有与共享锁定互斥锁定存在或等待互斥锁定结束后，才能设置共享锁定并提取数据（互斥不互斥就是锁的兼容性，这在以后再说明）。
排它锁定发生在对数据增加、删除、修改时，事务开始以后语句申请并设置排它锁定（前提是没有其它互斥锁定存在），以明确告知其它进程对当前数据不可以查询或修改，等待事务结束后其它进程才可以查询或修改。
更新锁定是一个介于共享与排它之间的中继锁定，比如我们带where条件的update语句，在查询要更新的记录时是设置共享锁定，当要更新数据时这时锁定必须由共享锁定升级成更新锁定继而升级为排它锁定，当排它锁定设置成功才可以进行数据修改操作。
显然也是要要求在锁升级的过程中没有互斥锁定的存在。
简单的理解更新锁定是一个中继闸一样，把升级成排它锁定进程“序列化”，以解决死锁。
最后重点说明一下，数据更新阶段是要对数据排它锁定不是更新锁定，不要被字面意思训导哦。
最后说一下在上述锁定模式下的互斥，共享锁定只与排它锁定互斥，更新锁定只与共享锁定不互斥。
在进行具体实例前我们一定要有一个工具来对我们实例过程进行监控，好，下面我写了一个过程，在需要时直接调用就行，过程如下：
CreateProcsp_us_lockinfo
---------------------------------------------------------------------
--Author:
HappyFlyStone
--Date :
2009-10-0315:
30:
00
--BLOG :
--申明 ：
请保留作者信息，转载注明出处
---------------------------------------------------------------------
AS
BEGIN
SELECT
DB_NAME（t1.resource_database_id）AS[数据库名],
t1.resource_typeAS[资源类型],
-- t1.request_typeAS[请求类型],
t1.request_statusAS[请求状态],
-- t1.resource_descriptionAS[资源说明],
CASEt1.request_owner_typeWHEN'TRANSACTION'THEN'事务所有'
WHEN'CURSOR'THEN'游标所有'
WHEN'SESSION'THEN'用户会话所有'
WHEN'SHARED_TRANSACTION_WORKSPACE'THEN'事务工作区的共享所有'
WHEN'EXCLUSIVE_TRANSACTION_WORKSPACE'THEN'事务工作区的独占所有'
ELSE''
ENDAS[拥有请求的实体类型],
CASEWHENT1.resource_type='OBJECT'
THENOBJECT_NAME（T1.resource_ASsociated_entity_id）
ELSE T1.resource_type+':
'+ISNULL（LTRIM（T1.resource_ASsociated_entity_id）,''）
ENDAS[锁定的对象],
t4.[name]AS[索引],
t1.request_modeAS[锁定类型],
t1.request_session_idAS[当前spid],
t2.blocking_session_idAS[锁定spid],
-- t3.snapshot_isolation_stateAS[快照隔离状态],
t3.snapshot_isolation_state_descAS[快照隔离状态描述],
t3.is_read_committed_snapshot_onAS[已提交读快照隔离]
FROM
sys.dm_tran_locksASt1
leftjoin
sys.dm_os_waiting_tasksASt2
ON
t1.lock_owner_address=t2.resource_address
leftjoin
sys.databasesASt3
ONt1.resource_database_id=t3.database_id
leftjoin
（
SELECTrsc
升级会员 