数据库原理与运用之事务控制与并发处理.doc

第13章 事务控制与并发处理13.1 事物概述数据库系统是面向多用户的。一个基本问题是，多个用户同时访问数据库的情况下如何保证数据的一致性和有效性？事务控制与并发处理为此类问题的解决提供了一种有效的途径。事务是数据库并发控制技术涉及的基本概念，是并发控制的基本单位。13.1.1什么是事务事务（Transaction）是指作为单个逻辑工作单元执行的一系列数据库操作。一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中数据的一致性和可恢复性。 例如：对于银行转帐问题，将帐号A1上的金额x转入帐号A2。如何保证帐号信息的是正确的？13.1.2 事务的特性一个逻辑工作单元要成为事务，必须满足所谓的ACID(原子性、一致性、隔离性和持久性)属性。l 原子性(Atomicity)：事务是数据库操作的逻辑工作单位。就操作而言，事务中的操作是一个整体，不能再被分割，要么全部成功执行，要么全部不成功执行。l 一致性(Consistency)：事务一致性是指事务执行前后都能够保持数据库状态的一致性，即事务的执行结果是将数据库从一个一致性状态变到另一个一致性状态。l 隔离性(Isolation)：隔离性是指多个事务在执行时不互相干扰。事务具有隔离性意味着一个事务的内部操作即其使用的数据库对其他事务是不透明的，其他事务不会干扰这些操作和数据。l 持续性 (Durability) ：指事务一旦提交，则其对数据库中数据的改变就应该是永久的，即使是出现系统故障等问题。事务开始之后，事务所有的操作都陆续写到事务日志中。这些任务操作在事务日志中记录一个标志，用于表示执行了这种操作，当取消这种事务时，系统自动执行这种操作的反操作，保证系统的一致性。系统自动生成一个检查点机制，这个检查点周期地发生。检查点的周期是系统根据用户定义的时间间隔和系统活动的频度由系统自动计算出来的时间间隔。检查点周期地检查事务日志，如果在事务日志中，事务全部完成，那么检查点将事务提交到数据库中，并且在事务日志中做一个检查点提交标记。如果在事务日志中，事务没有完成，那么检查点将事务日志中的事务不提交到数据库中，并且在事务日志中做一个检查点未提交标记。13.1.3 事务和批的差别编程时，一定要区分事务和批的差别：1. 批是一组整体编译的SQL语句，事务是一组作为单个逻辑工作单元执行的SQL语句。2. 批语句的组合发生在编译时刻，事务中语句的组合发生在执行时刻。3. 当在编译时，批中某个语句存在语法错误，系统将取消整个批中所有语句执行，而在运行时刻，如果事务中某个数据修改违反约束、规则等，系统默认只回退到产生该错误的语句。4. 如果批中产生一个运行时错误，系统默认只回退到产生该错误的语句。但当打开XACT_ABORT选项为ON时，可以系统自动回滚产生该错误的当前事务。一个事务中也可以拥有多个批，一个批里可以有多个SQL语句组成的事务，事务内批的多少不影响事务的提交或回滚操作。 13.1.4 事务的类型根据事务的设置、用途的不同，SQL Server 2005将事务分为多种类型。1. 根据系统的设置分类根据系统的设置，SQL Server 2005将事务分为两种类型：系统提供的事务和用户定义的事务，分别简称为系统事务和用户定义事务。(1) 系统事务系统提供的事务是指在执行某些语句时，一条语句就是一个事务。但是要明确，一条语句的对象既可能是表中的一行数据，也可能是表中的多行数据，甚至是表中的全部数据。因此，只有一条语句构成的事务也可能包含了多行数据的处理。系统提供的事务语句如下：ALTER TABLE 、CREATE、DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REBOKE、SELECT、UPDATE、TRUNCATE TABLE这些语句本身就构成了一个事务。【例1】使用CREATE TABLE创建一个表。CREATE TABLE student (id CHAR(10), name CHAR(6), sex CHAR(2) )说明：这条语句本身就构成了一个事务。这条语句由于没有使用条件限制，那么这条语句就是创建包含3个列的表。要么创建全部成功，要么全部失败。(2) 用户定义事务在实际应用中，大多数的事务处理采用了用户定义的事务来处理。在开发应用程序时，可以使用BEGIN TRANSACTION语句来定义明确的用户定义的事务。在使用用户定义的事务时，一定要注意事务必须有明确的结束语句来结束。如果不使用明确的结束语句来结束，那么系统可能把从事务开始到用户关闭连接之间的全部操作都作为一个事务来对待。事务的明确结束可以使用两个语句中的一个：COMMIT语句和ROLLBACK语句。COMMIT语句是提交语句，将全部完成的语句明确地提交到数据库中。ROLLBACK语句是取消语句，该语句将事务的操作全部取消，即表示事务操作失败。还有一种特殊的用户定义的事务，这就是分布式事务。例12-1事务是在一个服务器上的操作，其保证的数据完整性和一致性是指一个服务器上的完整性和一致性。但是，如果一个比较复杂的环境，可能有多台服务器，那么要保证在多台服务器环境中事务的完整性和一致性，就必须定义一个分布式事务。在这个分布式事务中，所有的操作都可以涉及对多个服务器的操作，当这些操作都成功时，那么所有这些操作都提交到相应服务器的数据库中，如果这些操作中有一个操作失败，那么这个分布式事务中的全部操作都将被取消。2. 根据运行模式分类根据运行模式，SQL Server 2005将事务分为种类型：自动提交事务、显示事务、隐式事务和批处理级事务。(1) 自动提交事务自动提交事务是指每条单独的语句都是一个事务。(2) 显式事务显式事务指每个事务均以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显示结束。(3) 隐式事务隐式事务指在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。(4) 批处理级事务该事务只能应用于多个活动结果集（MARS），在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。当批处理完成时，没有提交或回滚的批处理级事务自动由SQL Server语句集合分组后形成单个的逻辑工作单元。13.2 事物的控制事务是一个数据库操作序列，由若干个语句组成。那么如何利用自己已有的语句组成一个事务呢？这就涉及到事务的启动和终止问题。13.2.1 启动事务在SQL Server中，启动事务的方式有三种：显示启动、自动提交和隐式启动。1. 显示启动显示启动是以BEGIN TRANSACTION 命令开始的，即当执行该语句时，SQL Server将认为这是一个事务的起点。BEGIN TRANSATION的语法如下：BEGIN TRAN | TRANSACTIONTRANSACTION_NAME | TRAN_NAME_VARIABLEWITH MARK description;RANSACTION_NAME | TRAN_NAME_VARIABLE:指定事务的名称，可以用变量提供名称。该项是可选项。WITH MARK description：指定在日志中标记事务。Description是描述该标记的字符串。如果使用了WITH REMARK，则必须指定事务名。WITH REMARK允许将事务日志还原到命名标记。2. 自动提交自动提交是指用户每发出一条SQL语句，SQL SERVER会自动启动一条事务，语句执行完了以后，SQL SERVER自动执行提交操作来提交该事务。3. 隐式启动 当将IMPLICIT_TRANSACTIONS设置为ON时，表示将隐式事务模式设置为打开，设置语句如下：SET IMPLICIT_TRANSACTIONS ON;在隐式事务模式下，任何DML语句（Delete、Update、INSERT）都自动启动一个事务。隐式启动的事务通常称为隐性事务。13.2.2 终止事务终止方法有两种，一种是使用COMMIT命令（提交命令），另一种是使用ROLLBACK命令（回滚命令）。但这俩种方法有本质上的区别；当执行COMMIT命令是，会将语句执行结果保存到数据库中，并终止事务；当执行ROLLBACK命令时，数据库将返回到事务开始时的初始状态，并终止事务。1. COMMIT TRANSACTION 提交事务执行COMMIT TRANSACTION 语句时，将终止隐式启动或显示启动的事务。如果TRANCOUNT为1，COMMIT TRANSACTION使得自从事务开始以来所有数据修改成为数据库的永久部分，释放事务所占用的资源，并将TRANCOUNT为0。如果大于1，则COMMIT TRANSACTION使TRANCOUNT按1递减并且事务将保持活动状态。COMMIT TRAN| TRANSCATION transaction_name | tran_name_variable;【例2】实现银行帐号转帐功能的事务。BEGIN TRANSCATION virementDECLARE balance float, x float; -显示启动事务SET x = 200; -当转出金额小于x时，取消操作SELECT (balance = balance FROM UserTable WHERE acount = 20000000xxxxxxx1;If (balance x) return;UPDATE UserTable SET balance = balance - x where account = 20000000xxxxxxx1;UPDATE UserTable SET balance = balance + x where account = 20000000xxxxxxx2;GOCOMMIT TRANSACTION virement; -提交事务，事务终止如果在一个事务中，既有成功执行的DML，也有因为内部错误而导致失败执行的DML语句，该事务会回滚吗？一般，SQL-Server只回滚产生错误的语句，而不会回滚整个事务。如果希望遇到错误时，事务能够自动回滚整个事务，则SET XACT_ABORT选项设置为ON。SET XACT_ABORT ON;2. 回滚事务ROLLBACK TRANSACTION它可以将显示事务或隐式事务回滚到事务的起点或事务内部的某个保存点。ROLLBACK TRAN | TRANSACTIONtransaction_name | tran_name_variable | savepoint_name ;根据是否有保存点可以将回滚分为全部回滚或部分回滚。设置保存点：用于在事务中设置一个保存点，目的是在撤消事务时可以只撤消部分事务，以提高系统的效率 SAVE TRANSACTION save1;两个可用于事务管理的全局变量是error及rowcount。l error：给出最近一次执行的出错语句引发的错误号，error为0表示未出错。l rowcount：给出受事务中已执行语句所影响的数据行数。事务控制语句的使用事务控制语句的使用方法如下：BEGIN TRAN/ *A组语句序列* /SAVE TRAN save_point/ *B组语句序列* /if error0ROLLBACK TRAN save_point / *仅回退B组语句序列* /COMMIT TRAN/ *提交A组语句，且若未回退B组语句则提交B组语句* /【例3】使用事务向表book中插入数据。BEGIN TRAN tran_exampINSERT INTO book( book_id , book_name , publish_company)VALUES( dep04_s006_01 , VFP程序设计 , 南京大学出版社 );SAVE TRAN int_point;INSERT INTO book( book_id , book_name , publish_company )VALUES( dep04_s006_02 , VFP实验指导书 , 东南大学出版社 ); INSERT INTO book(book_id , book_name)VALUES(dep04_s006_03 , VFP课程设计指导书); IF error0 ROLLBACK TRAN int_point;COMMIT TRAN tran_examp;13.2.3 嵌套事务事务允许嵌套的。当事务嵌套时，多个事务同时处于活动状态。13.3 并发控制DBMS 必须提供一种允许多个用户同时对数据库进行存取访问控制的机制，同时确保数据库的一致性和完整性，这就是并发控制。13.3.1 数据的几种不一致性数据不完整会破坏事务的ACID特性，它是诱发并发错误的主要原因。主要有以下几种造成数据不一致的原因。1.脏写例如：两个火车售票点出售火车票的例子。2.读“脏”数据3.不可重复读4.幻影读任务1：读出数据A，将其加上50，写回任务2：读出数据A，将其加上40，写回1. 脏写时间任务1任务21X=R(A)=1002X=R(A)=1003X+=50 4 X+=405W(A, X) = 150 6 W(A, X) = 1407COMMIT 8 COMMIT串行执行结果：190 并发执行结果：1402.脏读时间点任务1任务21X=R(A)=100 2X+=50 3W(A, X) = 150 4 X=R(A)=1505ROLLBACK 6 X+=407 W(A, X) = 1908 COMMIT串行执行：140 并发执行：1903.不可重复读写时间点任务1任务21X=R(A)=100 2 X=R(A)=1003X+=50 4W(A, X) = 150 5COMMIT 6 X+=407 W(A, X) = 1408 COMMIT串行执行结果：190 并发执行结果：1404. 幻影读时间点任务1任务2原有4行数据，总和为10001S=SELECT SUM(A) = 1000 2 INSERT (A) VALUES (200)3 COMMIT4N=SELECT COUNT(*) =5 5AVG=S/N = 200 6 串行执行结果：250 并发执行结果：20013.3.2 基于事务隔离级别的并发控制语法实现：SET TRANSACTION ISOLATEION LEVELREAD UNCOMMITTED | READ COMMITED | REPEATABLE READ| SNAPSHOT | SERIALIZABLE ;1. READ UNCOMMITTED该隔离级别允许读取已经被其他事务修改但尚未提交的数据。是隔离级别中限制最少的一种。2. READ COMMITED该隔离级别允许事务读取已提交的数据。3. REPEATABLE READ不能读取已由其他事务修改但尚未提交的数据。4. SERIALIZABLE事务之间只能顺序执行。5. SNAPSHOT事务只能识别在其开始之前提交的数据修改，看不到在当前事务开始以后的其他事务的修改。隔离级别脏写脏读不可重复读写幻影读READ UNCOMMITTED OOOOREAD COMMITEDPPOOREPEATABLE READPPPOSERIALIZABLEPPPPSNAPSHOTPPPP13.3.2 基于锁的并发控制1.锁的概念锁定是指对数据源的锁定，在多用户同时使用时，对同个数据块的访问的一种机制。这种机制的实现就是锁（Lock）来完成的。一个事务可以申请对一个资源加锁，一旦成功，则其他事务就要等该事务对此资源访问结束后才能访问此资源。用于保证数据的一致性和完整性。2. 锁的分类从数据库系统的角度来看：分为独占锁（即排它锁），共享锁和更新锁锁模式描述共享 (S)用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。更新 (U)用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。排它 (X)用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。 意向锁用于建立锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。架构锁在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。大容量更新 (BU)向表中大容量复制数据并指定了 TABLOCK 提示时使用。共享锁 共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。 更新锁 更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。 若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。 排它锁 排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。 意向锁 意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。 意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。 锁模式 描述 意向共享 (IS) 通过在各资源上放置 S 锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。 意向排它 (IX) 通过在各资源上放置 X 锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。IX 是 IS 的超集。 与意向排它共享 (SIX) 通过在各资源上放置 IX 锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。允许顶层资源上的并发 IS 锁。例如，表的 SIX 锁在表上放置一个 SIX 锁（允许并发 IS 锁），在当前所修改页上放置 IX 锁（在已修改行上放置 X 锁）。虽然每个资源在一段时间内只能有一个 SIX 锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的 IS 锁来读取层次结构中的底层资源。 独占锁：只允许进行锁定操作的程序使用，其他任何对他的操作均不会被接受。执行数据更新命令时，SQL Server会自动使用独占锁。当对象上有其他锁存在时，无法对其加独占锁。 共享锁：共享锁锁定的资源可以被其他用户读取