SQL Server 事 务和并 发 控 制

1.1  事务

19.1.1  事务的概念

事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中数据的一致性和可恢复性。

举一个简单的例子，ATM机。

你通过ATM系统转账。你有1000元，对方有1000元。你将把500元从你的账户划到对方账户，最终的结果是你有500元，对方的账户1500元。但在交易时，当你从帐上取走500元后，软件出现故障，没有来得及去给对方存钱，也就是你的账户少了500，而对方并没有增加。这就会导致数据的不一致性存在。而通过事务，就可以实现。

1、事务的特性

原子性：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。  

一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。

隔离性：一事务的执行不能被其它事务干扰。

持续性（永久性）：指事务一旦提交，则其对数据库中数据的改变就应该是永久的  

 

2、事务和批的区别

编程时，一定要区分事务和批的差别。

1）批是一组整体编译的SQL语句，事务是一组作为单个逻辑工作单元执行的SQL语句。

2）批语句的组合发生在编译时刻，事务中语句的组合发生在执行时刻。

3）当在编译时，批中某个语句存在语法错误，系统将取消整个批中所有语句执行，而在运行时刻，如果事务中某个数据修改违反约束、规则等，系统默认只回退到产生该错误的语句。

一个事务中也可以拥有多个批，一个批里可以有多个SQL语句组成的事务，事务内批的多少不影响事务的提交或回滚操作。

19.1.2  事务对并发控制和保障
数据完整的重要性

1．事务与并发控制的关系

并发性是用来解决多个用户对同一数据进行操作时的问题。 特别是对于网络数据库来说， 这个特点更加突出。

例如：在线售票系统。

假设有一个订票系统，在9：00这个时刻，甲乙同时在2个窗口买票，甲买5张，已买

10张。正常情况下剩余票数是5--5-10=35张。

如果数据库没有并发手段，则会出现以下两种情况：

（1）甲先买上，将剩余票45写入数据库；乙后买上，将40写回数据库，因此剩余票数为40

（2）乙先买上，将剩余票40写入数据库；甲后买上，将45写回数据库，因此剩余票数为45

如果在用户并发访问期间没有保证多个事务正确的交叉运行，用户操作相同的数据时可能会产生一些意想不到的问题

2．事务对保障数据一致和完整性的作用

故障轻则造成运行事务非正常中断，影响数据库中数据的正确性，重则破坏数据库，使数据库中全部或部分数据丢失 。

影响事务正常运行的故障有：

（1）事务内部的故障

（2）系统故障

（3）介质故障

（4）计算机病毒 

19.2 事务的分类

SQL Server 的事务模式可分为显式事务、隐式事务和自动事务三种。

1)  显式事务

显式事务是指由用户执行T-sql事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括：

BEGIN TRANSACTION:标识一个事务的开始，即启动事务。

COMMIT TRANSACTION、COMMIT WORK：标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。

ROLLBACK TRANSACTION、ROLLBACK WORK：标识一个事务的结束，说明事务执行过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。

 

2) 隐式事务

在隐式事务模式下，在当前事务提交或回滚后，SQL Server自动开始下一个事务。

所以，隐式事务不需要使用BEGIN TRANSACTION语句启动事务，而只需要用户使用ROLLBACKTRANSACTION、ROLLBACK WORK、COMMITTRANSACTION、COMMIT WORK等语句提交或回滚事务。在提交或回滚后，SQL Server自动开始下一个事务。执行SETIMPLICIT_TRANSACTIONS ON语句可使SQL Server进入隐式事务模式。在隐式事务模式下，当执行下面任意一个语句时，可使SQL Server重新启动一个事务： 所有CREATE语句、ALTER TABLE、所有DROP语句、TRUNCATE TABLE、GRANT                    

REVOKE、INSERT、UPDATE、DELETE、SELECT、OPEN、FETCH需要关闭隐式事务模式时，调用SET语句关闭IMPLICIT_TRANSACTIONS   OFF连接选项即可。

3) 自动事务模式

在自动事务模式下，当一个语句被成功执行后，它被自动提交，而当它执行过程中产生错误时，被自动回滚。自动事务模式是SQL Server的默认事务管理模式，当与SQL Server建立连接后，直接进入自动事务模式，直到使用BEGIN TRANSACTION语句开始一个显式事务，或者打开IMPLICIT_TRANSACTIONS连接选项进入隐式事务模式为止。而当显式事务被提交或IMPLICIT_TRANSACTIONS 被关闭后，SQL Server又进入自动事务管理模式。

   BEGIN TRAN demo

   SELECT *FROM Student

   INSERT INTOStudent

       VALUES('20090101','王明','男','1992-9-23',null,3)

   SELECT *FROM Student

   --ROLLBACK     -- 回滚整个事务

   COMMIT      -- 提交事务

