事务处理1.ppt

1、第13章 事务处理,事务的定义,事务的性质-ACID,ACID 原子性A: 事务处理是组成对象的最小单元。要么整体成功；要么整体失败。 一致性C：所有的改变都以一致的方式进行，系统从一个有效状态进入另一个有效状态。 隔离性I：在同一时间进行的事务处理相互独立。 持久性D：如果提交了一个事务处理，产生的效果将是不变的，即使系统失败，也是如此。,事务处理控制语句,BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK TRANSACTION,事务缺陷-脏读,脏读：在事务提交前，它对数据所做的修改对其他事务来说是可见的。是危害最大的事务完整性缺陷。 Set Tra

2、nsaction Isolation Level Read Committed; Use DatabaseName - Transaction-1 Begin Transaction Update Customer Set Nickname=XXX Where CustomerID=1 -Transaction-2 Set Transaction Isolation Level Read UnCommitted Select Nickname From Customer Where CustomerID=1 -Transaction-1 Commit Transaction,事务缺陷-不可重复

3、读,不可重复读：如果隔离性是完全的，一个事务不应看到该事务外的数据修改。 - Transaction-1 Set Transaction Isolation Level Read Committed; Use DatabaseName Begin Transaction Select Nickname From Customer Where CustomerID=1 -Transaction-2 Update Customer Set Nickname=XXX Where CustomerID=1 Commit Transaction -T-2提交后T-1再次检索同一行，如能看到T-2更新后的

4、值，则说明存在不可重复读缺陷。 Select Nickname From Customer Where CustomerID=1 Commit Transaction,事务缺陷-虚幻行,是危害最小的事务完整性缺陷。也是指事务的更新操作不仅影响结果集的数据值，还导致Select语句返回不同的记录集。 - Transaction-2 Begin Transaction Select * From Customer Where Nickname=Missy -Transaction-1 Begin Transaction Update Customer Set Nickname=Missy Wher

5、e CustomerID=1 Commit Transaction -Transaction-2如果隔离性是完全的，T2再次执行同样的Select命令时，应得到与以前相同的记录集。 Select * From Customer Where Nickname=Missy Commit Transaction,隔离级别-1,数据库可通过调整隔离级别来控制允许的事务缺陷。ANSI SQL-92定义了4个隔离级别。SQL Server使用锁来实现隔离级别，默认隔离级别为已提交读。 级别1：未提交读：最不严格的隔离级别，不能防止任何事务缺陷，根本没有在事务之间提供隔离。这种模式只能确保数据不会受损，而不

6、能处理行争用。 级别2：已提交读： SQL Server默认隔离级别。 级别3：可重复读：能够防止脏读和不可重复读。,隔离级别-2,级别4：可序列化：最严格的隔离级别，能够防止所有事务缺陷。这种设置虽然提供了完全的事务隔离性，但将导致严重的锁争用并降低性能。 SQL Server 新增隔离： 快照隔离：它创建要更新的数据的快照拷贝(TempDB)。在更新期间，阅读事务能够继续读取原始数据，更新被提交后，将覆盖原始数据。快照隔离消除了读取事务和写入事务之间的争用。但争用并未完全消失，而是转移到写出事务和写入事务之间。如果第二个写入事务试图更新正在被更新的资源，它将被阻塞。,隔离级别-3,已提交读

7、快照：其行为与已提交读相同，但写入事务和读取事务之间不存在争用。,SQL Server 的锁机制-锁,可以认为SQL Server中有两个进程：查询处理器和锁管理器。 锁管理器的目标是通过创建和释放锁尽可能高效地确保事务完整性。 每个锁都有的三个属性： 粒度(Granularity)：锁的大小 模式(Mode)：锁的类型 持续时间(Duration)：锁的隔离模式,SQL Server 的锁机制-锁粒度,锁控制的数据可以小到一行记录，也可大到整个数据库。根据锁的粒度，可采用不同的锁组合来满足特定的锁定需求。 可加锁的对象：记录、表、页、区域和数据库等。,SQL Server 的锁机制-锁管理器

8、,SQL Server 具有自动和强制锁功能。 SQL Server 的锁管理器试图在锁大小和数量之间寻求平衡，以获得最佳的性能。矛盾的焦点在并发性（较小的锁可以允许更多的事务同时访问数据）和性能（锁越少速度越快）。为达到平衡，锁管理器动态地从一组锁切换为另一组锁。 动态锁的好处： 无需编程，就可自动在性能和并发性之间取得最佳平衡。 随着数据库的增长，锁管理器将不断应用合适的锁粒度，确保数据库保持良好的性能。 动态锁定简化了管理工作。,SQL Server 的锁机制-锁切换,25个行锁可能升级为一个页锁。 然后，如果在同一个区的其他4个页面中包含另外25个被锁定的行，页锁和这25个行锁可能被升

