数据库原理及应用 第10章 事务与锁 10.1 事务 10.2 锁

第10章 事务与锁 10.1 事 务 10.1.1 事务的概念 事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单元。通过事务，Microsoft SQL Server 能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。 事务通常是以BEGIN TRANSACTION 开始，以COMMIT 或ROLLBACK 结束。 COMMIT表示提交，即提交事务的所有操作。具体地说就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，事务正常结束。 ROLLBACK 表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有以完成的操作全部撤销，回滚到事务开始的状态。

10.1.2 事务的特征 第10章 事务与锁 1. 原子性（Atomicity） 第10章 事务与锁 10.1.2 事务的特征 事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为ACID（原子性、一致性、隔离性和持久性）属性，符合这四个属性的逻辑工作单元就可以称为一个事务。 1. 原子性（Atomicity） 事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。事务必须是原子工作单元；其对于数据的修改，要么全都执行，要么全都不执行。

第10章 事务与锁 2. 一致性（Consistency） 3. 隔离性（Isolation） 第10章 事务与锁 2. 一致性（Consistency） 数据库的一致状态是指数据库中的数据满足完整性约束。事务在完成时，必须使所有的数据都保持一致状态。换句话说，一个事务应该把系统从一个一致状态转换到另一个一致状态。举个例子，在关系数据库的情况下，一个一致的事务将保护定义在数据上的所有完整性约束。 例如，当开发用于转账的应用程序时，应避免在转账过程中任意移动小数点。 3. 隔离性（Isolation） 在同一个环境中可能有多个事务并发执行，而每个事务都应表现为独立执行。串行的执行一系列事务的效果应该同于并发的执行它们。这要求两件事：在一个事务执行过程中，数据的中间的（可能不一致）状态不应该被暴露给所有的其他事务。两个并发的事务应该不能操作同一项数据。 数据库管理系统通常使用锁来实现这个特征。由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据，这称为可串行性。

第10章 事务与锁 4. 持久性（Durability） 第10章 事务与锁 4. 持久性（Durability） 持久性指一个事务一旦提交，它对数据库中数据的改变就应该是持久的，即使数据库因故障而受到破坏，DBMS 也应该能够恢复。即对事务发出COMMIT 命令后，即使这时发生系统故障，事务的效果也被持久化了。同样，当在事务执行过程中，系统发生故障，则事务的操作都被回滚，即数据库回到事务开始之前的状态。 对数据库中的数据修改都是在内存中完成的，这些修改的结果可能已经写到硬盘也可能没有写到硬盘，如果在操作过程中，发生断电或系统错误等故障，数据库可以保证未结束的事务对数据库的数据修改结果（即使已经写入磁盘）在下次数据库启动后也会被全部撤销；而对于结束的事务（即使其修改的结果还未写入磁盘）在数据库下次启动后会通过事务日志中的记录进行“重做”，把丢失的数据修改结果重新生成，并写入磁盘，从而保证结束事务对数据修改的永久化。 事务的ACID只是一个抽象的概念，具体是由RDBMS 来实现的。数据库管理系统用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生了错误，就可以根据日志，撤销事务对数据库已经做的更新，使数据库回退到执行事务前的初始状态。

10.1.3 执行事务的三种模式 第10章 事务与锁 1. 显式事务 语法： 第10章 事务与锁 10.1.3 执行事务的三种模式 1. 显式事务 显式事务是指在自动提交模式下以 Begin Transaction 开始一个事务，以 Commit 或Rollback 结束一个事务，以Commit 结束事务是把事务中的修改永久化，即使这时发生断电这样的故障。BEGIN TRANSACTION 标记一个显式本地事务起始点。BEGIN TRANSACTION会自动将@@TRANCOUNT 加1。 语法： BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable[ WITH MARK[ 'description' ] ] ]

第10章 事务与锁 2. 自动提交事务 系统默认的事务方式，是指对于用户发出的每条SQL 语句SQL Server 都会自动开始一个事务，并且在执行后自动进行提交操作来完成这个事务，在这种事务模式下，一个SQL 语句就是一个事务。 3. 隐式事务 当连接以隐性事务模式进行操作时，SQL Server 将在提交或回滚当前事务后自动启动新事务。无须描述事务的开始，只需用 Commit 提交或Rollback 回滚每个事务。隐性事务模式生成连续的事务链。

