SQL Server 事务和锁

事务是由一系列的数据操作命令组成，是数据库应用程序的基本逻辑单元。SQL Server 在对数据库进行操作时，通过事务来保证数据的一致性和完整性
由用户访问引发的数据操作经常会是同时发生在多个数据表上的一系列的处理，为了能够保证数据的一致性，必须要求这些操作要么全部执行要么全部不执行，而不能发生数据操作的中断。
用户访问数据库时，并发的情况是常态，数据库系统的并发处理能力是衡量其性能的重要标志之一。计算机系统通过适当的并发控制机制协调并发操作，保证数据的一致性。在SQL Server 2016中，以事务为基本操作单位，使用锁来实现并发控制。

事务概述

在计算机系统设计过程中，与一个事务相关的数据必须保证可靠性、一致性和完整性，以符合实际的企业生产过程。

现实生活中如网上购物、股票交易、银行借贷等都是的采用事务方式来处理。

在SQL Server中，通常由事务来完成相关操作，以确保多个数据的修改作为一个单元来处理。

特点

事务（Transaction）是数据库单个的操作单元。如果某一事务执行成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被还原。

在SQL Server中，事务作为单个逻辑工作单元来执行一系列操作，具有4个特点:

1. 原子性（Atomicity）

事务包含的一系列数据操作是是一个整体，执行过程中要么全部执行，要么全部不执行。

2. 一致性(Consistency)

事务执行完成后，将数据库从一个一致状态转变到另一个一致状态，事务不能违背定义在数据库中的任何完整性检查。一致性在逻辑上不是独立的，它是由事务的隔离性来表示。

3. 隔离性(Isolation)

一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
事务隔离，但是数据不隔离。

4. 持久性(Durability)

要求一旦事务提交，那么对数据库所做的修改将是持久的，无论发生何种机器和系统故障，都不应该对其有任何影响。

SQL Server 2016数据库引擎会通过事务日志强制执行事务的物理一致性，并且保证事务的持久性。
SQL Server 2016还会强制对约束、数据类型以及其他内容执行一切一致性检查以确保逻辑上的一致性。

管理事务

一般来说，事务的基本操作包括启动、保存、提交或回滚等。本节将对不同类型的事务操作进行详细的介绍。

1. 启动事务

显式事务的定义
显式事务需要明确定义事务的启动。显式事务的定义格式如下：

BEGIN {TRAN | TRANSACTION}
   [{transaction_name | @tran_name_variable }
   [WITH MARK[ 'description' ]]   ]

【例10.1】将teaching数据库的course表中课程号为c05103的课程名称为“高等数学”，并提交该事务

DECLARE @TranName VARCHAR(20);
SELECT @TranName = 'Add_Score';
BEGIN TRAN @TranName;
 update course set cname='高等数学'
 where courseno='c05103'
-- 提交事务
COMMIT TRAN @TranName;
GO

隐式事务的定义：隐式事务不需要begin开启

默认情况下的隐式事务是关闭的，使用隐式事务需要先将事务模式设置为隐式事务模式。注意不再使用隐式事务时，要退出该模式。

 SET IMPLICIT_TRANSACTIONS {ON | OFF}

【例10.2】分别使用显式事务和隐式事务向表course中插入两条记录。

--first part显式事务
SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS OFF;
GO
PRINT N'Tran count at start = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

BEGIN TRANSACTION
  INSERT INTO course
      VALUES('c05141','WIN程序设计','选修',64,16,7);
  PRINT N'Tran count at 1st = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
  INSERT INTO course
     VALUES('c05142','WEB程序设计','选修',64,16,7);
  PRINT N'Tran count at 2nd = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
-- 提交事务
COMMIT TRANSACTION
GO


-- second part隐式事务
PRINT N'Setting IMPLICIT_TRANSACTIONS ON.';
SET IMPLICIT_TRANSACTIONS ON;
PRINT N'Use implicit transactions.';
-- No BEGIN TRAN needed here.
INSERT INTO course
     VALUES('c05151','管理信息系统','选修',48,16,3);
PRINT N'Tran count in 1st implicit transaction = '
     + CAST(@@TRANCOUNT AS NVARCHAR(10));
INSERT INTO course
     VALUES('c05152','电子商务','选修',48,16,5);
PRINT N'Tran count in 2nd implicit transaction = '
     + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
-- 提交事务
COMMIT TRANSACTION;
PRINT N'Tran count after implicit transaction = '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
SET IMPLICIT_TRANSACTIONS OFF;
GO

2 .保存事务

为了提高事务执行的效率，或者进行程序的调试等，可以在事务的某一点处设置一个标记（保存点），这样当使用回滚语句时，可以不用回滚到事务的起始位置，而是回滚到标记所在的位置即保存点。
保存点设置及使用格式。

