190.事务管理与并发控制

第10章事务管理与并发控制

•    10.1 事务的基本概念

 

10.1.1 事务

Ø 事务(Transaction)是构成单一逻辑工作单元的数据库操作序列。这些操作是一个统一的整体，要么全部成功执行（执行结果写到物理数据文件），要么全部不执行（执行结果没有写到任何的物理数据文件）。也可以这样理解，事务是若干操作语句的序列，这些语句序列要么全部成功执行，要么全部都不执行。全部不执行的情况是：在执行到这些语句序列中的某一条语句时，由于某种原因（如断电、磁盘故障等）而导致该语句执行失败，这时将撤销在该语句之前已经执行的语句所产生的结果，使数据库恢复到执行这些语句序列之前的状态。

【例子】对于银行转帐问题，可以表述为：将帐户A1上的金额x转到帐户A2。这个操作过程可以用如图10.1所示的流程表示。

 

•         如果转帐程序在刚好执行完操作③的时刻出现硬件故障，并由此导致程序运行中断，那么数据库就处于这样的状态：帐号A1中已经被扣除金额x（转出部分），而帐号A2并没有增加相应的金额x。也就是说，已经从帐号A1上转出金额x，但帐号A2并没有收到这批钱。显然，这种情况在实际应用决不允许出现。

•         如果将上述操作①至⑤定义为一个事务，由于事务中的操作要么全都执行，要么全都不执行，那么就可以避免出现上述错误的状态。这就是事务的魅力。

 

 

10.1.2 事务的ACID特性

Ø 作为一种特殊的数据库操作序列，事务的主要特性体现以下四个方面：

（1）原子性（Atomicity）

        事务是数据库操作的逻辑工作单位。就操作而言，事务中的操作是一个整体，不能再被分割，要么全部成功执行，要么全部不成功执行。

（2）一致性（Consistency）

       事务的一致性是指出事务执行前后都能够保持数据库状态的一致性，即事务的执行结果是将数据库从一个一致状态转变为另一个一致状态。

Ø  实际上，事务的一致性和原子性是密切相关的。

Ø  对于前面转帐的例子，当操作操作③被执行后，出于某种客观原因而导致操作④不能被执行时，如果操作③和④都是同一个事务中的操作，那么由于事务具有原子性，所以操作①、②和③执行的结果也自动被取消，这样数据库就回到执行操作①前的状态，从而保持数据库的一致性。

Ø  数据库的一致性状态除了取决于事务的一致性以外，还要求在事务开始执行时的数据库状态也必须一致的。否则就算事务具有一致性，但在执行该事务后并不一定能够保持数据库状态的一致性。

（3）隔离性（Isolation）

        隔离性是指多个事务在执行时不相互干扰的一种特性。事务的隔离性意味着一个事务的内部操作及其使用的数据对其他事务是不透明的，其他事务感觉不到这些操作和数据的存在，更不会干扰这些操作和数据。也就是说，事务的隔离性使系统中的每个事务都感觉到“只有自己在工作”，而感觉不到系统中还有其他事务在并发执行，

（4）持久性（Durability）

        持久性或称永久性（Permanence），是指一个事务一旦成功提交，其结果对数据库的改变将是永久的，即使是出现系统故障等问题。

事务的这四个特性通常被称为事务的ACID特性。一个数据库管理系统及其并发控制机制应该能确保这些特性不遭到破坏。

 

 

•    10.2 事务的管理

 

 

10.2.1 启动事务

Ø 启动事务方式有三种：显式启动、自动提交和隐式启动。

1. 显式启动

       显式启动是以BEGIN TRANSACTION命令开始的，即当执行到该语句的时SQL Server将认为这是一个事务的起点。

        BEGIN TRANSACTION的语法如下：

   BEGIN { TRAN | TRANSACTION }

       [ { transaction_name | @tran_name_variable }

      [ WITH MARK [ 'description' ] ]

    ]

   [ ; ]

 

u 其参数意义如下：

Ø transaction_name | @tran_name_variable

     指定事务的名称，可以用变量提供名称。该项是可选项。如果是事务是嵌套的，则仅在最外面的BEGIN...COMMIT或BEGIN...ROLLBACK嵌套语句对中使用事务名。

Ø WITH MARK [ 'description' ]

      指定在日志中标记事务。description 是描述该标记的字符串。如果使用了WITH MARK，则必须指定事务名。WITH MARK允许将事务日志还原到命名标记。

     显式启动的事务通常称为显式事务。本章介绍的主要是显式事务。

 

 

2. 自动提交

Ø 自动提交是指用户每发出一条SQL语句，SQL Server会自动启动一个事务，语句执行完了以后SQL Server自动执行提交操作来提交该事务。也就是说，在自动提交方式下，每一条SQL语句就是一个事务，通常称为自动提交事务，这是SQL Server的默认模式。

Ø  CREATE TABLE语句是一个事务，因此不可能出现这样的情况：在执行该语句时，有的字段被创建而有的没有被创建。

 

 

3. 隐式启动

Ø 当将SIMPLICIT_TRANSACTIONS设置为ON时，表示将隐式事务模式设置为打开，设置语句如下：

SET IMPLICIT_TRANSACTIONS ON;

 

Ø 在隐式事务模式下，任何DML语句（DELETE、UPDATE、INSERT）都自动启动一个事务，直到遇到事务提交语句或事务回滚语句，该事务才结束。结束后，自动启动新的事务，而无需用BEGIN TRANSACTION描述事务的开始。隐式启动的事务通常称为隐性事务。在隐性事务模式生下，事务会形成连续的事务链。

Ø 如果已将IMPLICIT_TRANSACTIONS设置为ON，建议随时将之设置回OFF。另外，事务的结束是使用COMMIT或ROLLBACK语句来实现，这将在下一节介绍。

 

 

10.2.2 终止事务

Ø 有启动，就必有终止。

Ø 终止方法有两种，一种是使用COMMIT命令（提交命令），另一种是使用ROLLBACK命令（回滚命令）。这两种方法有本质上的区别：当执行到COMMIT命令时，会将语句执行的结果保存到数据库中（提交事务），并终止事务；当执行到ROLLBACK命令时，数据库将返回到事务开始时的初始状态，并终止事务。如果ROLLBACK命令是采用ROLLBACK TRANSACTION savepoint_name时，则数据库将返回到savepoint_name标识的状态。

 

 

1. 提交事务——COMMIT TRANSACTION

Ø 执行COMMIT TRANSACTION语句时，将终止隐式启动或显式启动的事务。

ü 如果@@TRANCOUNT为1，COMMIT TRANSACTION使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，释放事务所占用的资源，并将@@TRANCOUNT减少到0。