19.3 事务处理实例分析

【例19-1】使用事务的三种模式进行表的处理，分批执行，观察执行的过程。

USE jxgl

GO

SELECT times=0,* FROM student --检查当前表中的结果

GO

--SQL Server首先处于自动事务管理模式

INSERT student VALUES

('20090201','关汉青','男','1989-3-17','中国山东滨州',2)

SELECT times=1,* FROM student --显示'20090201'被插入。

GO

INSERT student VALUES

('20090201','关汉青','男','1989-3-17','中国山东滨州',2)

--服务器:消息2627，级别14，状态1，行1

--违反了PRIMARY KEY约束'PK__Student__75A278F5'。不能在对象'Student'中插入重复键。

--语句已终止。

SELECT times=2,* FROM student   --显示数据没有变化。

GO

BEGIN TRANSACTION      --进入显式事务模式

INSERT student VALUES

('20090202','关汉青','男','1989-3-17','中国山东滨州',2)

SELECT times=3,*FROM student  --显示'20090202'被插入

ROLLBACK TRANSACTION

GO

SELECT times=4,*FROM student     --因为执行了回滚，插入的'20090202'被撤消。

GO

SET IMPLICIT_TRANSACTIONSON        --进入隐式事务模式

INSERT student VALUES

('20090203','关汉青','男','1989-3-17','中国山东滨州',2)

SELECT times=5,*FROM student   --显示'20090203'被插入

ROLLBACK                             

GO

SELECT times=6,*FROM student     --因为执行了回滚，插入的'20090203'被撤消。

GO

DELETE FROM student WHERE学号='20090201'   --删除第个插入

SELECT times=7,*FROM student  --显示'20090201'不存在

ROLLBACK

GO                                    

SELECT times=8,*FROM student    --因为回滚，使删除作废，所以'20090201'又重新显示存在。

GO

SET IMPLICIT_TRANSACTIONSOFF      --隐式事务模式结束，又进入自动模式

DELETE FROM student WHERE学号='20090201'--删除第个插入

SELECT times=9,*FROM student   

       --自动模式执行成功被自动提交，显示'20090201'被删除不存在。

 

【例19-2】定义事务，使事务回滚到指定的保存点，分批执行，观察执行的过程。

USE jxgl

GO

SELECT times=0,* FROM student      --检查当前表中的结果

GO

BEGIN TRANSACTION demo

INSERT student VALUES

('20090201','关汉青','男','1989-3-17','中国山东滨州',2)

SAVE TRANSACTION save_demo

INSERT student VALUES

('20090202','关汉青','男','1989-3-17','中国山东滨州',2)

SELECT times=1,* FROM student       --显示'20090201'和'20090202'都被插入。

GO

ROLLBACK TRANSACTION save_demo        --回滚部分事务

SELECT times=2,* FROM student       --显示'20090202'被撤消不存在。

GO

ROLLBACK TRANSACTION                    --回滚整个事务

SELECT times=3,* FROM student       --显示'20090201'被撤消不存在。

 

 

【例19-3】创建数据表stu_test3，生成三个级别的嵌套时务，并提交该嵌套事务。观察变量@@TRANCOUNT的值的变化。

 

USE jxgl        --选择数据库必须单独在一个批中

GO

SELECT @@TRANCOUNT   --变量@@TRANCOUNT的值为

BEGIN TRANSACTION inside1            

SELECT @@TRANCOUNT   --变量@@TRANCOUNT的值为

INSERT student VALUES

('20090201','关汉青','男','1989-3-17','中国山东滨州',2)

GO

BEGIN TRANSACTION inside2            

SELECT @@TRANCOUNT   --变量@@TRANCOUNT的值为

INSERT student VALUES

('20090202','关汉青','男','1989-3-17','中国山东滨州',2)

GO

BEGIN TRANSACTION inside3            

SELECT @@TRANCOUNT          --变量@@TRANCOUNT的值为

INSERT student VALUES

('20090203','关汉青','男','1989-3-17','中国山东滨州',2)

GO

COMMIT TRANSACTION inside3           

SELECT @@TRANCOUNT         --变量@@TRANCOUNT的值减为

GO

COMMIT TRANSACTION inside2           

SELECT @@TRANCOUNT --变量@@TRANCOUNT的值减为

GO

