SQL 事务（transaction）锁（lock）游标（cursor）

SQL

事务

一个事务（ Transaction ）是由一系列的数据库查询操作和更新操作构成的，把这一系列操作作为单个逻辑工作单元执行，并且是不可分的。

1. 事务的特性和强制事务机制

1）事务的特性

原子性( A ) ：一个事务中的所有操作是一个逻辑上不可分割的单位。
一致性( C ) ：事务在完成时，必须使所有的数据都保持一致状态。
隔离性( I ) ：一个事务的执行不能被另一个事务干扰。
持久性( D )：指一个事务一旦提交，则它对数据库中数据的改变就应该是永久的。

2）强制事务机制

事务管理： 强制保持事务的原子性和一致性。事务启动之后，就必须成功完成，否则数据库引擎实例将撤消该事务启动之后对数据所做的所有修改。
锁机制：锁定设备，强制保持事务的隔离性。
事务日志：记录设备，保证事务的持久性。即使服务器硬件、操作系统或数据库引擎实例自身出现故障，该实例也可以在重新启动时使用事务日志，将所有未完成的事务自动地回滚到系统出现故障的点。

2. 事务定义语句

（1）BEGIN TRANSACTION 语句
BEGIN TRANSACTION 语句定义一个事务的开始

（2）COMMIT TRANSACTION 语句
COMMIT TRANSACTION 是提交一个事务语句

（3）ROLLBACK TRANSACTION 语句
ROLLBACK TRANSACTION 是回滚事务语句

• 若回滚到事务的起点，也可使用 ROLLBACK WORK

使用事务处理删除学号为 “ 125204999 ” 学生信息。

代码如下：

USE STUMS
GO
DECLARE @tran_name varchar(32)
SELECT @tran_name = 'Transaction_delete'
BEGIN TRAN @tran_name  /*开始事务*/
DELETE 学生基本信息 WHERE 学号 = '125204999'
DELETE 选课 WHERE 学号 = '125204999'
COMMIT TRAN @tran_name /*提交事务*/
GO

3. 并发问题

在没有上锁的前提下，多个用户同时访问一个数据库，此时他们的事务同时使用相同的数据时可能会发生问题。这些问题包括以下几种情况：

1）丢失更新

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。
每个事务都不知道其他事务的存在，最后的更新将重写由其他事务所做的更新，这将导致数据更新丢失。

例如，有两个用户同时访问 STUMS 数据库的 “ 学生基本信息 ” 表，并读入同一数据进行修改，然后保存更改结果。这样，后保存其更改结果的用户就覆盖了第一个用户所做的更新，破坏了第一个用户提交的结果，导致第一个用户的更新被丢失。如果在第一个用户完成之后第二个用户才能进行更改，则可以避免该问题。

2）脏读

脏读是指事务1 修改某数据后，事务2 读取了这一数据，事务1 又由于某种原因撤销其修改，将修改过的数据恢复原值。这样，事务2 读到的数据就与数据库中的数据不一致了，我们称事务2 为 “ 脏读 ” ，读取的数据为 “ 脏 ” 数据。

例如，一个用户正在修改 STUMS 数据库的教师表中 “ 职称 ” 列的数据，在更改过程中，另一用户读取了教师表的数据。此后，第一个用户发现职称列的数据修改错了，于是删除了所做的修改，将其恢复到原数据并保存。这样，事务2读到的数据包含不再存在的修改内容，并且这些修改内容应认为从未存在过，即“脏读”。如果在第一个用户确定最终更改前任何人都不能读取更改的数据，则可以避免该问题。

3）不可重复读

不可重复读是指事务1 读取数据后，事务2 执行了更新操作，使事务1 无法再现第一次读取的结果。针对插入、修改和删除的更新操作，不可重复读有下列三种情况：
① 事务1 按一定条件从数据库中读取了某些数据记录之后，事务2 插入了一些记录，当事务1 再次按相同条件读取数据时，发现多了一些数据记录。
② 事务1 读取了某一数据记录之后，事务2 对其做了修改，当事务1 再次读取这一数据时，得到与前一次不同的值。
③ 事务1 按一定条件从数据库中读取了某些数据记录之后，事务2 删除了其中部分记录，当事务1 再次按相同条件读取数据时，发现某些数据记录不见了。