ü 如果@@TRANCOUNT大于1，则COMMIT TRANSACTION使@@TRANCOUNT按1递减并且事务将保持活动状态。

Ø COMMIT TRANSACTION语句的语法如下：

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

[ ; ]

 

 

      其中，transaction_name | @tran_name_variable用于设置要结束的事务的名称（该名称是由BEGIN TRANSACTION语句指定），但SQL Server会忽略此参数，设置它的目的是给程序员看的，向程序员指明COMMIT TRANSACTION与哪些BEGIN TRANSACTION相关联，以提高代码的可读性。

 

 

【例10.1】创建关于银行转帐的事务。

Ø 假设用UserTable表保存银行客户信息，该表的定义代码如下：

CREATE TABLE UserTable

(

    UserId           varchar(18)       PRIMARY KEY,            --身份证号

    username          varchar(20)    NOT NULL,                       --用户名

    account              varchar(20)     NOT NULL UNIQUE,                         --帐号

    balance          float           DEFAULT  0,          --余额

    address          varchar(100)                     --地址

);

Ø 用下面两条语句分别添加两条用户记录：

INSERT INTO UserTable VALUES('430302x1','王伟志','020000y1',10000,'中关村南路');

INSERT INTO UserTable VALUES('430302x2','张宇','020000y2',100,'火器营桥');

u 现在将账户020000y1上的2000元转到账户430302x2上。为了使得不出现前面所述的情况（转出帐号上已经被扣钱，但转入帐号上的余额并没有增加），我们把转帐操作涉及的关键语句放到一个事务中，这样就可以避免出现上述错误情况。下面代码是对转帐操作的一个简化模拟：

BEGIN TRANSACTION virement            -- 显式启动事务
DECLARE @balance float,@x float;
-- ①设置转帐金额
SET @x = 200;
-- ②如果转出帐号上的金额小于x,则取消转帐操作
SELECT @balance = balance FROM  UserTable WHERE account = '020000y1';
IF(@balance < @x) return;
-- 否则执行下列操作
-- ③从转出帐号上扣除金额x
UPDATE UserTable SET balance = balance - @x WHERE account = '020000y1';
-- ④在转入帐号上加上金额x
UPDATE UserTable SET balance = balance + @x WHERE account = '020000y2';
-- ⑤转帐操作结束
GO
COMMIT TRANSACTION virement;         -- 提交事务，事务终止

Ø  利用以上启动的事务，操作③和操作④要么都对数据库产生影响，要么对数据库都不产生影响，从而避免了“转出帐号上已经被扣钱，但转入帐号上的余额并没有增加”的情况。实际上，只是需要将操作③和操作④对应的语句放在BEGIN TRANSACTION …COMMIT TRANSACTION即可。

Ø  有时候DML语句执行失败并不一定是由硬件故障等外部因素造成的，也有可能是由内部运行错误（如违反约束等）造成的，从而导致相应的DML语句执行失败。

Ø  如果在一个事务中，既有成功执行的DML语句，也有因内部错误而导致失败执行的DML语句，那么该事务会自动回滚吗？

    一般来说，执行SQL语句产生运行时错误时，SQL Server只回滚产生错误的SQL语句，而不会回滚整个事务。如果希望当遇到某一个SQL 语句产生运行时错误时，事务能够自动回滚整个事务，则SET XACT_ABORT选项设置为ON（默认值为OFF）：SET XACT_ABORT ON

ü 即当SET XACT_ABORT为ON时，如果执行SQL语句产生运行时错误，则整个事务将终止并回滚；

ü 当SET XACT_ABORT为OFF时，有时只回滚产生错误的SQL语句，而事务将继续进行处理。

ü 如果错误很严重，那么即使SET XACT_ABORT为OFF，也可能回滚整个事务。OFF 是默认设置。

Ø  注意，编译错误（如语法错误）不受SET XACT_ABORT的影响。

 

 

【例10.2】回滚包含运行时错误的事务。

Ø 先观察下列代码：

USE MyDatabase;

GO

CREATE TABLE TestTransTable1(c1 char(3) NOT NULL, c2 char(3));

GO

BEGIN TRAN 

   INSERT INTO TestTransTable1 VALUES('aa1','aa2');       

   INSERT INTO TestTransTable1 VALUES(NULL,'bb2');   -- 违反非空约束

   INSERT INTO TestTransTable1 VALUES('cc1','cc2');

COMMIT TRAN; 

 

 

Ø 上述代码的作用是：

  （1）先创建表TestTransTable1，其中字段c1有非空约束；

  （2）创建了一个事务，其中包含三条INSERT语句，用于向表TestTransTable1插入数据。

 

Ø 第二条INSER语句违反了非空约束。根据事务的概念，于是许多读者可能会得到这样的结论：由于第二条INSERT语句违反非空约束，因此该语句执行失败，从而导致整个事务被回滚，使得所有的INSERT语句都不被执行，数据库回到事务开始时的状态——表TestTransTable1仍然为空。

 

 

Ø  但实际情况并不是这样。我们使用SELECT语句查看表TestTransTable1：

SELECT * FROM TestTransTable1;

Ø  结果如图10.2所示。

 

 

 

 

 

 

 

Ø  图10.2表明，只有第二条记录没有被插入，第一和第三条都被成功插入了，可见事务并没有产生回滚。但如果将XACT_ABORT设置为ON，当出现违反非空约束而导致语句执行失败时，整个事务将被回滚。

 

 

 【例子】执行下列代码：

USE MyDatabase;

GO

SET XACT_ABORT ON;     -- 将XACT_ABORT设置为ON  xact_abort

GO

DROP TABLE TestTransTable1;

GO

CREATE TABLE TestTransTable1(c1 char(3) NOT NULL, c2 char(3));

GO

BEGIN TRAN 

   INSERT INTO TestTransTable1 VALUES('aa1','aa2');       

   INSERT INTO TestTransTable1 VALUES(NULL,'bb2');   -- 违反非空约束

   INSERT INTO TestTransTable1 VALUES('cc1','cc2');

COMMIT TRAN;

SET XACT_ABORT OFF;     -- 将XACT_ABORT改回默认设置OFF

GO

 

Ø然后用SELECT语句查询表TestTransTable1，结果发现，表TestTransTable1中并没有数据。这说明，上述事务已经被回滚。

Ø类似地，例10.1也有同样的问题。比如，如果用CHECK将字段balance设置在一定的范围内，那么余额超出这个范围时会违反这个CHECK约束。但定义的事务virement在出现违反约束情况下却无法保证数据的一致性。显然，通过将XACT_ABORT设置为ON，这个问题就可以得到解决。

 

 

 

