Sql Server并发和事务

2023-08-02 12:24:40

锁的作用范围通常在事务中，事务是建立在并发模式下。

从SQL Server 2005开始，加入了一种新的并发模式-----乐观并发。不管使用哪种并发模式，如果多个会话同时修改相同的数据，都会产生资源争用，然后引发一系列的问题。

1.存在的读现象:包括脏读、不可重复读和幻读。

2.丢失更新：一个会话的修改效果被另外一个会话意外覆盖

3.过量的锁定：过量的锁定会导致阻塞，导致资源压力和终端用户的响应延时

4.死锁：最少两个会话互相阻塞对方，引发死锁。SQL Server 尽可能自动侦测并干预死锁，最后给客户端返回1205错误

绝大部分基于锁的阻塞问题都可以通过优化查询、调整索引、修改隔离级别及表结构的设计来大大降低

一：悲观并发和乐观并发

默认情况下，SQLServer 都会采用悲观模式来应对并发。大量的并发操作，如果不使用悲观并发来防止多个会话对数据同时修改，会造成数据不一致。在该模式下，将会通过加锁的形式保护正在被读取的事务，以保证这些数据在读取过程中不被其他会话修改。在会话修改数据的过程中也会加锁，防止其他会话读取或者修改对应的数据。也就是在悲观模式下，读操作阻塞写操作，写操作阻塞读写操作。数据库中所有操作都会加锁。

对于乐观并发，SQL Server 会假设只有少量的冲突发生。其默认的机制就是使用快照的行版本技术，把行版本存放在TempDB数据库中

在两种并发模式下，SQL Server 都会通过使用特定的事务隔离级别来协调事务间的争用问题。不同的隔离级别会申请不同的锁，也会影响锁的持有时间，越高的隔离级别，锁持有的时间越长，越能避免资源争用和数据的不一致性，但是并发度也越低。

基于读写，写写互相阻塞的特性（读读不互相阻塞），SQL Server中普遍存在锁定及阻塞情况，随着并发量和数量的逐步增长，其潜在的问题将会凸显。锁定的持续时间及范围越来越大，数据库的总体性能也会越来越低。

悲观并发对所有行为都加锁，怕在操作过程中意外影响，所以被称为悲观模式。甚至有点独占的意味。在这种情况下，读写是互相阻塞的。乐观并发通过把悲观并发在读写时加S锁的行为移到TempDB的行版本存储中，避免了读阻塞写的操作，从而把读写的资源争用降到最低，并尽量实现读操作不阻塞写操作，写操作永远阻塞读操作。

二：事务

不管使用悲观并发还是乐观并发，都会涉及事务这个概念，事务就是一个操作单元。这个操作可能是一行UPDATE语句，也可能是异常复杂的一系列增删改操作。

1.Atomicity，原子性。一个事务被当作单独的工作单元，不管事务内有什么东西，都被认为是一个整体。

2.Consistency，一致性。一个事务的执行前后状态都应该是一个逻辑一致性状态。

3.Isolation，隔离性。事务的内部不应该和其他事务有交互。

4.Durability，持久性，一旦事务完成，它的效果应该是永久的，不管是什么情况，包括系统关闭，都需要保证事务的结果。

SQL Server 默认保证其中3个特性：原子性、一致性、和持久性。如果要保证隔离性，就需要使用比默认级别更高级的隔离级别。

（1）.事务作用域

SQL Server 事务有四类作用域，其中两种（显示事务、自动提交事务）是默认的。另外两种（隐式事务、批范围事务）是特定情况下才用的。

一：自动提交事务

自动提交事务是一个单独的数据修改操作。任何INSERT、UPDATE、DELETE、MERGE、BULK INSERT等操作，都属于自动提交事务。一个单独的UPDATE语句，不管其影响行数是1行还是100万行，都会当做一个原子操作。在其中发生故障，SQL Server 会回滚这些操作，就当没有发生过一样。

if DB_ID('Demo_Isolation')is not null

drop database Demo_Isolation

create database Demo_Isolation

go

use Demo_Isolation

if OBJECT_ID('Demo_Table','U') is not null

drop table Demo_Table

create table Demo_Table

(

   id int identity(1,1),

   name nvarchar(32)

);

　　检查一下当前数据库的隔离级别。

dbcc useroptions

insert into dbo.Demo_Table(name)

select top 100000000 a.name

from sys.columns a cross join sys.columns b

cross join sys.columns

　　然后马上执行关闭服务，模拟系统故障。

insert into dbo.Demo_Table(name)

select top 100000000 a.name

from sys.columns a cross join sys.columns b

cross join sys.columns c

rollback

　　关闭窗体，没有办法强制回滚，只有在系统故障时事务才会回滚

（2）显式事务

显式事务是以BEGIN TRANSACTION开头、以COMMIT TRANSACTION或者ROLLBACK TRANSACTION结尾的、包含有任意数据量语句的代码块。这类事务是最常见的事务类型

（3）隐式事务

隐式事务必须在会话开始时，使用SET IMPLICIT_TRANSACTIONS ON 开启。不需要使用Begin tran作为开头，但是必须显示使用COMMIT/ROLLBACK TRAN结束事务。不建议使用这种方式，尽可能使用显示事务来定义

（4）批范围事务

用于在客户端连接字符串中使用了Multiple Active Result Sets（MARS）选项的事务。SQL Server会回滚所有以BEGIN TRAN开头但是不包含COMMIT TRAN的事务，目的是避免应用程序死锁。

2.事务隔离级别

每一个事务都运行在一个特定的事务隔离级别中，这个由会话的隔离级别决定。通过隔离级别，SQL Server 会决定锁的持有时间。如果会话/事务过程中没有使用SET TRANSACTION ISOLATION LEVEL【隔离级别】或者Lock hints指定，默认会使用当前数据库的隔离级别，通常为READ COMMITTED。