SAVE {TRAN | TRANSACTION} {savepoint_name | @savepoint_variable}
ROLLBACK TRANSACTION {savepoint_name | @savepoint_variable}

【例10.3】定义一个事务，向course表中添加一条记录，并设置保存点。然后再删除该记录，并回滚到事务的保存点，提交事务。

BEGIN TRAN;
INSERT INTO course
VALUES('c05139','统一建模语言UML','选修',48,16,6);
SAVE TRAN savepoint;
DELETE FROM course
WHERE courseno='c05139';
ROLLBACK TRAN savepoint;
COMMIT TRAN
GO

3 .提交事务

提交事务标志着一个执行成功的隐式事务或显式事务的结束。
事务提交后，自事务开始以来所执行的所有数据修改被持久化，事务占用的资源被释放。

COMMIT {TRAN | TRANSACTION}
[transaction_name | @tran_name_variable]

【例10.4】定义事务更新course表中课程号为c05109的课程名称为“数理统计”，然后回滚该事务。

begin transaction   Subtract_course
  update course set cname='数理统计'
  where courseno='c05109'
  if exists( select * from course where courseno='c05109')
  	rollback transaction  Subtract_course
  else
    commit transaction  Subtract_course

4 .回滚事务

回滚事务是指清除自事务的起点或到某个保存点所做的所有数据修改。释放由事务控制的资源。

ROLLBACK {TRAN | TRANSACTION}
 [transaction_name | @tran_name_variable 
  | savepoint_name | @savepoint_variable ]

5 .自动提交事务

SQL Server在启动显式事务，或者隐性事务模式设置为打开之前，都将以自动提交模式进行操作。在关闭隐性事务模式设置时，SQL Server 为自动提交模式。
在自动提交模式下，发生回滚操作的内容取决于遇到的错误类型。当遇到运行时错误时，仅回滚发生错误的语句；当遇到的错误时编译错误时，回滚所有的语句。

【例10.5】比较自动提交事务发生运行时错误和编译时错误的处理情况。

--发生编译错误的事务示例：
INSERT INTO course VALUES('c11111','测试课程','必修',48,8,5);
INSERT INTO course VALUES('c22222','测试课程','必修',48,8,5);
INSERT INTO course VALUSE('c33333','测试课程','必修',48,8,5); VALUSE--语法错误
SELECT * FROM course;
GO


--发生运行时错误的事务示例：
INSERT INTO course VALUES('c11111','测试课程','必修',48,8,5);
INSERT INTO course VALUES('c22222','测试课程','必修',48,8,5);
INSERT INTO course VALUES('c11111','测试课程','必修',48,8,5); --重复键
SELECT * FROM course;
GO

6 .事务嵌套

在显式事务中再定义事务，称为嵌套事务。SQL Server 2016支持嵌套事务最重要的原因为了允许在存储过程中使用事务而不必顾及这个事务本身是否在另一个事务中被调用的。
下面对于嵌套事务进行如下说明。
（1）SQL Server数据库引擎忽略内部事务的提交。
（2）对COMMIT TRANSACTION的每个调用都必须用于事务最后执行的语句。如果嵌套BEGIN TRANSACTION语句，那么COMMIT语句只应用于最后一个嵌套事务。
（3）ROLLBACK TRANSACTION语句的transaction_name transaction_name只能引用外部事务的事务名称。

（4）@@TRANCOUNT函数可以记录当前事务的嵌套级别。每个BEGIN TRANSACTION语句使@@TRANCOUNT增加1。每个COMMIT TRANSACTION语句使@@TRANCOUNT减去1。如果@@TRANCOUNT等于0，则表明当前操作不在事务中。
（5）默认情况下，隐式事务是不能嵌套的。

【例10.6】嵌套事务提交后，外部事务发生回滚。
【例10.7】使用@@TRANCOUNT函数查看事务的嵌套级别。

管理并发数据

用户创建会话访问服务器时，系统会为用户分配私有内存区域，保存当前用户的数据和控制信息，每个用户进程通过访问自己的私有内存区访问服务器，用户之间互不干扰，以此实现并发数据访问的控制。当数据库引擎所支持的并发操作数较大时，数据库并发程序就会增多。

控制多个用户如何同时访问和更改共享数据而不会彼此冲突称为并发控制。在SQL Server中，并发控制是通过用锁来实现的。

并发的影响

多个用户访问同一个数据资源时，如果数据存储系统没有并发控制，就会出现并发问题，比如修改数据的用户会影响同时读取或修改相同数据的其他用户。

下面是使用SQL Server时可能出现的一些并发问题。

（1）更新丢失（lost update）