2. 回滚事务——ROLLBACK TRANSACTION

        回滚事务是利用ROLLBACK TRANSACTION语句来实现，它可以将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点（savepoint）。该语句的语法如下： 

ROLLBACK { TRAN | TRANSACTION }

     [ transaction_name | @tran_name_variable

     | savepoint_name | @savepoint_variable ]

[ ; ] 

 

Ø  transaction_name | @tran_name_variable 

      该参数用于指定由BEGIN TRANSACTION语句分配的事务的名称。嵌套事务时，transaction_name 必须是最外面的BEGIN TRANSACTION语句中的名称。

 

 

Ø savepoint_name | @savepoint_variable

        该参数为SAVE TRANSACTION语句中指定的保存点。指定了该参数，则回滚时数据库将恢复到该保存点时的状态（而不是事务开始时的状态）。不带savepoint_name和transaction_name的ROLLBACK TRANSACTION语句将使事务回滚到起点。

Ø  根据在ROLLBACK TRANSACTION语句中是否使用保存点，可以将回滚分为全部回滚和部分回滚。

    （1）全部回滚

 

【例10.3】全部回滚事务。

       下面代码先定义表TestTransTable2，然后在事务myTrans1中执行三条插入语句，事务结束时用ROLLBACK TRANSACTION语句全部回滚事务，之后又执行两条插入语句，以观察全部回滚事务的效果。代码如下：

USE MyDatabase;

GO

CREATE TABLE TestTransTable2(c1 char(3), c2 char(3));

GO

DECLARE @TransactionName varchar(20) = 'myTrans1';

