sql语句大全之sql server 锁与事务拨云见日(中)

当前位置:

首页 > 数据库 > sql语句 >

sql语句大全之sql server 锁与事务拨云见日(中)

一.事务的概述

　　上一章节里，重点讲到了锁，以及锁与事务的关系。离上篇发布时间好几天了，每天利用一点空闲时间还真是要坚持。听《明朝那些事儿》中讲到"人与人最小的差距是聪明，人与人最大的差距是坚持"很经典的一句话一直记得。这篇重点围绕事务来开展。涉及的知识点包括：事务的概述，事务并发控制模型，并发产生的负面影响，事务隔离级别以及不同的表现。本章多以文字描述为主，没有多少代码量，重点是阐述不同隔离级别的不同表现，在以后的业务中，涉及到事务时，本文可以用来做个参考。

1.1 事务ACID

　事务作为一个逻辑工作单元执行一系列的操作，它包括四个属性：原子性、一致性、隔离性和持久性 (ACID) 属性, 只有这样才能成为一个事务。　　

　原子性：当一个事务被当作一个单独的工作单元时，不管事务内有什么，都是一个整体。对于其数据修改，要么全都执行，要么全都不执行。　　

一致性：事务在完成时，必须使所有的数据都保持一个逻辑一致状态。　　

　　隔离性：并发事务所做的修改必须与其他并发事务所做的修改隔离。事务能识别数据所处的状态，要么是另一并发事务修改它之前的状态，要么是并发事务修改它之后的状态。

　　持久性：一但事务完全，它的效果是永久存于系统的。该修改即使出现系统故障也将一直保持。 SQL Server 2014和更高版本启用延迟的持久事务。

1.2 事务的操作模式有几下几种：

　　自动提交事务：每条单独的语句都是一个事务。

　　显式事务：每个事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT 或 ROLLBACK 语句显式结束。

　　隐式事务：在前一个事务完成时新事务隐式启动，但每个事务仍以 COMMIT 或 ROLLBACK 语句显式完成。

　　批处理级事务：只能应用于多个活动结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显式或隐式事务变为批处理级事务。在sql server 2000 必须对每个 SqlCommand 对象使用独立的 SqlConnection 对象。但是 SQL Server 2005 启用了 MARS，可以共用一个SqlConnection 对象。

本章重点讲到显式事务的隔离级别

二. 事务并发模型

　　2.1　并发访问是指：多用户同时访问一种资源被视为并发访问资源。并发数据访问需要某些机制，以防止多个用户试图修改其他用户正在使用的资源时产生负面影响，机制就是下面讲的事务隔离级别。处于活动状态而不互相干涉的并发用户数据越多，并发性就越好。当一个正在修改数据的用户阻止了其他用户读取数据，或者当一个正在读取数据的用户阻止了其它用户修改数据时，并发性就降低了。

　　2.2　并发类型

　　　　在sqlserver里数据库系统可以采用两种方式来管理并发数据访问：乐观并发控制和悲观并发控制，在sql server 2000以前只有悲观并发。乐观并发控制是一种称为行版本控制(row versioning)的技术支持。这二种技术并发控制的区别在于：是在冲突发生前进行防止，还是在发生后采用某种方法来处理冲突。

　　悲观并发控制

　　　在悲观并发中，sql server是获取锁来阻塞对于其它用户正在使用数据的访问。用户操作的读与写之间是会互相阻塞的。

乐观并发控制

　　　　乐观并发控制默认采用行版本控制使其它用户能够看到修改操作发生以前的数据状态，旧版本数据行会保存下来。因些读取数据不会受到其它用户正对该数据进行修改操作的影响，换言之修改数据不会受到其它用户正对该数据进行读取影响。因为读取用户访问的数据行是一个被保存过的版本。用户读与写之间不会互相阻塞，但写与写还是会发生阻塞。

　　2.3 事务并发带来的负面影响

　　　修改数据的用户会影响同时读取或修改相同数据的其他用户。即这些用户可以并发访问数据。如果数据存储系统没有并发控制，则用户可能会看到以下负面影响：

并发影响	定义
丢失更新	当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其他事务的存在。最后的更新将覆盖由其他事务所做的更新，这将导致数据丢失。
脏读	当一个用户修改了数据但尚未提交修改，而另一个正在读取的用户会读到这个修改从而导致不一致的状态发生。
不可重复读	一个用户在同一个事务中分别以两个读操作间隔读取相同资源时可能会得到不同的值。
虚拟读取（幻影）	一个事务里执行两个相同的查询，但第二个查询返回的行集合是不同的，此时就会发生虚拟读取。这种情况发生在where 查询中，比如 where count(1)<10。同一个事务中多次使用相同的条件查询，select操作返回不同数据的结果集。