9、级为一个区锁，因为该区中，有超过50%的页面都受到了锁定的影响。 如果有足够的区被锁住，这些锁可能升级为一个表锁。,SQL Server 的锁机制-锁模式/类型-1,锁模式决定了锁的用途。 共享锁S：读取锁，表示“正在查看数据”。若事务T对数据对象X加上共享锁，则其他事务只能对X再加共享锁，不能加排他锁，从而保证了其他事务可以读X，但在T释放X上的锁之前不能对X做任何修改。 排他锁X：写锁，若事务T对数据对象X加上排他锁，则只允许事务T独占数据项X，其他事务都不能对X加任何类型的锁，直到T释放X上的锁，从而避免其他事务读取不一致的数据。 更新锁U：它并不是事务执行更新时使用的锁（此时应使用排他

10、锁）。更新锁意味着事务即将使用排他锁，它当前正在扫描数据，以确定要使用排他锁锁定哪行。用来预定对某对象施加X锁，它允许其他事务读，但不允许再施加U锁和X锁。当被读取的对象要被更新时，升级为X锁。U锁直到事务结束时才能释放。,SQL Server 的锁机制-锁模式/类型-2,意向锁：是一种用于警示的锁，告诉其他事务即将要发生一些事情。主要目的是提高性能。所以类型和粒度都可以使用意向锁。如：IS、SIX、IX。 意向锁是共享锁或派他锁的预先请求，解决了两个性能问题：层次锁定和永久锁阻塞。 SQL Server 使用意向锁向更高的数据层次传播锁。 如：当事务1获得一个行锁后，它将同时对该行所属的页和

11、表放置意向锁。,SQL Server 的锁机制-锁模式/类型-3,通过允许获得锁的事务在更大范围设置意向锁，将检查锁的事务的工作交给了建立锁的事务来完成。这样，虽然建立锁的事务需要同时加上三个锁，但以后其他事务在检查锁时就可以少做数百次的搜索。 意向锁还避免了严重的共享锁争用问题，称为永久锁阻塞。如：事务获得意向排他锁IX后，其他事务就无法获得共享锁。意向排他锁并非完全的排他锁，但它表示即将获得排他锁。,SQL Server 的锁机制-锁模式/类型-2,架构锁（Sch-M, Sch-S） 用于保护数据库架构。 在任何查询执行期间，SQL Server都将使用架构稳定锁（Sch-S）来禁止执行任

12、何数据定义语言（DDL）命令。 仅当SQL Server调整数据库的物理结构时才使用架构修改锁（Sch-M）。如果SQL Server向表中添加列，在该架构修改操作期间，架构锁将禁止其他所有事务查看或修改相应的数据。,SQL Server 的锁机制-锁的兼容性,SQL Server 的锁机制-锁的持续时间,它由事务的隔离级别决定。隔离级别越严，锁的持续时间越长。,监视锁定和阻塞,使用Management Studio的“摘要”-“报表”。 使用活动监视器：“对象资源管理器”-“管理”-“活动监视器”，它每5秒自动刷新一次。 使用Profiler:跟踪事件Error and WarningsBl

13、ocked Process Report 必须启动高级选项并设置Blocked Process Threshold如： sp_Configure show advanced options,1; Go Reconfigure; Go sp_Configure blocked process threshold,1; /*1秒*/ Go Reconfigure;,死锁,死锁发生时， SQL Server 选择完成工作量最少的事务作为死锁牺牲品，以让其他事务继续执行。 事务也可自愿成为死锁牺牲品，使用Set Deadlock Priority LOW 避免死锁应遵循的原则： 事务应简明扼要，不包括任何不应放在事务中的代码。以使事务应尽量小且应尽快提交。 绝不要编写依赖用户输入的事务。 编写批处理和过程时，尽可能让它们以相同的顺序获得锁。 规划物理架构和小心选择聚集索引，确保可能被同时查询的数据存储在数据页尽可能近的地方。减小锁的影响范围有助于防止锁升级，且锁粒度越小，越有助于避免锁争用。 除非确实需要，否则不要提高隔离级别，因为隔离级别越高，锁的持续时间越长