COMMIT TRANSACTION inside1           

SELECT @@TRANCOUNT --变量@@TRANCOUNT的值减为

   GO

【例19-4】在教学管理数据的STUDENT表中先删除一条记录，然后再插入一条记录，通过测试错误值c确定提交还是回滚。

USE jxgl

GO

DECLARE @del_error int,@ins_error int

-- 开始一个事务

BEGIN TRAN

-- 删除一个学生

DELETE FROM student WHERE学号='20090201'

-- 为删除语句设置一个接受错误数值的变量

SELECT @del_error = @@ERROR

--再执行插入语句

INSERT student VALUES

('20090201','关汉青','男','1989-3-17','中国山东滨州',2)

 

--为插入语句设置一个接受错误数值的变量

SELECT @ins_error = @@ERROR

--测试错误变量中的值

IF @del_error = 0 AND@ins_error = 0

   BEGIN

       print '成功，提交事务'

       COMMIT TRAN

   END

ELSE

   BEGIN

       print '有错误发生，回滚事务'

     IF@del_error <> 0

        PRINT '错误发生在删除语句'

     IF@ins_error <> 0

        PRINT '错误发生在插入语句'

     ROLLBACK TRAN

   END

   GO

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

19.6 并发控制

在大型分布式数据库应用程序中，对数据库的并发访问操作是一个普遍存在的问题。SQL Server使用资源锁定的方法管理用户的并发操作。 SQL Server 2000提供了两种并发控制方法：

▲乐观并发控制：该方法假想用户之间不太可能发生资源冲突（事实上不是不可能），所以允许用户在不锁定任何资源的情况下执行事务。只有当用户试图修改数据时才检查资源是否冲突。该方法需要使用游标，游标中介绍。

▲悲观并发控制：该方法根据需要在事务的持续时间内锁定资源，从而确保事务的完整性和数据库的一致性。这是SQL Server 2000默认的并发控制方法。下面予以介绍。

8.6.1  SQL Server锁的粒度及模式

1、SQL Server 锁的粒度

1）RID：行标识符，锁定表中单行数据。

2）键值：具有索引的行数据。

3）页面：一个数据页面或索引页面。

4）区域：一组连续的8个数据页面或索引页面。

5）表：    整个表，包括其所有的数据和索引。

6）数据库：一个完整的数据库。

可以根据事务所执行的任务来灵活选择所锁定的资源粒度。

2、资源锁定模式—基本锁

1）共享锁：用于只读数据操作，它允许多个并发事务对资源锁定进行读取，但禁止其他事务对锁定资源的修改操作。

2）排它锁：它锁定的资源不能被其它并发事务再进行任何锁定，所以其它事务不能读取和修改。锁定的资源用于自己的数据修改。

一般更新模式是由一个事务组成，该事务先读取记录，要获取资源的共享锁，然后修改记录，此操作要求锁转换为排它锁。如果两个事务都获得了资源上的共享锁，然后试图同时更新数据，这样肯定有一个事务要将共享锁转化为排它锁，因为一个事务的排它锁与其它事务的共享锁不兼容，发生锁等待。另一个事务也会出现这个问题，由于两个事务都要转化为排它锁，并且都等待另一个事务释放共享锁，因此发生死锁。

 

2、资源锁定模式—专用锁

3）更新锁：在修改操作的初始化阶段用于锁定可能被修改的资源。一个数据修改事务在开始时直接申请更新锁，每次只有一个事务可以获得资源的更新锁。

使用更新锁可以避免上述死锁，因为一次只有一个事务可以获得更新锁，之后当需要继续修改数据时，将更新锁转换为排它锁，否则将更新锁转换为共享锁。

4）意向锁

意向锁表示如果获得一个对象的锁，说明该结点的下层对象正在被加锁。例如放置在表上的共享意向锁表示事务打算在表中的页或行上加共享锁。

意向锁可以提高性能，因为系统仅在表级上检查意向锁而无须检查下层。

①意向共享锁：对一个对象加意向共享锁，表示将要对它的下层对象加共享锁。

②意向排它锁：对一个对象加意向排它锁，表示将要对它的下层对象加排它锁。

③意向排它共享锁：对一个对象加意向排它共享锁，表示对它加共享锁，再在它的下层对象加排它锁。

5）架构锁

①架构修改锁：执行表的数据定义语言（DDL）操作时使用。

②架构稳定锁：编译查询时使用。它不阻塞任何事务锁，包括排它锁。

6）大容量更新锁：

当数据大容量复制到表的时候使用。

8.6.2 封锁协议