第10章 事务与锁 10.1.4 事务隔离级别 1. Read Uncommitted 未提交读取 第10章 事务与锁 10.1.4 事务隔离级别 事务隔离级别的前提是一个多用户、多进程、多线程的并发系统，在这个系统中为了保证数据的一致性和完整性，引入了事务隔离级别这个概念，对一个单用户、单线程的应用来说则不存在这个问题。在SQL Server 中提供了四种隔离级别： 1. Read Uncommitted 未提交读取 一个会话可以读取其他事务还未提交的更新结果，如果这个事务最后以回滚结束，这时的读取结果就可能是错误的，所以多数的数据库应用都不会使用这种隔离级别。 2. Read Committed 已提交读取 这是SQL Server 的缺省隔离级别，设置为这种隔离级别的事务只能读取其他事务已经提交的更新结果，否则发生等待。但是其他会话可以修改这个事务中被读取的记录，而不必等待事务结束，显然，在这种隔离级别下，一个事务中的两个相同的读取操作，其结果可能不同。

第10章 事务与锁 3. Read Repeatable 可重复读取 4. Serializable 序列化 第10章 事务与锁 3. Read Repeatable 可重复读取 在一个事务中，如果在两次相同条件的读取操作之间没有添加记录的操作，也没有其他更新操作导致在这个查询条件下记录数增多，则两次读取结果相同。换句话说，就是在一个事务中第一次读取的记录保证不会在这个事务期间发生改变。SQL Server 是通过在整个事务期间给读取的记录加锁实现这种隔离级别的，这样，在这个事务结束前，其他会话不能修改事务中读取的记录，而只能等待事务结束，但是SQL Server 不会阻碍其他会话向表中添加记录，也不阻碍其他会话修改其他记录。 4. Serializable 序列化 在一个事务中，读取操作的结果是在这个事务开始之前其他事务就已经提交的记录，SQLServer 通过在整个事务期间给表加锁实现这种隔离级别。在这种隔离级别下，对这个表的所有 DML 操作都是不允许的，即要等待事务结束，这样就保证了在一个事务中的两次读取操作的结果肯定是相同的。 隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

第10章 事务与锁 在SQL Server 2008 中可以使用SET TRANSACTION ISOLATION LEVEL 命令设置当前的事务隔离级别。 语法： SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE } 【例 10.3】 设置SQL Server 的隔离级别为read committed。 set transaction isolation level read committed

第10章 事务与锁 10.1.5 事务保存点的设置与回滚 语法： 参数： 第10章 事务与锁 10.1.5 事务保存点的设置与回滚 事务保存点是指在事务内设置保存点或标记，在遇到事务处理出错时返回到事务记录点。 语法： SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }[ ; ] 参数： savepoint_name：是指派给保存点的名称。保存点名称必须符合标识符规则，但只使用前 32 个字符 @savepoint_variable：是用户定义的、含有有效保存点名称的变量的名称，长度不能超过32 个字符。如果长度超过 32 个字符，也可以传递到变量，但只使用前 32 个字符。必须用char、varchar、nchar 或nvarchar 数据类型声明该变量。

第10章 事务与锁 【例10.4】 事务保存点示例。 BEGIN TRAN… SAVE TRAN TempTran… IF（@@ERROR<>0） BEGIN ROLLBACK TRAN TempTran –-回滚到事务保存点 …… /*失败时所使用的变通方案*/ END...IF(...) COMMIT ELSE ROLLBACK 如果将事务回滚到保存点，则根据需要必须完成其他剩余的Transact-SQL 语句和COMMIT TRANSACTION 语句，或者必须通过将事务回滚到起始点完全取消事务。若要取消整个事务，请使用ROLLBACK TRANSACTION transaction_name 语句，这将撤销事务的所有语句和过程。