例如，一个用户两次读取 STUMS 数据库的选课表中记录信息，但在两次读取之间，另一用户正在进行选修课成绩的录入操作，重写了该文档。当第一个用户再次读取选课表中的数据时，其数据信息已更改，使第一次读取不可重复。如果只有在录入操作的用户全部完成录入后，才可以让其他用户访问选课表数据，则可以避免该问题。

4）幻象读

幻象读属不可重复读的特例。当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻象读问题。
事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其他事务删除。同样，由于其他事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。

例如，学生处正通过学生信息管理系统统计应届毕业生数，而此时教务处却因毕业班的某学生考试作弊将其开除正整理数据库，将该考生的信息从数据库中删除。这样，就发生了幻象读，导致学生处的统计数据不正确。如果只有在数据删除工作完成后，才让学生处访问 STUMS 数据库，则可以避免该问题。

锁

1. 锁的分类

1）共享锁

共享（S）锁允许并发事务读取（ SELECT ）一个资源。

2）排他锁

排它锁（X）又称互斥锁，可以防止并发事务对资源进行访问。当需要对表进行 INSERT、UPDATE 或 DELETE操作时，应该使用排它锁。

3）更新锁

更新（U）锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享锁，然后修改行，此操作要求锁转换为排他锁。

4）意向锁

意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享锁或排他锁。 意向锁包括意向共享（IS）、意向排他（IX）以及与意向排他共享（SIX）。

5）架构锁

用于保护数据库的模式，又称模式锁。

6）大容量更新锁

当将数据大容量复制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 设置了 table lock on bulk 表选项时，将使用大容量更新（BU）锁。

7） 键范围锁

在使用可序列化事务隔离级别时，对于 T-SQL 语句读取的记录集，键范围锁可以隐式保护该记录集中包含的行范围。键范围锁可防止幻读。通过保护行之间键的范围，它还防止对事务访问的记录集进行幻像插入或删除。

2. 锁定提示

可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中为单个表引用指定锁定提示。

为 “ 学生基本信息 ” 表加一个共享锁，并且保持到事务结束时再释放。

代码如下：

USE STUMS
GO
SELECT * FROM 学生基本信息 (TABLOCK HOLDLOCK)
GO

修改 “ 选课 ” 表学分列数据，为 “ 选课 ” 表加一个更新锁，并且保持到事务结束时再释放。

代码如下：

USE STUMS
GO
UPDATE 选课 WITH(UPDLOCK HOLDLOCK)
SET 学分 = 3 WHERE 成绩 >= 60
GO

3. 死锁

在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。

1）死锁检测

• 死锁检测是由锁监视器线程执行的，该线程定期搜索数据库引擎实例的所有任务。

锁监视器搜索进程几点处理方法 ：

默认时间间隔为 5 秒。
如果锁监视器线程查找死锁，根据死锁的频率，死锁检测时间间隔将从 5 秒开始减小，最小为 100 毫秒。
如果锁监视器线程停止查找死锁，数据库引擎将两个搜索间的时间间隔增加到 5 秒。
如果刚刚检测到死锁，则假定必须等待锁的下一个线程正进入死锁循环。检测到死锁后，第一对锁等待将立即触发死锁搜索，而不是等待下一个死锁检测时间间隔。

2） 死锁优先级

使用 SET DEADLOCK_PRIORITY 语句设置死锁优先级。

代码如下：

/*使用变量将死锁优先级设置为 LOW */
DECLARE @deadlock_var NCHAR(3)
SET @deadlock_var = N'LOW'
SET DEADLOCK_PRIORITY @deadlock_var
GO
/*将死锁优先级设置为 NORMAL*/
SET DEADLOCK_PRIORITY NORMAL;
GO