当两个或多个事务选择同一行，然后根据最初选定的值更新该行时，就会出现更新丢失的问题。每个事务都不知道其他事务的存在。最后的更新将覆盖其他事务所做的更新，从而导致数据丢失。

（2）不可重复读（unrepeatable read）

当一个事务多次访问同一行且每次读取不同数据时，会出现不可重复读问题。因为其他事务可能正在更新该事务正在读取的数据。

（3）幻读（plantom read）

当对某行执行插入或删除操作，而该行属于某事务正在读取的行的范围时，就会出现幻读问题。
由于其他事务的删除操作，使事务第一次读取行范围时存在的行在后续读取时已不存在。
与此类似，由于其他事务的插入操作，后续读取显示原来读取时并不存在的行。

（4）脏读（dirty read）

即读出的是不正确的临时数据。
例如，当第2个事务选择第1个事务正在更新的行时，就会出现此问题。
第2个事务正在读取的数据尚未被其他事务提交，并可能由更新此行的事务更改。

3.事务的隔离级别

锁在用作事务控制机制时，可以解决并发问题。在同一时间可以运行多个事务时，锁允许事务独立运行。

事务可以设置隔离级别，隔离级别描述了一个事务必须与其他事务所进行的资源或数据更改相隔离的程度。
隔离级别从允许并发负面影响（如脏读、幻读等）的角度进行描述。
SQL Server 2016支持的隔离级别可以通过编程方式进行设置，也可以通过使用SQL语法 SET TRANSACTION ISOLATION LEVEL进行设置。
下面是使用SET TRANSACTIOIN ISOLATION LEVEL设置隔离级别的语法。

SET TRANSACTION ISOLATION LEVEL
{   READ UNCOMMITTED
   | READ COMMITTED
   | REPEATABLE READ
   | SNAPSHOT
   | SERIALIZABLE  }

【例10.8】将隔离级别设置为REPEATABLE READ时，对于后续每个Transact-SQL语句，SQL Server将所有共享锁保持到事务结束。

管理锁

当多个用户或应用程序同时访问同一数据时，锁可以防止这些用户或应用程序同时对数据进行更改，确保事务的完整性和数据的一致性。

锁由SQL Server数据库引擎在内部进行管理。数据库引擎可以根据用户采取的操作，会自动获取和释放锁。

当事务开始并在事务内以查询语言、DML或DDL语言执行命令时，SQL Server 会锁定任何所需的资源以帮助保护所需隔离级别的资源。
默认情况下，行级锁定用于数据页，页级锁定用于索引页。
为保留系统资源，当超过行锁数的可配置阈值时，锁管理器将自动执行锁升级。在锁管理器中可以为每个会话分配的最大锁数是262143。

1.锁的类型

锁的类型确定并发事务可以访问数据的方式。SQL Server根据必须锁定的资源和必须执行的操作来确定使用哪种锁。

锁定的粒度

可以锁定的资源指锁定的粒度或发生锁定的级别。

默认情况下，行级锁用于数据页，页级锁用于索引页。为保留系统资源，当超过行锁数的可配置阈值时，锁管理器将自动执行锁升级。
在较小粒度（如行级）上锁定会提高并发性，但开销更多，因为如果锁定许多行，则必须持有更多的锁。在较大粒度（如表级）上锁定会降低并发性，因为锁定整个表会限制其他事务对该表任何部分的访问。可以锁定的资源主要包括行、数据页、架构、表和数据库等。如表10-3所示。

3.锁的兼容性

如果某个事务已锁定一个资源，而另一个事务又需要访问该资源，那么SQL Server会根据第一个事务所用锁定模式的兼容性确定是否授予第二个锁。
对于已锁定的资源，只能施加兼容类型的锁。资源的锁定模式有一个兼容性矩阵，可以显示哪些锁与在同一资源上获取的其他锁兼容，并按照锁强度递增的顺序列出这些锁。表10-4显示了请求的锁定模式及其与现有锁定模式的兼容性。

例如，如果持有排他（X）锁，那么除非在第一个事务结束时释放该X锁，否则其他事务将无法获取该资源的共享锁、更新锁或排他锁。相反，如果已向某个资源应用共享（S）锁，那么即使第一个事务尚未完成，其他事务也可以获取该资源的共享锁或更新（U）锁。但是，只有在释放共享锁之后，其他事务才可以获取排他锁。
注意
IX锁与IX锁定模式兼容，因为IX指示其意向是更新某些行，而不是更新所有行。只要不影响其他事务正在更新的行，那么也允许其他事务读取或更新某些行。

4.死锁（deadlock）

死锁是指事务永远不会释放它们所占用的锁，死锁中的两个事务都将无限期等待下去。