第10章 事务与锁 【例10.5】 建立事务，实现向学生成绩表 StudScoreInfo 中插入数据，如果成功则提交，不成功回滚到插入前。 第10章 事务与锁 【例10.5】 建立事务，实现向学生成绩表 StudScoreInfo 中插入数据，如果成功则提交，不成功回滚到插入前。 Begin transaction save transaction sp1 INSERT INTO StudScoreInfo(StudNo,CourseID,StudScore) VALUES(‘20080654001’,’A010101’,90） if (@@ERROR<>0） begin rollback transaction sp1 print ‘插入数据失败！’ end else commit go

第10章 事务与锁 10.1.6 分布式事务 分布式事务是指事务的参与者、支持事务的服务器、资源服务器以及事务管理器分别位于不同的分布式系统的不同节点之上。如果要在事务中存取多个数据库服务器中的数据（包含执行存储过程），就必须使用“分布式事务”（Distributed Transaction）。 【例10.6】 分布式事务操作示例（其中AnotherSever 要替换成已经存在的另一台服务器）。 Begin Distributed tran INSERT INTO CourseInfo(CourseID,CourseName) VALUES(‘A00232’,’TestName’) If @@ERROR<>0 GOTO ERRORPROC INSERT INTO AnotherServer.DatabaseName.dbo.tablename(FieldName1,FieldName2) VALUES(‘VALUES1’,’VALUES2’) ERRORPROC： IF @@ERROR<>0 ROLLBACK ELSE COMMIT

第10章 事务与锁 10.2 锁 锁是网络数据库中的一个非常重要的概念，它主要用于多用户环境下保证数据库完整性和一致性。在用户有 SQL 请求时，系统分析请求，在同时考虑锁定条件和系统性能两个方面因素的情况下自动为数据库加上适当的锁，同时系统在运行期间常常自动进行优化处理，实行动态加锁。对于一般的用户而言，通过系统的自动锁定管理机制基本可以满足使用要求，但如果对数据安全、数据库完整性和一致性有特殊要求，可以自己控制数据库的锁定和解锁。 10.2.1 数据不一致问题 事务是完整性的单位，一个事务的执行是把数据库从一个一致的状态转换成另一个一致的状态。因此，如果事务孤立执行时是正确的，但如果多个事务并发交错地执行，就可能相互干扰，造成数据库状态的不一致。在多用户环境中，数据库必须避免同时进行的查询和更新发生冲突。这一点是很重要的，如果正在被处理的数据能够在该处理正在运行时被另一用户的修改所改变，那么该处理结果是不明确的。不加控制的并发存取会产生以下几种错误：

第10章 事务与锁 1. 丢失更新（Lost Update） 2. 脏读（Dirty Reads） 第10章 事务与锁 1. 丢失更新（Lost Update） 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。因为每个事务都不知道其他事务的存在，最后的更新将重写由其他事务所做的更新，这将导致数据丢失。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。 使用SET TRAN ISOLATION LEVEL SERIALIZABLE 语句，把事务隔离级别调整到SERIALIZABLE 可以解决问题。 2. 脏读（Dirty Reads） 一个事务读到另外一个事务还没有提交的数据，称之为脏读。就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。 解决方法：把事务隔离级别调整到READ COMMITTED，即使用语句SET TRAN ISOLATION LEVEL READ COMMITTED进行设置。

第10章 事务与锁 3. 不可重复读（No-repeatable Reads） 4. 幻觉读（Phantom Reads） 第10章 事务与锁 3. 不可重复读（No-repeatable Reads） 不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。 解决方法是把事务隔离级别调整到REPEATABLE READ。使用SET TRAN SOLATION LEVELREPEATABLE READ。 4. 幻觉读（Phantom Reads） 幻觉读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。 并发控制的主要方法是封锁，锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致。

第10章 事务与锁 10.2.2 锁的概念 1. 乐观并发性控制（Optimistic Concurrency） 第10章 事务与锁 10.2.2 锁的概念 锁（Lock）是指将指定的数据临时锁起来供用户使用，以防止该数据被别人修改或读取。 锁的一个主要作用就是进行并发性控制，并发性控制分为乐观与悲观并发性控制。并发性（Concurrency）是指允许多个事务同时进行数据处理的性质。 1. 乐观并发性控制（Optimistic Concurrency） 乐观控制（或称乐观锁定）就是假设发生数据存取冲突的机会很小，因此在事务中并不会持续锁定数据，而只有在更改数据时才会去锁定数据并检查是否发生存取冲突。 2. 悲观并发性控制（Pessimistic Concurrency） 悲观控制（或称悲观锁定）与乐观控制刚好相反，它会在事务中持续锁定要使用的数据，以确保数据可以正确存取。

10.2.3 SQL Server 2008 的锁机制 第10章 事务与锁 行级锁 第10章 事务与锁 10.2.3 SQL Server 2008 的锁机制 Microsoft SQL Server 2008 具有多粒度锁定，允许一个事务锁定不同类型的资源。为了使锁定的成本减至最少，Microsoft SQL Server 自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以增加并发但需要较大的开销，因为如果锁定了许多行，则需要控制更多的锁。锁定在较大的粒度（例如表）就并发而言是相当昂贵的，因为锁定整个表限制了其他事务对表中任意部分进行访问，但要求的开销较低，因为需要维护的锁较少。SQL Server可以锁定行、页、扩展盘区、表、库等资源。 行级锁 行是可以锁定的最小空间，行级锁占用的数据资源最少，所以在事务的处理过程中，允许其他事务继续操纵同一个表或者同一个页的其他数据，大大降低了其他事务等待处理的时间，提高了系统的并发性。

第10章 事务与锁 页级锁 页级锁是指在事务的操纵过程中，无论事务处理数据的多少，每一次都锁定一页(8K的Page)，在这个页上的数据不能被其他事务操纵。在SQL Server 7.0 以前，使用的是页级锁。页级锁锁定的资源比行级锁锁定的数据资源多。在页级锁中，即使是一个事务只操纵页上的一行数据，那么该页上的其他数据行也不能被其他事务使用。因此，当使用页级锁时，会出现数据的浪费现象，也就是说，在同一个页上会出现数据被占用却没有使用的现象。在这种现象中，数据的浪费最多不超过一个页上的数据行。 表级锁 表级锁也是一个非常重要的锁。表级锁是指事务在操纵某一个表的数据时，锁定了这个数据所在的整个表，其他事务不能访问该表中的其他数据。当事务处理的数据量比较大时，一般使用表级锁。表级锁的特点是使用比较少的系统资源，但是却占用比较多的数据资源。与行级锁和页级锁相比，表级锁占用的系统资源例如内存比较少，但是占用的数据资源却是最大。在表级锁时，有可能出现数据的大量浪费现象，因为表级锁锁定整个表，那么其他的事务都不能操纵表中的其他数据。

第10章 事务与锁 盘区锁 盘区锁是一种特殊类型的锁，只能用在一些特殊的情况下。是指事务占用一个盘区(盘区（Extent）是物理上连续的8个页 )，这个盘区不能同时被其他事务占用。例如在创建数据库和创建表时，系统分配物理空间时使用这种类型的锁。系统是按照盘区分配空间的。当系统分配空间时，使用盘区锁，防止其他事务同时使用同一个盘区。当系统完成分配空间之后，就不再使用这种类型的盘区锁。特别是，当涉及对数据操作的事务时，不使用盘区锁。 数据库级锁 数据库级锁是指锁定整个数据库，防止任何用户或者事务对锁定的数据库进行访问。数据库级锁是一种非常特殊的锁，它只是用于数据库的恢复操作过程中。这种等级的锁是一种最高等级的锁，因为它控制整个数据库的操作。只要对数据库进行恢复操作，那么就需要设置数据库为单用户模式，这样系统就能防止其他用户对该数据库进行各种操作。

第10章 事务与锁 行级锁是一种最优锁，因为行级锁不可能出现数据既被占用又没有使用的浪费现象。但是，如果用户事务中频繁对某个表中的多条记录操作，将导致对该表的许多记录行都加上了行级锁，数据库系统中锁的数目会急剧增加，这样就加重了系统负荷，影响系统性能。 因此，在 SQL Server 中，还支持锁升级（lock escalation）。所谓锁升级是指调整锁的粒度，将多个低粒度的锁替换成少数的更高粒度的锁，以此来降低系统负荷。在SQL Server 中当一个事务中的锁较多，达到锁升级门限时，系统自动将行级锁和页面锁升级为表级锁。特别值得注意的是，在SQL Server 中，锁的升级门限以及锁升级是由系统自动来确定的，不需要用户设置。

10.2.4 SQL Server 2008 的锁模式 第10章 事务与锁 1. 共享锁 第10章 事务与锁 10.2.4 SQL Server 2008 的锁模式 在 SQL Server 数据库中加锁时，除了可以对不同的资源加锁，还可以使用不同程度的加锁方式，即锁有多种模式，SQL Server 中锁模式包括以下几类。 1. 共享锁 Microsoft SQL Server 中，共享锁用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。默认情况下，数据被读取后，Microsoft SQL Server 立即释放共享锁。 例如，执行查询“SELECT * FROM studinfo”时，首先锁定第一页，读取之后，释放对第一页的锁定，然后锁定第二页。这样，就允许在读操作过程中，修改未被锁定的第一页

第10章 事务与锁 2. 修改锁 修改锁在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。 因为使用共享锁时，修改数据的操作分为两步，首先获得一个共享锁，读取数据，然后将共享锁升级为独占锁，然后再执行修改操作。共享锁升级到独占锁的转换将发生锁等待，因为一个事务的排它锁与其它事务的共享模式锁不兼容。这样如果同时有两个或多个事务同时对一个事务申请了共享锁，在修改数据的时候，这些事务都要将共享锁升级为独占锁。这时，这些事务都不会释放共享锁而是一直等待对方释放，这样就造成了死锁。 要避免死锁，可以使用修改锁。因为一次只有一个事务可以获得资源的修改锁。如果事务修改资源，则修改锁转换为独占 锁。否则，锁转换为共享锁。 3. 独占锁/排他锁 独占锁是为修改数据而保留的。它所锁定的资源，其他事务不能读取也不能修改。独占锁不能和其他锁兼容。

第10章 事务与锁 4. 结构锁 结构锁是指执行表的数据定义语言（DDL）操作（例如添加列或除去表）时使用架构修改（Sch-M）锁。当编译查询时，使用架构稳定性（Sch-S）锁。架构稳定性（Sch-S）锁不阻塞任何事务锁，包括排它锁。因此在编译查询时，其他事务（包括在表上有排它锁的事务）都能继续运行，但不能在表上执行 DDL 操作。 5. 意向锁 意向锁说明 Microsoft SQL Server 有在资源的低层获得共享锁或独占锁的意向。意向锁又可以分为共享意向锁、独占意向锁和共享式独占意向锁。 6. 批量修改锁 批量复制数据时使用批量修改锁。批量修改锁允许进程将数据并发地批量复制到同一表，同时防止其它不进行批量复制数据的进程访问该表。

第10章 事务与锁 10.2.5 死锁问题 在数据库系统中，死锁是指多个用户（进程）分别锁定了一个资源，并又试图请求锁定对方已经锁定的资源，这就产生了一个锁定请求环，导致多个用户（进程）都处于等待对方释放所锁定资源的状态。 在 SQL Server 中，系统能够自动定期搜索和处理死锁问题。系统在每次搜索中标识所有等待锁定请求的进程会话，如果在下一次搜索中该被标识的进程仍处于等待状态，SQL Server就开始递归死锁搜索。 当搜索检测到锁定请求环时，系统将根据各进程会话的死锁优先级别来结束一个优先级最低的事务，此后，系统回滚该事务，并向该进程发出1205 号错误信息。这样，其他事务就有可能继续运行了。死锁优先级的设置语句为： SET DEADLOCK_PRIORITY { LOW | NORMAL} 其中LOW 说明该进程会话的优先级较低，在出现死锁时，可以首先中断该进程的事务。

第10章 事务与锁 另外，各进程中通过设置LOCK_TIMEOUT 选项能够设置进程处于锁定请求状态的最长等待时间。该设置的语句： 第10章 事务与锁 另外，各进程中通过设置LOCK_TIMEOUT 选项能够设置进程处于锁定请求状态的最长等待时间。该设置的语句： SET LOCK_TIMEOUT { timeout_period } 其中，timeout_period 以毫秒为单位。 在应用程序中可以采用下面的一些方法来尽量避免死锁： ① 合理安排表访问顺序。 ② 在事务中尽量避免用户干预，尽量使一个事务处理的任务少些。 ③ 采用脏读技术。 ④ 数据访问时域离散法。 ⑤ 数据存储空间离散法。 ⑥ 使用尽可能低的隔离性级别。 ⑦ 使用 Bound Connections。 ⑧ 考虑使用乐观锁定或使事务首先获得一个独占锁定。