三.事务隔离级别

　　在sql server 2005及以上支持五种隔离级别来控制“读”操作的行为，其中有三个是悲观并发模式，一个是乐观并发模式，剩下一个存在两种模式。下面介绍隔离级别从允许的并发负作用（例如脏读或虚拟读取）的角度进行描述。

隔离级别	定义
未提交读 READUNCOMMITTED	隔离事务的最低级别，未提交读不会发出共享锁，允许脏读，一个事务可能看见其他事务所做的尚未提交的更改。未提交读不会发出共享锁. 该项的作用与与SELECT表上加NOLOCK相同。
已提交读 READ COMMITTED	一个事务不能读取其它事务修改但未提交的数据，避免了脏读。事务内语句运行完后便会释放共享锁，而不是等到事务提交的时候。这是数据库引擎默认级别。
可重复读 REPEATABLE READ	事务内查询语句运行完后不会释放共享锁，而是等到事务提交后.其它事务不能修改，删除，但可以插入新数据。因为不是范围锁，可能发生虚拟读取。
可序列化SERIALIZABLE	隔离事务的最高级别，事务之间完全隔离。阻止其它事务删除或插入任何行。相当于SELECT上加HOLDLOCK相同， SELECT 操作使用 WHERE 子句时获取范围锁，主要为了避免虚拟读取。
已提交读快照隔离 READ COMMITTED SNAPSHOT ISOLATION level (RCSI)	当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时，已提交读隔离使用行版本控制提供语句级读取一致性。读取操作只需要 SCH-S 表级别的锁，不需要页锁或行锁。使用行版本控制为每个语句提供一个在事务上一致的数据快照，因为该数据在语句开始时就存在。
快照隔离 SNAPSHOT ISOLATION level (SI)	快照隔离级别使用行版本控制来提供事务级别的读取一致性。读取操作不获取页锁或行锁，只获取 SCH-S 表锁。读取其他事务修改的行时，读取操作将检索启动事务时存在的行的版本。当 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时，只能对数据库使用快照隔离。默认情况下，用户数据库的此选项设置为 OFF。

　　sql server主要是通过共享锁申请和释放机制的不同处理，来实现不同的事务隔离级别。不同隔离级别允许的并发副作用如下：

隔离级别	脏读	不可重复读	幻影读	并发控制模型
未提交读	是	是	是	悲观
已提交读	否	是	是	悲观
已提交读快照	否	是	是	乐观
可重复读	否	否	是	悲观
快照	否	否	否	乐观
可串行化	否	否	否	悲观

　　不同隔离级别对共享锁的不同处理方式如下：

隔离级别	是否申请共享锁	何时释放	有无范围锁
未提交读	否		无
已提交读	是	当前语句做完时	无
可重复读	是	事务提交时	无
可序列化	是	事务提交时	无

四.事务隔离不同表现

设置未提交读

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

　设置提交读

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

设置可重复读

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

　　4.1 未提交读和提交读与其它事务并发，的区别如下表格：

未提交读

提交读

其它事务

SELECT Model FROM Product

WHERE SID=10905

显示model 值为test

SELECT Model FROM Product

WHERE SID=10905

显示model 值为test

begin tran

update product set model='test1'

where SID=10905

SET TRANSACTION ISOLATION
LEVEL READ UNCOMMITTED

SET TRANSACTION ISOLATION

LEVEL READ COMMITTED

这个事务将model值改为test1.

此时修改的X锁未释放

SELECT Model FROM Product

WHERE SID=10905

显示model值为test1,但这并不正确，

因为其它事务还没有提交。没有获取共享锁

SELECT Model FROM Product

WHERE SID=10905

查询被阻塞

申请获取共享锁时失败，因为X锁未释放

阻塞消失，得到的值还是test

rollback tran

这里事务回滚了x锁释放，值还是test

　　4.2 已提交读和可重复读与其它事务并发，的区别如下表格：

已提交读	可重复读	其它事务
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED begin tran SELECT Model FROM ProductWHERE SID=10905 第一次查询显示model值为 test	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ begin tran SELECT Model FROM Product WHERE SID=10905 第一次查询显示model值为 test
		begin tran update product set model='test1' where SID=10905 将model值改为 test1 另一事务是已提交读时，这里事务修改成功提交读共享锁查询后就释放。另一事务是可重复读时，这里事务修改阻塞可重复读共享锁一直保留到事务提交。
SELECT Model FROM Product WHERE SID=10905 第二次查询值显示为 test1	SELECT Model FROM Product WHERE SID=10905 第二次查询显示值显示为 test
commit tran 这里就是一个事务里多次读取同一值结果可能不一致	commit tran

　　未完...sql server 锁与事务拨云见日(下)

栏目列表