BEGIN TRAN @TransactionName

    INSERT INTO TestTransTable2 VALUES('aa1','aa2’);

    INSERT INTO TestTransTable2 VALUES('bb1','bb2’);

    INSERT INTO TestTransTable2 VALUES('cc1','cc2');

ROLLBACK TRAN @TransactionName  -- 回滚事务

INSERT INTO TestTransTable2 VALUES('dd1','dd2');

INSERT INTO TestTransTable2 VALUES('ee1','ee2');

SELECT * FROM TestTransTable2

 

 

     执行上述代码，结果如图10.3所示

 

Ø  以上可以看到，事务myTrans1中包含的三条插入语句并没有实现将相应的三条数据记录插入到表TestTransTable2中，

     原因：在于ROLLBACK TRAN语句对整个事务进行全部回滚，使得数据库回到执行这三条插入语句之前的状态。事务myTrans1之后又执行了两条插入语句，这时是处于事务自动提交模式（每一条SQL语句就是一个事务，并且这种事务结束后会自动提交，而没有回滚）下，因此这两条插入语句成功地将两条数据记录插入到数据库中。

Ø  根据ROLLBACK的语法，在本例中，BEGIN TRAN及其ROLLBACK TRAN后面的@TransactionName可以省略，其效果是一样的。

 

 

 

（2）部分回滚

Ø 如果在事务中设置了保存点（即ROLLBACK TRANSACTION语句带参数savepoint_name | @savepoint_variable）时，ROLLBACK TRANSACTION语句将回滚到由savepoint_name或@savepoint_variable指定的保存点上。

Ø 在事务内设置保存点是使用SAVE TRANSACTION语句来实现，其语法如下：

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

[ ; ]

 

 

savepoint_name | @savepoint_variable是保存点的名称，必须指定。

 

 

【例10.4】部分回滚事务。

     在例10.3所定义的事务中利用SAVE TRANSACTION语句增加一个保存点save1，同时修改ROLLBACK语句，其他代码相同。所有代码如下：

USE MyDatabase;

GO

DROP TABLE TestTransTable2;

CREATE TABLE TestTransTable2(c1 char(3), c2 char(3));

GO

DECLARE @TransactionName varchar(20) = 'myTrans1';

BEGIN TRAN @TransactionName

INSERT INTO TestTransTable2 VALUES('aa1','aa2');

    INSERT INTO TestTransTable2 VALUES('bb1','bb2');

    SAVE TRANSACTION save1;          -- 设置保存点

    INSERT INTO TestTransTable2 VALUES('cc1','cc2');         

ROLLBACK TRAN save1;   

INSERT INTO TestTransTable2 VALUES('dd1','dd2');

INSERT INTO TestTransTable2 VALUES('ee1','ee2');

SELECT * FROM TestTransTable2

 

 

 

 

      执行结果如图10.4所示。

 

 

 

    

    此结果表明，只有第三条插入语句的执行结果被撤销了。其原因在于，事务myTrans1结束时ROLLBACK TRAN语句回滚保存点save1处，即回滚到第三条插入语句执行之前，故第三条插入语句的执行结果被撤销，其他插入语句的执行结果是有效的。

 

10.2.3 嵌套事务

Ø 事务是允许嵌套的，即一个事务内可以包含另外一个事务。当事务嵌套时，就存在多个事务同时处于活动状态。

 

Ø 系统全局变量@@TRANCOUNT可返回当前连接的活动事务的个数。对@@TRANCOUNT返回值有影响的是BEGIN TRANSACTION、ROLLBACK TRANSACTION和COMMIT语句。具体影响方式如下：

ü 每执行一次BEGIN TRANSACTION命令就会使@@TRANCOUNT的值增加1；

ü 每执行一次COMMIT命令时，@@TRANCOUNT的值就减1；

ü 一旦执行到ROLLBACK TRANSACTION命令（全部回滚）时，@@TRANCOUNT的值将变为0；

ü 但ROLLBACK TRANSACTION savepoint_name（部分回滚）不影响@@TRANCOUNT的值。

 

 

【例10.5】嵌套事务。

       本例中，先创建表TestTransTable3，然后在有三个嵌套层的嵌套事务中向该表插入数据，并在每次启动或提交一个事务时都打印@@TRANCOUNT的值。代码如下：

USE MyDatabase;

GO

CREATE TABLE TestTransTable3(c1 char(3), c2 char(3));

GO

if(@@TRANCOUNT!=0) ROLLBACK TRAN;  -- 先终止所有事务

BEGIN TRAN Trans1

      PRINT '启动事务Trans1后@@TRANCOUNT的值：'+CAST(@@TRANCOUNT AS VARCHAR(10));

       INSERT INTO TestTransTable3 VALUES('aa1','aa2’);

       BEGIN TRAN Trans2

    PRINT '启动事务Trans2后@@TRANCOUNT的值：'+CAST(@@TRANCOUNT AS VARCHAR(10));

   INSERT INTO TestTransTable3 VALUES('bb1','bb2');

      BEGIN TRAN Trans3

            PRINT '启动事务Trans3后@@TRANCOUNT的值：'+CAST(@@TRANCOUNT AS VARCHAR(10));

            INSERT INTO TestTransTable3 VALUES('cc1','cc2');        

            SAVE TRANSACTION save1;         -- 设置保存点

            PRINT '设置保存点save1后@@TRANCOUNT的值：'+CAST(@@TRANCOUNT AS VARCHAR(10));

            INSERT INTO TestTransTable3 VALUES('dd1','dd2');         

            ROLLBACK TRAN save1;

            PRINT '回滚到保存点save1后@@TRANCOUNT的值：'+CAST(@@TRANCOUNT AS VARCHAR(10));  

           INSERT INTO TestTransTable3 VALUES('ee1','ee2');

       COMMIT TRAN Trans3

       PRINT '提交Trans3后@@TRANCOUNT的值：'+CAST(@@TRANCOUNT AS VARCHAR(10));

       INSERT INTO TestTransTable3 VALUES('ff1','ff2’);

              COMMIT TRAN Trans2

              PRINT '提交Trans2后@@TRANCOUNT的值：'+CAST(@@TRANCOUNT AS VARCHAR(10));

     COMMIT TRAN Trans1

     PRINT '提交Trans1后@@TRANCOUNT的值：'+CAST(@@TRANCOUNT AS VARCHAR(10));

 

 

 

Ø  执行上述代码，结果如图13.5所示。

 

 

 

 

Ø  从图10.5中也可以可以看出，每执行一次BEGIN TRANSACTION命令就会使@@TRANCOUNT的值增加1，每执行一次COMMIT命令时，@@TRANCOUNT的值就减1，但ROLLBACK TRANSACTION savepoint_name不影响@@TRANCOUNT的值。

 

Ø  如果遇到ROLLBACK TRANSACTION命令，不管该命令之后是否还有其他的COMMIT命令，系统中所有的事务都被终止（不提交），@@TRANCOUNT的值为0。

Ø  执行上述嵌套事务后，表TestTransTable3中的数据如图10.6所示。

 

 

 

Ø  如果将上述代码中的语句COMMIT TRAN Trans1（倒数第二条）改为ROLLBACK TRAN（不带参数），则表TestTransTable3中将没有任何数据。这说明，对于嵌套事务，不管内层是否使用COMMIT命令来提交事务，只要外层事务中使用ROLLBACK TRAN来回滚，那么整个嵌套事务都被回滚，数据库将回到嵌套事务开始时的状态。

 

•    10.3 并发控制

 

10.3.1 并发控制的概念

Ø 数据共享是数据库的基本功能之一。一个数据库可能同时拥有多个用户，这意味着在同一时刻系统中可能同时运行上百上千个事务。而每个事务又是由若干个数据库操作构成的操作序列，如何有效地控制这些操作的执行对提高系统的安全性和运行效率有着十分重要的意义。

Ø 在单CPU系统中，事务的运行有两种方式，一种是串行执行，一种是并发执行。串行执行是指每个时刻系统中只有一个事务在运行，其他事务必须等到该事务中所有的操作执行完了以后才能运行。这种执行方式的优点是方便控制，但其缺点却是十分突出，那就是整个系统的运行效率很低。因为在串行方式中，不同的操作需要不同的资源，但一个操作一般不会使用所有的资源且使用时间长短不一，所以串行执行的事务会使许多系统资源处于空闲状态。

Ø  如果能够充分利用这些空闲的资源，无疑可以有效提高系统的运行效率，这是考虑事务并发控制的主要原因之一。另外，并发控制可以更好保证数据的一致性，从而实现数据的安全性。

Ø  在并发执行方式中，系统允许同一个时刻有多个事务在并行执行。这种并行执行实际上是通过事务操作的轮流交叉执行来实现的。虽然在同一时刻只有某一个事务的某一个操作在占用CPU资源，但其他事务中的操作可以使用该操作没有占用的有关资源，这样可以在总体上提高系统的运行效率。

Ø  对于并发运行的事务，如果没有有效地控制其操作，就可能导致对资源的不合理使用，对数据库而言就可能导致数据的不一致性和不完整性等问题。因此，DBMS必须提供一种允许多个用户同时对数据进行存取访问的并发控制机制，以确保数据库的一致性和完整性。

Ø  简而言之，并发控制就是针对并发执行的事务，如何有效地控制和调度其交叉执行的数据库操作，使各事务的执行不相互干扰，以避免出现数据库的不一致性和不完整性等问题。

 

 

10.3.2 几种并发问题

        当多个用户同时访问数据库时，如果没有必要的访问控制措施，可能会引发数据不一致等并发问题，这是诱发并发控制的主要原因。为进行有效的并发控制，首先要明确并发问题的类型，分析不一致问题产生的根源。

1. 丢失修改（Lost Update）

       下面看一个经典的关于民航订票系统的例子。它可以说明多个事务对数据库的并发操作带来的不一致性问题。

【例子】假设某个民航订票系统有两个售票点，分别为售票点A和售票点B。假设系统把一次订票业务定义为一个事务，其包含的数据库操作序列如下：

T：Begin Transaction

     读取机票余数x;

     售出机票y张，机票余数x ← x – y;

     把x写回数据库，修改数据库中机票的余数;

Commit;

 

 

Ø  假设当前机票余数为10张，售票点A和售票点B同时进行一次订票业务，分别有用户订4张和3张机票。于是在系统中同时形成两个事务，分别记为TA和TB。如果事务TA和TB中的操作交叉执行，执行过程如图10.7所示。

 

 

 

 

 

 

 

 

 

 

 

 

 

       事务TA和TB执行完了以后，由于B_op3是最后的操作，所以数据库中机票的余数6。而实际情况是，售票点A售出4张，售票点B售出3张，所以实际剩下10-(4+3) = 3张机票。这就造成了数据库反映的信息与实际情况不符，从而产生了数据的不一致性。这种不一致性是由操作B_op3的（对数据库的）修改结果将操作A_op3的修改结果覆盖掉而产生的，即A_op3的修改结果“丢了”，所以称为丢失修改。

 

2. 读“脏”数据（Dirty Read）

Ø 事务TC对某一数据处理了以后将结果写回到数据区，然后事务TD从数据区中读取该数据。但事务TC出于某种原因进行回滚操作，撤消已做出的操作，这时TD刚读取的数据又被恢复到原值（事务TC开始执行时的值），这样TD读到的数据就与数据库中的实际数据不一致了，而TD读取的数据就是所谓的“脏”数据（不正确的数据）。“脏”数据是指那些被某事务更改、但还没有被提交的数据。

 

 

   【例子】  在订票系统中，事务TC在读出机票余数10并售出4张票后，将机票余数10-4=6写到数据区（还没来得及提交），恰在此时事务TD读取机票余数6，而TC出于某种原因（如断电等）进行回滚操作，机票余数恢复到了原来的值10并撤销此次售票操作，但这时事务TD仍然使用着读到的机票余数6，这与数据库中实际的机票余数不一致，这个“机票余数6”就是所谓的“脏”数据，如图10.8所示。

 

 

 

 

 

3. 不可重复读（Non-Repeatable Read）

Ø  事务TE按照一定条件读取数据库中某数据x，随后事务TF又修改了数据x，这样当事务TE操作完了以后又按照相同条件读取数据x，但这时由于数据x已经被修改，所以这次读取值与上一次不一致，从而在进行同样的操作后却得到不一样的结果。由于另一个事务对数据的修改而导致当前事务两次读到的数据不一致，这种情况就是不可重复读。这与读“脏”数据有相似之处。

   【例子】  在图10.9中c代表机票的价格，n代表机票的张数。机票查询事务TE读取机票价格c = 800和机票张数n = 7，接着计算这7张票的总价钱5600（可能有人想查询7张机票总共需要多少钱）；恰好在计算总价钱完后，管理事务TF（相关航空公司执行）读取c = 800并进行六五折降价处理后将c = 520写回数据库；这时机票查询事务TE重读c（可能为验证总价钱的正确性），结果得到c=520，这与第一次读取值不一致。显然，这种不一致性会导致系统给出错误的信息，这是不允许的。

 

 

 

 

4. 幻影读（Phantom Row）

Ø 假设事务TG按照一定条件两次读取表中的某些数据记录，在第一次读取数据记录后事务TH在该表中删除（或添加）某些记录。这样在事务TG第二次按照同样条件读取数据记录时会发现有些记录“幻影”般地消失（或增多）了，这称为幻影（Phantom Row）读。

Ø 导致以上四种不一致性产生的原因是并发操作的随机调度，这使事务的隔离性遭到破坏。为此，需要采取相应措施，对所有数据库操作的执行次序进行合理而有效的安排，使得各个事务都能够独立地运行、彼此不相互干扰，保证事务的ACID特性，避免出现数据不一致性等并发问题。

 

 

10.3.3 基于事务隔离级别的并发控制

Ø 保证事务的隔离性可以有效防止数据不一致等并发问题。事务的隔离性有程度之别，这就是事务隔离级别。在SQL Server中，事务的隔离级别用于表征一个事务与其他事务进行隔离的程度。隔离级别越高，就可以更好地保证数据的正确性，但并发程度和效率就越低；相反，隔离级别越低，出现数据不一致性的可能性就越大，但其并发程度和效率就越高。通过设定不同事务隔离级别可以实现不同层次的访问控制需求。

 

 

Ø 在SQL Server中，事务隔离级别分为四种：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE，它们对数据访问的限制程度依次从低到高。设置隔离级别是通过SET TRANSACTION ISOLATION LEVEL语句来实现，其语法如下：

ET TRANSACTION ISOLATION LEVEL

    { READ UNCOMMITTED

    | READ COMMITTED

    | REPEATABLE READ

    | SERIALIZABLE

    }

[ ; ]

 

 

 

 

 

1. 使用READ UNCOMMITTED

Ø 该隔离级别允许读取已经被其他事务修改过但尚未提交的数据，实际上该隔离级别根本就没有提供事务间的隔离。这种隔离级别是四种隔离级别中限制最少的一种，级别最低。

Ø 其作用可简记为：允许读取未提交数据。

  【例10.6】使用READ UNCOMMITTED隔离级别，允许丢失修改。

    当事务的隔离级别设置为READ UNCOMMITTED时，SQL Server允许用户读取未提交的数据，因此会造成丢失修改。为观察这种效果，按序完成下列步骤：

（1）创建表TestTransTable4并插入两条数据：

CREATE TABLE TestTransTable4(flight char(4), price float, number int);

INSERT INTO TestTransTable4 VALUES('A111',800,10);

INSERT INTO TestTransTable4 VALUES('A222',1200,20);

 

     其中，flight、price、number分别代表航班号、机票价格、剩余票数。

 

 

（2）编写事务TA和TB的代码：

-- 事务TA的代码

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置事务隔离级别

BEGIN TRAN TA

      DECLARE @n int;

      SELECT @n = number FROM TestTransTable4 WHERE flight = 'A111’;

      WAITFOR DELAY '00:00:10'              -- 等待事务TB读数据

      SET @n = @n - 4;

      UPDATE TestTransTable4 SET number = @n WHERE flight = 'A111';

COMMIT TRAN TA

 

-- 事务TB的代码

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRAN TB

     DECLARE @n int;

     SELECT @n = number FROM TestTransTable4 WHERE flight = 'A111’;

     WAITFOR DELAY '00:00:15'         -- 等待，以让事务TA先提交数据

     SET @n = @n - 3;

    UPDATE TestTransTable4 SET number = @n WHERE flight = 'A111';

COMMIT TRAN  TB

 

 

 

（3）打开两个查询窗口，分别在两个窗口中先后执行事务TA和TB（执行TA后应该在10秒以内执行TB，否则看不到预设的结果），分别如图10.10和图10.11所示。

 

 

 

 

 

 

（4）查询表中的数据：

          SELECT * FROM TestTransTable4;

          结果如图10.12所示。

 

 

 

 

        由代码可知，事务TA和TB分别售出了4张和3张票，因此应该剩下10-(4+3) = 3张票。但由图10.12可以看到，系统还剩下7张票。这就是丢失修改的结果。当隔离级别为READ UNCOMMITTED时，事务不能防止丢失修改。

        实际上，对于前面介绍的四种数据不一致情况，READ UNCOMMITTED隔离级别都不能防止它们。这是READ UNCOMMITTED隔离级别的缺点。其优点是可避免并发控制所需增加的系统开销，一般用于单用户系统（不适用于并发场合）或者系统中两个事务同时访问同一资源的可能性为零或几乎为零。

 

 

2. 使用READ COMMITTED

Ø 在使用该隔离级别时，当一个事务已经对一个数据块进行了修改（UPDATE）但尚未提交或回滚时，其他事务不允许读取该数据块，即该隔离级别不允许读取未提交的数据。它的隔离级别比READ UNCOMMITTED高一层，可以防止读“脏”，但不能防止丢失修改，也不能防止不可重复读和“幻影”读。 

Ø 其作用可简记为：不允许读取已修改但未提交数据。

Ø READ COMMITTED是SQL Server默认的事务隔离级别。

  【例10.7】使用READ COMMITTED隔离级别，防止读“脏”数据。

     先恢复表TestTransTable4中的数据：

DELETE FROM TestTransTable4;

INSERT INTO TestTransTable4 VALUES('A111',800,10);

INSERT INTO TestTransTable4 VALUES('A222',1200,20);

 

 

Ø  为观察读“脏”数据，先将事务的隔离级别设置为READ UNCOMMITTED，分别在两个查询窗口中先后执行事务TC和TD：

-- 事务TC的代码
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置事务隔离级别
BEGIN TRAN TC
    DECLARE @n int;
    SELECT @n = number FROM TestTransTable4 WHERE flight = 'A111’;
    SET @n = @n - 4;
    UPDATE TestTransTable4 SET number = @n WHERE flight = 'A111’;
    WAITFOR DELAY '00:00:10'             -- 等待事务TD读“脏”数据
ROLLBACK TRAN TC                 -- 回滚事务

 

 

 

 

-- 事务TD的代码

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRAN TD

     DECLARE @n int;

     SELECT @n = number FROM TestTransTable4 WHERE flight = 'A111'; -- 读“脏”数据

     PRINT '剩余机票数：'+CONVERT(varchar(10),@n);

COMMIT TRAN    TD

 

Ø  结果事务TD输出如下的结果：     剩余机票数：6

Ø  在等待事务TC执行完了以后，利用SELECT语句查询表TestTransTable4，结果发现剩余机票数为10。6就是事务TD读到的“脏”数据。

Ø  为了避免读到这个“脏”数据，只需将上述的隔离级别由READ UNCOMMITTED改为READ COMMITTED即可（其他代码不变）。但在将隔离级别更改了以后，我们发现事务TD要等事务TC回滚了以后（ROLLBACK）才执行读操作。READ COMMITTED虽然可以比READ UNCOMMITTED具有更好解决并发问题的能力，但是其效率较后者低。

 

 

3. 使用REPEATABLE READ

Ø 在该隔离级别下，如果一个数据块已经被一个事务读取但尚未作提交操作，则任何其他事务都不能修改（UPDATE）该数据块（但可以执行INSERT和DELETE），直到该事务提交或回滚后才能修改。该隔离级别的层次又在READ COMMITTED之上，即比READ COMMITTED有更多的限制，

Ø 它可以防止读“脏”数据和不可重复读。但由于一个事务读取数据块后另一个事务可以执行INSERT和DELETE操作，所以它不能防止“幻影”读。另外，该隔离级别容易造成死锁。例如，将它用于解决例10.6中的丢失修改问题时，就造成死锁。

Ø 其作用可简记为：不允许读取未提交数据，不允许修改已读数据。

 

 

【例10.8】使用REPEATABLE READ隔离级别，防止不可重复读。

Ø 先看看存在不可重复读的事务TE： 

-- 事务TE的代码

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 设置事务隔离级别

BEGIN TRAN TE

       DECLARE @n int, @c int;

       -- 顾客先查询张机票的价格

      SELECT @c = price FROM TestTransTable4 WHERE flight = 'A111';  -- 第一次读

      SET @n = 7;

      PRINT CONVERT(varchar(10),@n)+'张机票的价格：'+CONVERT(varchar(10),@n*@c)+'元’;

      WAITFOR DELAY '00:00:10'   -- 为观察效果，让该事务等待10秒

      -- 接着购买张机票

      SELECT @c = price FROM TestTransTable4 WHERE flight = 'A111';  -- 第二次读

      SET @n = 7;

        PRINT '总共'+CONVERT(varchar(10),@n)+'张机票，应付款：'+CONVERT(varchar(10),@n*@c)+'元';

COMMIT TRAN TE -- 提交事务

 

Ø  另一事务TF的代码如下：

-- 事务TF的代码

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 设置事务隔离级别

BEGIN TRAN TF

      UPDATE TestTransTable4 SET price = price*0.65 WHERE flight = 'A111'; -- 折价65折

COMMIT TRAN TF

 

 

Ø  分别在两个查询窗口中先后运行事务TE和事务TF（时间间隔要小于10秒），事务TE输出的结果如图10.13所示。

 

 

 

 

 

 

Ø  该结果说了事务TE出现了不可重复读：在相同条件下，利用两次读取的信息来计算的机票价格却不一样。原因在于，当事务TE处于10秒等待期时，事务TF对机票价格（price）进行六五折处理，结果导致了在同一事务中的两次读取操作获得不同的结果。

Ø  如果将事务隔离级别由原来的READ COMMITTED改为REPEATABLE READ（其他代码不变），则就可以防止上述的不可重复读，如图10.14所示。这是因为REPEATABLE READ隔离级别不允许对事务TE已经读取的数据（价格）进行任何的更新操作，这样事务TF只能等待事务TE结束后才能对价格进行五六折处理，从而避免不可重复读问题。显然，由于出现事务TF等待事务TE的情况，因此使用REPEATABLE READ隔离级别时要比使用READ COMMITTED的效率低。

 

4. 使用SERIALIZABLE

Ø SERIALIZABLE是SQL Server最高的隔离级别。在该隔离级别下，一个数据块一旦被一个事务读取或修改，则不允许别的事务对这些数据进行更新操作（包括UPDATE, INSERT, DELETE），直到该事务提交或回滚。也就是说，一旦一个数据块被一个事务锁定，则其他事务如果需要修改此数据块，它们只能排队等待。SERIALIZABLE隔离级别的这些性质决定了它能够解决“幻影”读问题。

Ø 其作用可简记为：事务必须串行执行。

 

 

【例10.9】使用SERIALIZABLE隔离级别，防止“幻影”读。

Ø 先看看存在“幻影”读的事务TG：

-- 事务TG的代码

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ         -- 设置事务隔离级别 

BEGIN TRAN TG

     SELECT * FROM TestTransTable4 WHERE price <= 1200;    -- 第一次读

     WAITFOR DELAY '00:00:10'                      -- 事务等待10秒

     SELECT * FROM TestTransTable4 WHERE price <= 1200;   -- 第二次读

COMMIT TRAN TG -- 提交事务

 

Ø 构造另一事务TH：

-- 事务TH的代码

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 设置事务隔离级别

BEGIN TRAN TH

     INSERT INTO TestTransTable4 VALUES('A333',1000,20);

COMMIT TRAN TH

 

 

 

Ø  分别在两个查询窗口中先后运行事务TG和事务TH（时间间隔要小于10秒，且先恢复表TestTransTable4中的数据），事务TG中的两条SELECT语句输出的结果分别如图10.15和图10.16所示：

 

 

 

 

 

 

 

Ø  在事务TG中完全相同的两个查询语句在两次执行后得到的结果不一样，其中在第二次查询结果中“幻影”般地增加了一个票价为1000元的航班信息。可见，REPEATABLE READ隔离级别虽然比前二者均高，但还是不能防止“幻影”读。

 

Ø  如果将事务隔离级别由原来的REPEATABLE READ改为SERIALIZABLE（其他代码不变），按照上述同样方法执行这两个事务后，事务TG中的两次查询得到的结果均如图10.15所示。这表明“幻影”读已经不复存在了，隔离级别SERIALIZABLE可以防止上述的“幻影”读。如果这时进一步查询表TestTransTable4中的数据，可以看到其结果与图10.16所示的结果一样。这是因为，在SERIALIZABLE隔离级别下，事务TG执行完了以后再执行事务TH，即串行事务TG和TH，因此事务TH中的语句不会影响到事务TG，从而避免“幻影”读。

Ø  需要说明的是，REPEATABLE READ和SERIALIZABLE隔离级别对系统性能的影响都很大，特别是SERIALIZABLE隔离级别，不是非不得以，最好不要使用。

Ø   根据以上分析，四种隔离级对事务“读”和“写”操作的处理关系说明如表10.1所示。

 

 

 

 

表10.1中，“读”、“写”、“插”和“删”分别指SELECT、UPDATE、INSERT和DELETE操作。
“读了,可再读”表述的意思是，执行了SELECT后，在事务还没有提交或回滚之前，还可以继续执行SELECT；
“读了,不可再写”是指，执行了SELECT后，在事务还没有提交或回滚之前，是不允许执行UPDATE操作的。其他项的意思可以照此类推。

 

Ø  根据表10.1，我们可进一步总结四种隔离级别对支持解决并发问题的情况，结果如表10.2所示。

 

 

                 注：√表示“防止”，×表示“不一定防止”      

严格说，REPEATABLE READ和SERIALIZABLE是不支持解决丢失修改问题的，因为它们用于此类问题时，容易造成死锁。

  【例子】对于例10.6中的事务TA和TB，如果将其中的UNCOMMITTED替换成REPEATABLE READ或SERIALIZABLE，然后按照例10.6中的方法执行这两个事务，结果虽然没有造成数据的不一致，但出现了死锁（死锁最后是由SQL Server自动终止一个事务来解除）。隔离级别的方法并不能完全解决涉及的并发问题。

 

 

 

10.3.4 基于锁的并发控制

Ø 锁定是指对数据块的锁定，是SQL Server数据库引擎用来同步多个用户同时对同一个数据块进行访问的一种控制机制。这种机制的实现是利用锁（LOCK）来完成的。一个用户（事务）可以申请对一个资源加锁，如果申请成功的话，则在该事务访问此资源的时候其他用户对此资源的访问受到诸多的限制，以保证数据的完整性和一致性。

Ø SQL Server提供了多种不同类型的锁。有的锁类型是兼容的，有的是不兼容的。不同类型的锁决定了事务对数据块的访问模式。SQL Serve常用的锁类型主要包括：

（1）共享锁（S）：允许多个事务并发读取同一数据块，但不允许其他事务修改当前事务加锁的数据块。一个事务对一个数据块加上一个共享锁后，其他事务也可以继续对该数据块加上共享锁。这就是说，当一个数据块被多个事务同时加上共享锁的时候，所有的事务都不能对这个数据块进行修改，直到数据读取完成，共享锁释放。

 

 

（2）排它锁（X）：也称独占锁、写锁，当一个事务对一个数据块加上排它锁后，它可以对该数据块进行UPDATE、DELETE、INSERT等操作，而其他事务不能对该数据块加上任何锁，因而也不能执行任何的更新操作（包括UPDATE、DELETE和INSERT）。一般用于对数据块进行更新操作时的并发控制，它可以保证同一数据块不会被多个事务同时进行更新操作，避免由此引发的数据不一致。

（3）更新锁：更新锁介于共享锁和排它锁之间，主要用于数据更新，可以较好地防止死锁。一个数据块的更新锁一次只能分配给一个事务，在读数据的时候该更新锁是共享锁，一旦更新数据时它就变成排他锁，更新完后又变为共享锁。但在变换过程中，可能出现锁等待等问题，且变换本身也需要时间，因此使用这种锁时，效率并不十分理想。

 

 

（4）意向锁：表示SQL Server需要在层次结构中的某些底层资源上（如行，列）获取共享锁、排它锁或更新锁。

     【例子】表级放置了意向共享锁，就表示事务要对表的页或行上使用共享锁；在表的某一行上上放置意向锁，可以防止其它事务获取其它不兼容的锁。意向锁的优点是可以提高性能，因为数据引擎不需要检测资源的每一列每一行，就能判断是否可以获取到该资源的兼容锁。它包括三种类型：意向共享锁，意向排他锁，意向排他共享锁。

（5）架构锁：架构锁用于在修改表结构时，阻止其他事务对表的并发访问。

（6）键范围锁：用于锁定表中记录之间的范围的锁，以防止记录集中的“幻影”插入或删除，确保事务的串行执行。

（7）大容量更新锁：允许多个进程将大容量数据并发的复制到同一个表中，在复制加载的同时，不允许其它非复制进程访问该表。

 

 

 

     在这些锁当中，共享锁（S锁）和排他锁（X锁）尤为重要，它们之间的相容关系描述如下：

Ø 如果事务T对数据块D成功加上共享锁，则其他事务只能对D再加共享锁，不能加排他锁，且此时事务T只能读数据块D，不能修改它（除非其他事务没有对该数据块加共享锁）。

Ø 如果事务T对数据块D成功加上排他锁，则其他事务不能再对D加上任何类型的锁，也对D进行读操作和写操作，而此时事务T既能读数据块D，也又能修改该数据块。

 

Ø  下面主要是结合SQL Server提供的表提示（table_hint），介绍共享锁和排他锁在并发控制中的使用方法。加锁情况的动态信息可以通过查询系统表sys.dm_tran_locks获得。

Ø  通过在SELECT、INSERT、UPDATE及DELETE语句中为单个表引用指定表提示，可以实现对数据块的加锁功能，实现事务对数据访问的并发控制。

Ø  为数据表指定表提示的简化语法如下：

{SELECT … | INSERT … | UPDATE … | DELECT … | MERGE …} [ WITH ( <table_hint> ) ]
<table_hint> ::=
[ NOEXPAND ] {
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FASTFIRSTROW
  | FORCESEEK
  | HOLDLOCK
  | NOLOCK
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

 

 

 

 

u 表提示语法中有很多选项，下面主要介绍与表级锁有密切相关的几个选项：

Ø HOLDLOCK 

   表示使用共享锁，使用共享锁更具有限制性，保持共享锁直到事务完成。而不是无论事务是否完成，都在不再需要所需表或数据页时立即释放共享锁。HOLDLOCK不能被用于包含FOR BROWSE选项的SELECT语句。它同等于SERIALIZABLE隔离级别。

Ø NOLOCK

 表示不发布共享锁来阻止其他事务修改当前事务在读的数据，允许读“脏”数据。它同等于等同于READ UNCOMMITTED隔离级别。

Ø PAGLOCK

表示使用页锁，通常使用在行或键采用单个锁的地方，或者采用单个表锁的地方。

Ø READPAST

    指定数据库引擎跳过（不读取）由其他事务锁定的行。在大多数情况下，这同样适用于页。数据库引擎跳过这些行或页，而不是在释放锁之前阻塞当前事务。它仅适用于READ COMMITTED或REPEATABLE READ隔离级别的事务中。

 

 

Ø  ROWLOCK

     表示使用行锁，通常在采用页锁或表锁时使用。

Ø  TABLOCK

     指定对表采用共享锁并让其一直持有，直至语句结束。如果同时指定了HOLDLOCK，则会一直持有共享表锁，直至事务结束。

Ø  TABLOCKX

    指定对表采用排他锁（独占表级锁）。如果同时指定了HOLDLOCK，则会一直持有该锁，直至事务完成。在整个事务期间，其他事务不能访问该数据表。

Ø  UPDLOCK

   指定要使用更新锁（而不是共享锁），并保持到事务完成。

 

     注意：如果设置了事务隔离级别，同时指定了锁提示，则锁提示将覆盖会话的当前事务隔离级别。

 

 

【例10.10】使用表级共享锁。

Ø 对于数据表TestTransTable4，事务T1对其加上表级共享锁，使得在事务期内其他事务不能更新此数据表。事务T1的代码如下：

BEGIN TRAN T1

     DECLARE @s varchar(10);

     -- 下面一条语句的唯一作用是对表加共享锁

    SELECT @s = flight FROM TestTransTable4 WITH(HOLDLOCK,TABLOCK) WHERE 1=2;

    PRINT '加锁时间：'+CONVERT(varchar(30), GETDATE(), 20);

   WAITFOR DELAY '00:00:10'               -- 事务等待10秒

   PRINT '解锁时间：'+CONVERT(varchar(30), GETDATE(), 20);

COMMIT TRAN T1

Ø 为观察共享锁的效果，进一步定义事务T2：

BEGIN TRAN T2

     UPDATE TestTransTable4 SET price = price*0.65 WHERE flight = 'A111’;

    PRINT '数据更新时间：'+CONVERT(varchar(30), GETDATE(), 20); 

COMMIT TRAN T2

 

 

 

Ø  然后分别在两个查询窗口中先后运行事务T1和事务T2（时间间隔要小于10秒），事务T1和T2输出的结果分别如图10.17和图10.18所示。

 

 

 

 

 

 

 

 

 

Ø  对比图10.17和图10.18，事务T1对表TestTransTable4的更新操作（包括删除和添加）必须等到事务T2解除共享锁以后才能进行（但在事务T1期内，事务T2可以使用SELECT语句查询表TestTransTable4）。

 

Ø  使用HOLDLOCK和TABLOCK可以避免在事务期内被锁定对象受到更新（包括删除和添加），因而可以避免“幻影”读；但由于T1在进行UPDATE操作后，T2能够继续SELECT数据，因此这种控制策略不能防止读“脏”数据；共享锁也不能防止丢失修改。

Ø  如果同时在T1和T2中添加读操作和写操作，则容易造成死锁。

      【例子】如果在例10.6的两个事务TA和TB中改用共享锁进行并发控制，同样会出现死锁的现象。但更新锁能够自动实现在共享锁和排他锁之间的切换，完成对数据的读取和更新，且在防止死锁方面有优势。如果在例10.6的两个事务TA和TB中改用更新锁，结果是可以对这两个事务成功进行并发控制的。

 

【例10.11】利用更新锁解决丢失修改问题。

       对于例10.6的两个事务TA和TB，用事务隔离级别的方法难以解决丢失修改问题，但用更新锁则可以较好地解决这个问题。更新锁是用UPDLOCK选项来定义，修改后事务TA和TB的代码如下：

-- 事务TA的代码

BEGIN TRAN TA 

DECLARE @n int; 

SELECT @n = number FROM TestTransTable4 WITH(UPDLOCK,TABLOCK) WHERE flight = 'A111';

WAITFOR DELAY '00:00:10'         -- 等待10秒，以让事务TB读数据

SET @n = @n - 4;

UPDATE TestTransTable4 SET number = @n WHERE flight = 'A111';

COMMIT TRAN   TA

 

-- 事务TB的代码

BEGIN TRAN TB

DECLARE @n int;

SELECT @n = number FROM TestTransTable4 WITH(UPDLOCK,TABLOCK) WHERE flight = 'A111';

WAITFOR DELAY '00:00:15'           

SET @n = @n - 3;

UPDATE TestTransTable4 SET number = @n WHERE flight = 'A111';

COMMIT TRAN  TB

 

 

 

 

  【例10.12】   利用排他锁来实现事务执行的串行化。

        下面代码是为表TestTransTable4加上表级排他锁（TABLOCKX），并将其作用范围设置为整个事务期：

BEGIN TRAN T3

     DECLARE @s varchar(10);

     -- 下面一条语句的唯一作用是对表加排他锁

     SELECT @s = flight FROM TestTransTable4 WITH(HOLDLOCK,TABLOCKX) WHERE 1=2;

     PRINT '加锁时间：'+CONVERT(varchar(30), GETDATE(), 20);

     WAITFOR DELAY '00:00:10'            -- 事务等待10秒   

     PRINT '解锁时间：'+CONVERT(varchar(30), GETDATE(), 20);

COMMIT TRAN T3

进一步定义事务T4：

BEGIN TRAN T4

      DECLARE @s varchar(10);

     SELECT @s = flight FROM TestTransTable4;

     PRINT '数据查询时间：'+CONVERT(varchar(30), GETDATE(), 20);

COMMIT TRAN T4

 

 

 

 

Ø  与例13.10类似，分别在两个查询窗口中先后运行事务T3和事务T4（时间间隔要小于10秒），事务T3和T4输出的结果分别如图10.19和图10.20所示。

 

 

Ø  事务T3通过利用TABLOCKX选项对表TestTransTable4加上排他锁以后，事务T4对该表的查询操作只能在事务T3结束之后才能进行，其他更新操作（如INSERT、UPDATE、DELETE）更是如此。因此，利用排他锁可以实现事务执行的串行化控制。