在运用X锁和S锁对数据对象加锁时，需要约定一些规则：封锁协议（Locking Protocol）

何时申请X锁或S锁持锁时间、何时释放

 不同的封锁协议，在不同的程度上为并发操作的正确调度提供一定的保证

常用的封锁协议：三级封锁协议

1级封锁协议

事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放

正常结束（COMMIT）

非正常结束（ROLLBACK）

1级封锁协议可防止丢失修改

在1级封锁协议中，如果是读数据，不需要加锁的，所以它不能保证可重复读和不读“脏”数据。

2级封锁协议

1级封锁协议+事务T在读取数据R前必须先加S锁，读完后即可释放S锁

2级封锁协议可以防止丢失修改和读“脏”数据。

在2级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读。

3级封锁协议

1级封锁协议 + 事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放

3级封锁协议可防止丢失修改、读脏数据和不可重复读。

容易造成比较多的死锁

封锁协议小结

8.6.3 事务隔离

为了避免产生并发访问问题，SQL Server使用不同类型的锁对资源进行锁定，从而限制在一个事务读取数据期间其他事务锁执行的操作类型，即对事务进行隔离。

不同的并发访问问题可以通过设置不同的事务隔离级别加以解决。事务的隔离级别控制一个事务与其他事务的隔离程度，它决定该事务在读取数据时对资源所使用的锁类型。

2）事务隔离级别对不同类型的行为

隔离级别

脏数据

丢失修改

不可重读

幻影

未提交读

是

是

是

是

提交读

否

是

是

是

可重复读

否

否

否

是

可串行读

否

否

否

否

3）设置事务隔离级别

调用T-SQL中的SET TRANSACTION  INOLATION  LEVEL语句可以调整事务的隔离级别，以控制由该连接所发出的所有SELECT语句的默认事务锁定行为。该语句的语法格式为：

  SET TRANSACTION ISOLATION LEVEL{

      READUNCOMMITTED

     |READCOMMITTED

    |REPEATABLE  READ

    |SERIALIZABLE

  }

例如：将事务隔离级别设置为REPEATABLE  READ

SET TRANSACTION ISOLATION LEVEL

REPEATABLE  READ

 

注意：一旦设定，系统就会按这种隔离级别自动进行并发处理。

      设置表级锁

表级锁是由程序员进行的，可以对SELECT、INSERT、UPDATE、DELETE语句进行精确控制。

表级锁定不是系统自动完成，而是需要程序员自己根据事务的要求进行锁定，然后，系统按程序员在程序中的锁定予以执行。

一般来说，读操作需要共享锁，写操作需要排它锁。如果需要更精确，还需要一些其它专用锁。具体设置有：

设置共享锁

设置排它锁

设置专用锁

 

         设置共享锁

共享锁用于读操作。一个资源可获得多个共享锁。

使用HOLDLOCK设置共享锁。

例：

USE  Northwind

GO

BEGIN TRANSANCTION T1

SELECT OrderID,OrderDate  FROM  Orders

                 WITH   (HOLDLOCK)

SELECT COUNT(OrderID)  FROM  Orders

COMMIT

   设置排它锁

对于INSERT、UPDATE和DELETE语句使用排它锁。在并发事务中，只有一个事务能够获得资源的排它锁。

使用TABLOCKX设置排它锁。

例：

USE Northwind

GO

BEGIN TRANSANCTION T1

INSERT INTO  Orders  WITH (TABLOCKX)

               (CustomerID,OrderDate) 

VALUES (‘ALEF’,’2005-01-01’)

COMMIT

      设置其它专用锁

方法同上。

NOLOCK：不要发出共享锁和排它锁。仅适用SELECT语句。

READPAST：跳过已经锁定的行。仅适用SELECT语句。

TABLOCK：使用大容量更新锁。

PAGLOCK：使用页级锁。

ROWLOCK：使用行级锁，不使用页级和表级锁。

UPDLOCK：读取表时使用更新锁。

XLOCK：适用于不同粒度的排它锁。

8.6.4 死锁处理

SQL Server能够自动定期搜索和处理死锁问题。当检测到有死锁时， SQL Server回滚被中断的事务，并向应用程序返回1205号错误信息，未被中断的事务则继续执行。在数据库应用程序扑捉到1205号错误，可以对死锁现象做后续处理。

为减少死锁次数，在设计应用程序时：

1）尽量使一个事务在一个批内，并且要短；

2）不要在事务处理期间和用户交互；

3）灵活使用较低级别的事务隔离；

4）在事务处理期间要使访问数据量最小。