SQL Server 2016对并发事务的处理，使用任何方案都会导致死锁问题。
第1种情况是：
两个事务分别锁定了两个单独的对象，这时每一个事务都要求在另外一个事务锁定的对象上获得一个锁，结果是每一个事务都必须等待另外一个事务释放占有的锁，此时就发生了死锁。这种死锁是最典型的死锁形式。

第2种情况是：不能100%复现
在一个数据库中，有若干长时间运行的事务并行的执行操作，查询分析器处理非常复杂的查询时，例如连接查询，由于不能控制处理的顺序，有可能发生死锁。

解决方案：自动检测死锁循环
SQL Server 2016的SQL Server Database Engine自动检测死锁循环，并选择一个会话作为死锁中放弃的一方，通过终止该事务来打断死锁。被终止的事务发生回滚，并返回给连接一个错误消息。
如果在交互式的Transact-SQL语句中发生死锁错误，用户只要简单地重新输入Transact-SQL语句即可。在程序中的Transact-SQL中，应用程序必须提供对死锁错误码的处理，例如通过提示信息通知用户或者自动再次执行该事务。

【例10.9】本例制造了一个简单的死锁场景，并由SQL Server检测和处理死锁。
具体步骤和代码如下。
（1）启动SQL Server Management Studio并打开一个查询设计器窗口。
（2）输入并执行以下代码来创建一个表t1，并在不关闭事务的情况下插入数据。
CREATE TABLE t1(i int);
BEGIN TRAN;
INSERT INTO t1 VALUES(1);
（3）打开第2个查询窗口并执行以下语句创建另一个表t2，并在其中插入数据，然后尝试在表t1中更新数据。
CREATE TABLE t2(i int);
BEGIN TRAN;
INSERT INTO t2 VALUES(1);
UPDATE t1 SET i=2;
由于在查询1中的事务没有提交，因此这个事务将被阻塞。
（4）切换回查询窗口1，执行以下UPDATE语句更新表t2。此时会发生什么结果呢？
UPDATE t2 SET i=3;
在几秒钟后，其中一个事务被取消了，并且返回了一个错误消息，如图10-1所示。

在发生死锁的两个事务中，根据事务处理时间的长短确定事务的优先级。处理时间长的事务具有较高的优先级，处理时间短的事务具有较低的优先级。在发生冲突时，保留优先级高的事务，牺牲优先级低的事务。
为了防止并处理死锁，应该遵守以下原则。
（1）事务中需要按照同一顺序访问数据库对象，避免在事务中存在用户交互访问数据的情况。
（2）尽量保持事务简短并处于一个批处理中，尽量使用基于行版本控制的隔离级别。
（3）处理事务时尽量设置和使用较低的隔离级别

5 .显示锁定信息

为了查看数据库引擎实例中的锁信息，可以使用动态管理视图sys.dm_tran_locks。这个视图返回有关当前活动的锁管理器资源的信息。向锁管理器发出的已授予锁或正等待授予锁的每个当前活动请求分别对应一行。
结果集中的列大体分为两组：资源组和请求组。资源组说明正在进行锁请求的资源，请求组说明锁请求。查看锁信息可以通过系统视图sys.dm_tran_locks进行查看。
【例10.10】使用sys.dm_tran_locks视图查看锁的信息。
（1）启动SQL Server Management Studio并创建一个查询设计器窗口。
（2）输入并执行下列语句，对course表进行查询、插入和更新

USE teaching;
GO
BEGIN TRAN
SELECT courseno,cname
FROM course
--WITH(holdlock, rowlock)
WHERE credit=2.0;
INSERT INTO course
VALUES('c11222','数据库概论','必修',48,16,7);
UPDATE course SET cname='数据库原理'
WHERE courseno='c11222';
5 .显示锁定信息
【例10.10】使用sys.dm_tran_locks视图查看锁的信息。
（3）为了查看事务中使用的锁的信息，使用动态管理视图sys.dm_tran_locks。在查询窗口中键入并执行以下SELECT语句来获取锁信息并提交事务。
SELECT resource_type, resource_associated_entity_id,
request_status, request_mode, request_session_id,
resource_description
FROM sys.dm_tran_locks
WHERE resource_database_id=DB_ID('teaching');
（4）查询结果如图10-2所示。
（5）提交事务。 COMMIT TRAN
小结
通过本章的学习，了解到SQL Server中所有的数据访问都是通过事务进行的，以及SQL Server如何在事务间通过锁来实现并发控制。事务的4项基本特性及锁的使用，目的都是为了保证数据的一致性和完整性。通过学习要求掌握如下的内容。
事务和锁的基本概念。
定义显式或隐式事务的启动和应用。
事务的嵌套定义。
如何通过定义隔离级别实现事务访问资源和数据的隔离，以及隔离级别与并发问题的关系。
锁的类型和管理。

posted @ 2022-11-25 10:20 kingwzun 阅读(672) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

kingwzun