（1）READ UNCOMMITTED：允许所有脏读，不可重复读和幻读。

（2）READ COMMITTED：防止脏读，但允许不可重复读和幻读。

（3）REPEATABLE READ：防止脏读和不可重复读，但允许幻读。

（4）SERIALIZABLE：防止所有读现象

上面的4类隔离隔离级别中，除了READ COMMITTED（其中基于快照的一种）是乐观并发之外，其他都是悲观并发。在这几种隔离级别下，SQL Server会申请共享锁和排他锁，以避免当前事务正在读取的数据被另一个事务更改。从SQL Server2005开始，添加了新的乐观并发隔离级别，称为SNAPSHOT隔离，其中READ COMMITTED也加入了一种新的隔离（READ_COMMITTED_SNAPSHOT）,这两种隔离级别不需要加入共享锁，能够重一定程度上增强并发性。

隔离级别可以通过在会话的开端使用SET TRANSACTION ISOLATION LEVEL【隔离级别】来修改SQL Server的默认设置。也可以在语句中使用lock hints来修改。

3.需要预防的读现象

隔离级别存在的一个重要目的是预防3种读现象：脏读、不可重复读和幻读

（1）脏读

当事务运行在READ UNCOMMITTED隔离级别下时，会出现脏读。如果事务A修改了数据，但是没有提交，而另一个事务B此时又读取了这部分的数据，就很容易出现数据的非一致性状态，这种状态叫做脏读。默认情况下，SQL Server 使用了READ COMMITTED隔离级别，而这种级别不允许脏读，当事务正在修改的数据时，对其他事务是否能在提交前读取数据不会进行控制。这种特性会导致读取了非一致性的数据，俗称“脏数据”。除了数据不一致，还有另外一种潜在的风险，当事务A在READ UNCOMMITTED下进行大表扫描时，由于没有对访问的数据进行加锁,其他事务可以进行UPDATE操作。如事务A扫描了表中一半的数据，已经读取了某条数据，但是事务B中途进行了UPDATE操作，导致数据的存储位置改变，可能已经移到表的最末端。这时候事务A在完成扫描之后，实际上已经扫描了这条数据两次，甚至更多次。

打开两个窗口：

begin transaction;--显示事务

update Person.Person

set FirstName='James'

where LastName='Jones';

waitfor delay '00:00:05.000';

rollback transaction;

select FirstName,LastName from Person.Person where LastName='Jones';

set transaction isolation level read uncommitted;

select FirstName,LastName from Person.Person where LastName='Jones'

两边的数值不同，窗口A中使用了ROLLBACK,理论上应该回滚，但是由于窗口B 使用了READ UNCOMMITTED隔离级别，会出现脏读，所以窗口B中的数据读出来和窗口A的不一样。

（2）不可重复读

如果在一个事务中单独两次查询相同的数据，获得不一样的结果，就称为不可重复读。（在同一个事务中的两次查询）通常是因为两次读取数据之间，有其他事务对这些数据进行了修改操作。从而导致第二次读取到不一致的数据。

（3）幻读

这种情况发生在带有限定词的查询中，如where条件，如一个事务中，两个相同的where 条件的select操作，反回了不同的数据行。为幻读。

3.丢失更新

如果一个事务进行了UPDATE操作后，在未提交或者回滚事务前，另一个事务又做了同样的操作，这样就会覆盖第一次的update，导致第一次update就像没有出现过。所有的隔离级别都用于防止丢失更新，也就是说不允许两个事务同时更新相同的数据，以免其中一个事务的更新操作丢失。

DECLARE @SafetyStockLevel INT = 0 ,

    @Uplift INT = 5;

BEGIN TRAN;

SELECT  @SafetyStockLevel = SafetyStockLevel

FROM    Production.Product

WHERE   ProductID = 1;

SET @SafetyStockLevel = @SafetyStockLevel + @Uplift;

WAITFOR DELAY '00:00:05.000';

UPDATE  Production.Product

SET     SafetyStockLevel = @SafetyStockLevel

WHERE   ProductID = 1;

SELECT  SafetyStockLevel

FROM    Production.Product

WHERE   ProductID = 1;

COMMIT TRAN;

	DECLARE @SafetyStockLevel INT = 0 ,

		@Uplift INT = 100;

	BEGIN TRAN;

	SELECT  @SafetyStockLevel = SafetyStockLevel

	FROM    Production.Product

	WHERE   ProductID = 1;

	SET @SafetyStockLevel = @SafetyStockLevel + @Uplift;

	UPDATE  Production.Product

	SET     SafetyStockLevel = @SafetyStockLevel

	WHERE   ProductID = 1;

	SELECT  SafetyStockLevel

	FROM    Production.Product

	WHERE   ProductID = 1;

	COMMIT TRAN;

A的代码：在waitfor前面会把数据加5，等待5s。在等待的过程中，窗口B执行Update，由于窗口A的事务为提交，所以实际修改没有发生，所以对事务之外的事务来说，数据还是1000，在B中，实际上已经对‘1000’这个值update了。两次update虽然在独立的事务内，但是由于窗口A中的update操作是在后面提交的，最终覆盖了窗口B的update，导致窗口B的操作“没有发生过”。最后提交的操作会覆盖先提交的操作。

对于丢失更新的问题，可以使用悲观并发或者乐观并发来避免。避免丢失的操作是发生在更新前还是跟新后。悲观并发是在更新前避免，乐观并发是在更新后避免。在悲观并发模式中，读取数据时申请锁，这样一来，其他事务就无法更新了。同样，在更新时也加锁，让其他事务无法读。从而避免了丢失更新。

码农公寓

相关文章