将锁超时期限设置为 1,800 毫秒。

代码如下：

SET LOCK_TIMEOUT 1800
GO

游标

1. 游标概念

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标是一种数据访问机制，它允许用户访问单独的数据行，而并非对整个行集合进行操作。游标包括以下两个部分。
游标结果集（ Cursor Result Set ）：由定义该游标的 SELECT 语句返回的行的集合（可以是零行、一行或多行）。
游标位置( Cursor Position ）：指向游标结果集中某一行的当前指针。

2. 游标类型

静态游标在滚动期间很少或根本检测不到变化，但消耗的资源相对很少。
动态游标在滚动期间能检测到所有变化，但消耗的资源却较多。
由键集驱动的游标介于静态和动态之间，能检测到大部分变化，但比动态游标消耗更少的资源。
只进游标和滚动都作为能应用到静态游标、由键集驱动的游标和动态游标的选项。

3. 游标函数

• @@CURSOR_ROWS

可调用 @@CURSOR_ROWS 以确定当其被调用时检索了游标符合条件的行数。其语法格式为：
@@CURSOR_ROWS
返回类型为 integer
返回值有以下几种：
-m：游标被异步填充。返回值 ( -m ) 是键集中当前的行数。
-1：游标为动态游标。因为动态游标可反映所有更改，所以游标符合条件的行数不断变化。因此，永远不能确定已检索到所有符合条件的行。
0：没有已打开的游标，对于上一个打开的游标没有符合条件的行，或上一个打开的游标已被关闭或被释放。
n：游标已完全填充。返回值 (n) 是游标中的总行数。

声明一个 xs_Cursor 游标，并且使用 SELECT 显示 @@CURSOR_ROWS 的值。

USE STUMS
GO
SELECT @@CURSOR_ROWS
DECLARE xs_Cursor CURSOR FOR
SELECT 姓名, @@CURSOR_ROWS
FROM 学生基本信息
OPEN xs_Cursor
FETCH NEXT FROM xs_Cursor
SELECT @@CURSOR_ROWS
CLOSE xs_Cursor
DEALLOCATE xs_Cursor
GO

• @@FETCH_STATUS

通过检测 @@Fetch_Status 的值，可以获得 Fetch 语句的状态信息，该状态信息用于判断该 Fetch 语句返回数据的有效性。其语法格式为：
@@FETCH_STATUS
返回类型为 integer。当执行一条 Fetch 语句之后， @@Fetch_Status 可能出现以下三种值：
0：Fetch 语句成功。
-1：Fetch 语句失败或行不在结果集中。
-2：提取的行不存在。

说明：由于 @@FETCH_STATUS 对于在一个连接上的所有游标都是全局性的，所以要谨慎使用 @@FETCH_STATUS。在执行一条 FETCH 语句后，必须在对另一游标执行另一 FETCH 语句前测试 @@FETCH_STATUS。在此连接上出现任何提取操作之前，@@FETCH_STATUS 的值没有定义。

声明一个 xs_Cursor 游标，并且使用 @@FETCH_STATUS 控制一个 WHILE 循环中的游标活动。

USE STUMS
GO
DECLARE xs_Cursor CURSOR FOR	-- 声明游标
SELECT 学号, 姓名, 出生日期
FROM 学生基本信息
WHERE 性别 = '女'
OPEN xs_Cursor 		-- 打开游标
FETCH NEXT FROM xs_Cursor	-- 提取上一个提取行的后面的一行，如果Fetch Next为对游标的第一次提取操作，
						    --则返回结果集中的第一行。NEXT 为默认的游标提取选项。
WHILE @@FETCH_STATUS = 0	-- Fetch 语句成功
BEGIN
FETCH NEXT FROM xs_Cursor
END
CLOSE xs_Cursor		-- 关闭游标
DEALLOCATE xs_Cursor	-- 释放游标
GO