1.事务的开启
使用 API 函数和 SQL 语句,可以在 SQL Server 数据库引擎实例中将事务作为显式、自动提交或隐式事务来启动。
-
通过 API 函数或通过发出 BEGIN TRANSACTION 语句来显式启动事务。
-
自动提交事务
-
数据库引擎的默认模式。每个单独的 SQL 语句都在其完成后提交。不必指定任何语句来控制事务。
-
隐式事务
-
通过 API 函数或 SET IMPLICIT_TRANSACTIONS ON 语句,将隐性事务模式设置为打开。下一个语句自动启动一个新事务。当该事务完成时,下一个 Transact-SQL 语句又将启动一个新事务。
-
当隐性事务模式设置为打开之后,当数据库引擎实例首次执行下列任何语句时,都会自动启动一个事务:
-
ALTER TABLE
INSERT
CREATE
OPEN
DELETE
REVOKE
DROP
SELECT
FETCH
TRUNCATE TABLE
GRANT
UPDATE
在发出 COMMIT 或 ROLLBACK 语句之前,该事务将一直保持有效。在第一个事务被提交或回滚之后,下次当连接执行以上任何语句时,数据库引擎实例都将自动启动一个新事务。该实例将不断地生成隐性事务链,直到隐性事务模式关闭为止。
-
批范围的事务
-
只适用于多个活动的结果集 (MARS),在 MARS 会话中启动的 Transact-SQL 显式或隐式事务将变成批范围的事务。当批处理完成时,如果批范围的事务还没有提交或回滚,SQL Server 将自动回滚该事务。
事务模式按连接级别进行管理。一个连接的事务模式发生变化对任何其他连接的事务模式没有影响。
2.事务的结束
可以使用 COMMIT 或 ROLLBACK 语句,或者通过 API 函数来结束事务。
-
COMMIT
-
如果事务成功,则提交。COMMIT 语句保证事务的所有修改在数据库中都永久有效。COMMIT 语句还释放事务使用的资源(例如,锁)。
-
ROLLBACK
-
如果事务中出现错误,或用户决定取消事务,则回滚该事务。ROLLBACK 语句通过将数据返回到它在事务开始时所处的状态,来取消事务中的所有修改。ROLLBACK 还释放事务占用的资源。
3.指定事务边界及错误处理
可以使用 Transact-SQL 语句来确定数据库引擎事务启动和结束的时间。
-
Transact-SQL 语句
-
可以使用 BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION、ROLLBACK WORK 和 SET IMPLICIT_TRANSACTIONS 语句来描述事务。这些语句主要用于 DB 库应用程序和 Transact-SQL 脚本(如使用 osql 命令提示实用工具运行的脚本)中。
错误处理
如果某个错误使事务无法成功完成,SQL Server 会自动回滚该事务,并释放该事务占用的所有资源。如果客户端与数据库引擎实例的网络连接中断了,那么当网络向实例通知该中断后,该连接的所有未完成事务均会被回滚。如果客户端应用程序失败或客户端计算机崩溃或重新启动,也会中断连接,而且当网络向数据库引擎实例通知该中断后,该实例会回滚所有未完成的连接。如果客户端从该应用程序注销,所有未完成的事务也会被回滚。
如果批中出现运行时语句错误(如违反约束),那么数据库引擎中的默认行为是只回滚产生该错误的语句。可以使用 SET XACT_ABORT 语句更改此行为。在执行 SET XACT_ABORT ON 语句后,任何运行时语句错误都将导致自动回滚当前事务。编译错误(如语法错误)不受 SET XACT_ABORT 的影响。
出现错误时,纠正操作(COMMIT 或 ROLLBACK)应包括在应用程序代码中。一种处理错误(包括那些事务中的错误)的有效工具是 Transact-SQL TRY...CATCH 构造。
4.高级事务
显式事务可以嵌套。这主要是为了支持存储过程中的一些事务,这些事务可以从已在事务中的进程调用,也可以从没有活动事务的进程中调用。
下列示例显示了嵌套事务的用途。TransProc
过程强制执行其事务,而不管执行事务的进程的事务模式。如果在事务活动时调用 TransProc
,很可能会忽略 TransProc
中的嵌套事务,而根据对外部事务采取的最终操作提交或回滚其 INSERT 语句。如果由不含未完成事务的进程执行 TransProc
,则在该过程结束时,COMMIT TRANSACTION 将有效地提交 INSERT 语句。
SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
USE AdventureWorks;
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
Colb CHAR(3) NOT NULL);
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc;
GO
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
EXECUTE TransProc 3,'bbb';
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans;
GO
SQL Server 数据库引擎将忽略内部事务的提交。根据最外部事务结束时采取的操作,将提交或者回滚内部事务。如果提交外部事务,也将提交内部嵌套事务。如果回滚外部事务,也将回滚所有内部事务,不管是否单独提交过内部事务。
对 COMMIT TRANSACTION 或 COMMIT WORK 的每个调用都应用于最后执行的 BEGIN TRANSACTION。如果嵌套 BEGIN TRANSACTION 语句,那么 COMMIT 语句只应用于最后一个嵌套的事务,也就是在最内部的事务。即使嵌套事务内部的 COMMIT TRANSACTION transaction_name 语句引用外部事务的事务名称,该提交也只应用于最内部的事务。
ROLLBACK TRANSACTION 语句的 transaction_name 参数引用一组命名嵌套事务的内部事务是非法的,transaction_name 只能引用最外部事务的事务名称。如果在一组嵌套事务的任意级别执行使用外部事务名称的 ROLLBACK TRANSACTION transaction_name 语句,那么所有嵌套事务都将回滚。如果在一组嵌套事务的任意级别执行没有 transaction_name 参数的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句,那么所有嵌套事务都将回滚,包括最外部事务。
5.事务保存点
保存点提供了一种机制,用于回滚部分事务。您可以使用 SAVE TRANSACTION savepoint_name 语句创建保存点。然后执行 ROLLBACK TRANSACTION savepoint_name 语句以回滚到保存点,而不是回滚到事务的起点。
在不可能发生错误的情况下,保存点很有用。在很少出现错误的情况下使用保存点回滚部分事务,比让每个事务在更新之前测试更新的有效性更为有效。更新和回滚操作代价很大,因此只有在遇到错误的可能性很小,而且预先检查更新的有效性的代价相对很高的情况下,使用保存点才会非常有效。
下面的示例说明了保存点在一个订单系统中的使用情况。该系统中存货不足的可能性很小,因为该公司具备有效的供应商和分购点。通常应用程序在尝试更新订单记录时,会先验证目前是否有足够的存货。下面的示例假定由于某种原因,验证目前可用存货量代价相对较大(由于连接到一个低速的调制解调器或广域网上)。可将应用程序编写为只进行更新,而且如果收到错误信息表明库存不足时,将回滚该更新。在这种情况下,在插入之后快速检查 @@ERROR 要比在更新之前验证库存数量的速度要快得多。
InvCtrl
表有一个 CHECK 约束,如果 QtyInStk
列低于 0,它就会触发 547 号错误。OrderStock
过程将创建一个保存点。如果出现 547 错误,它将回滚到该保存点,并将当前手边有的项目数返回给调用进程。然后调用进程可以针对现有的数量重新下订单。如果 OrderStock
返回 0,这就使调用进程确认当前有足够的存货来满足定购需要。
SET NOCOUNT OFF;
GO
USE AdventureWorks;
GO
CREATE TABLE InvCtrl
(WhrhousID int,
PartNmbr int,
QtyInStk int,
ReordrPt int,
CONSTRAINT InvPK PRIMARY KEY
(WhrhousID, PartNmbr),
CONSTRAINT QtyStkCheck CHECK (QtyInStk > 0) );
GO
CREATE PROCEDURE OrderStock
@WhrhousID int,
@PartNmbr int,
@OrderQty int
AS
DECLARE @ErrorVar int;
SAVE TRANSACTION StkOrdTrn;
UPDATE InvCtrl SET QtyInStk = QtyInStk - @OrderQty
WHERE WhrhousID = @WhrhousID
AND PartNmbr = @PartNmbr;
SELECT @ErrorVar = @@error;
IF (@ErrorVar = 547)
BEGIN
ROLLBACK TRANSACTION StkOrdTrn;
RETURN (SELECT QtyInStk
FROM InvCtrl
WHERE WhrhousID = @WhrhousID
AND PartNmbr = @PartNmbr);
END
ELSE
RETURN 0;
GO
6.事务隔离级别
设置事务隔离级别使程序员面临的风险因某些完整性问题而增加,但好处是可以支持更好的数据并发访问。事务隔离级别有:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SNAPSHOT
- SERIALIZABLE
下表显示了不同隔离级别允许的并发副作用。
隔离级别 | 更新丢失 | 脏读取 | 重复读取 | 幻读 |
未提交读取 | N | Y | Y | Y |
提交读取 | N | N | Y | Y |
可重复读取 | N | N | N | Y |
快照 序列化 | N N | N N | N N | N N |
ALTER DATABASE | DROP DATABASE |
ALTER FULLTEXT CATALOG | DROP FULLTEXT CATALOG |
ALTER FULLTEXT INDEX | DROP FULLTEXT INDEX |
BACKUP | RECONFIGURE |
CREATE DATABASE | RESTORE |
CREATE FULLTEXT CATALOG | UPDATE STATISTICS |
CREATE FULLTEXT INDEX |
也不能使用下列语句:
- 显式事务中的全文系统存储过程。
- sp_dboption 用于设置数据库选项,或在显式事务或隐式事务内部修改 master 数据库的任何系统过程。
尽可能使事务保持简短很重要。当事务启动后,数据库管理系统 (DBMS) 必须在事务结束之前保留很多资源,以保护事务的原子性、一致性、隔离性和持久性 (ACID) 属性。如果修改数据,则必须用排他锁保护修改过的行,以防止任何其他事务读取这些行,并且必须将排他锁控制到提交或回滚事务时为止。根据事务隔离级别设置,SELECT 语句可以获取必须控制到提交或回滚事务时为止的锁。特别是在有很多用户的系统中,必须尽可能使事务保持简短以减少并发连接间的资源锁定争夺。在有少量用户的系统中,运行时间长、效率低的事务可能不会成为问题,但是在有上千个用户的系统中,将不能忍受这样的事务。
![](http://i.msdn.microsoft.com/Global/Images/clear.gif)
以下是编写有效事务的指导原则:
- 不要在事务处理期间要求用户输入。
在事务启动之前,获得所有需要的用户输入。如果在事务处理期间还需要其他用户输入,则回滚当前事务,并在提供了用户输入之后重新启动该事务。即使用户立即响应,作为人,其反应时间也要比计算机慢得多。事务占用的所有资源都要保留相当长的时间,这有可能会造成阻塞问题。如果用户没有响应,事务仍然会保持活动状态,从而锁定关键资源直到用户响应为止,但是用户可能会几分钟甚至几个小时都不响应。 - 在浏览数据时,尽量不要打开事务。
在所有预备的数据分析完成之前,不应启动事务。 - 尽可能使事务保持简短。
在知道要进行的修改之后,启动事务,执行修改语句,然后立即提交或回滚。只有在需要时才打开事务。 - 若要减少阻塞,请考虑针对只读查询使用基于行版本控制的隔离级别。
- 灵活地使用更低的事务隔离级别。
可以很容易地编写出许多使用只读事务隔离级别的应用程序。并不是所有事务都要求可序列化的事务隔离级别。 - 灵活地使用更低的游标并发选项,例如开放式并发选项。
在并发更新的可能性很小的系统中,处理“别人在您读取数据后更改了数据”的偶然错误的开销要比在读取数据时始终锁定行的开销小得多。 - 在事务中尽量使访问的数据量最小。
这样可以减少锁定的行数,从而减少事务之间的争夺。