资源简介

(共29张PPT)
8
事务与锁
第
章
本章导读
事务与锁是SQL Server提供的数据完整性保护机制。事务能够使实现某个功能的所有代码完整地执行，从而保证数据的完整性；锁能够对多个事务的执行进行并发控制，从而保证数据的一致性。
本章将介绍事务与锁的基本概念，以及使用T-SQL命令对它们进行相关操作的方法。
知识目标
理解事务与锁的基本概念。
能力目标
能够使用T-SQL创建事务。
能够使用T-SQL加锁。
素质目标
培养逻辑思维能力。
增强自主学习、探究学习的意识。
项目导航
事 务
1
锁
2
01
事务
8.1.1初识事务——事务的特征1事务的特征有如下4种，它们统称为事务的ACID准则。（1）原子性（atomicity）。事务是数据库的逻辑工作单位，每个事务都是一个不可分割的整体，事务在执行时对于所有的操作要么都做，要么都不做。（2）一致性（consistency）。事务执行完成时必须使数据库从一个一致的状态变为另一个一致的状态。（3）隔离性（isolation）。一个事务的执行不受其他事务干扰，也就是说，一个事务的内部操作及使用的数据对其他并发事务是隔离的。（4）持久性（durability）。一个事务一旦提交，它对数据库中数据的改变应该是持久的，即使数据库出现故障，也不会丢失提交事务的操作。事务是数据库系统中执行代码的最小工作单位，它是用户定义的一组操作序列。——事务的类型2（1）自动提交事务。自动提交事务是数据库引擎的默认模式，每条单独语句都是一个事务，不必指定语句来控制事务。（2）隐式事务。隐式事务是指使用SET IMPLICIT_TRANSACTIONS ON语句不明显地定义事务开始，使用COMMIT或ROLLBACK语句明显地定义事务结束的事务。（3）显式事务。显示事务是指使用BEGIN TRANSACTION语句明显地定义事务开始，使用COMMIT或ROLLBACK语句明显地定义事务结束的事务。8.1.1初识事务8.1.2管理事务——管理事务的T-SQL命令有：BEGIN TRANSACTION，表示开始事务；COMMIT TRANSACTION，表示提交事务，即将事务中所有对数据库的更新写到磁盘的物理数据库中，事务正常结束；ROLLBACK TRANSACTION，表示回滚事务，即当事务运行过程中发生了某种故障导致其不能继续执行时，系统会将事务中对数据库所有已完成的操作全部撤销，回滚到事务开始时的状态；SAVE TRANSACTION，表示保存事务，以便事务回滚时能够撤销到保存点。知识库事务与锁的相关操作主要使用T-SQL实现，故本章主要介绍使用T-SQL对它们进行操作的方法。——【例 8-1】在数据库scs中执行事务，向数据表student插入数据，同时限定最多只能插入5条记录。若数据表student中的记录总数不超过5条，显示“插入数据成功！”；反之，则显示“插入数据失败！”，并取消插入操作。步骤1 查询数据表student当前的数据，可见已有5条记录，如图8-1所示。SELECT * FROM student图8-1数据表student的数据8.1.2管理事务——步骤2 执行事务，向已有5条记录的数据表student插入数据，结果如图8-2所示。BEGIN TRANSACTIONINSERT INTO student VALUES('12004','张三','男','自动化',20)DECLARE @studentCount INTSELECT @studentCount = (SELECT COUNT(*) FROM student)IF @studentCount > 5BEGINROLLBACK TRANSACTIONPRINT '插入数据失败！'ENDELSEBEGINCOMMIT TRANSACTIONPRINT '插入数据成功！'END8.1.2管理事务——图8-2事务执行结果这段T-SQL代码中使用BEGIN TRANSACTION定义事务的开始，向数据表student中插入一条数据，同时查询数据表student中的总记录数。当总记录数大于5时，事务执行失败，使用ROLLBACK TRANSACTION命令撤销所有操作，并显示“插入数据失败！”；当总记录数不大于5时，事务继续执行，并显示“插入数据成功！”。8.1.2管理事务——课堂总结① 初识事务② 管理事务
02
锁
8.2.1初识锁——一般情况下，数据库是可以供多个用户同时使用的，甚至有些类型的数据库需要供上万名用户在同一时刻使用。例如，铁路票务系统中，同一时刻并发运行的事务可能多达成百上千个。各个事务并发运行时，如果不加以控制可能会造成数据不一致的情况。下面通过实例进行说明。设有一铁路订票系统，两处售票点同时卖出了同一趟列车的车票，具体过程如下。（1）甲售票处（设事务T1）读出某趟列车的车票余额C为100张，记为C=100。（2）乙售票处（设事务T2）读出同一趟列车的车票余额C也为100张，记为C=100。（3）甲售票处卖出一张车票，修改余额C←C 1，即C=99，将C写回数据库。（4）乙售票处也卖出一张车票，也修改余额C←C 1，即C=99，将C写回数据库。这样的操作会造成数据的不一致性，因为售票处实际上卖出了两张车票，而余额却只减少了一张。这种不一致性就是因为没有进行并发控制，对于T1、T2两个事务的操作顺序是随机的，导致事务T1的修改操作丢失了。8.2.1初识锁——知识库并发操作带来的不一致性主要有如下3种。（1）丢失更新（lost update）。例如，上例中的事务T1与T2，因为T2的提交破坏了T1提交的结果，导致T1的修改丢失。（2）脏读（dirty read）。例如，事务T1更新了数据A后，事务T2读取了更新后的数据A，但事务T1因某种原因撤销，同时撤销更新操作，这时，事务T2得到的数据与数据库中的内容不一致，这种情况就称为脏读。（3）不可重复读（non-repeatable read）。例如，事务T1读取数据A后，事务T2读取数据A并将其修改为数据B，使得事务T1再次读取该数据时得到了数据B，这种情况就称为不可重复读。8.2.2锁的类型——1．排他锁排他锁又称写锁，简称X锁。排他锁常用于数据修改操作，如INSERT、UPDATE及DELETE，它能够确保事务不会同时对同一数据进行多次更新。【例 8-2】创建事务tr1与tr2，使用事务tr1更新数据并添加排他锁，同时将事务tr1延时10秒，执行事务tr1后立即执行事务tr2查询事务tr1所锁定的记录，查看执行情况。步骤1 在数据库scs上打开两个查询编辑窗口，在第一个查询编辑窗口中输入如下代码，创建事务tr1。BEGIN TRANSACTION tr1UPDATE student WITH(TABLOCKX) SET age=19 WHERE sn='冯明'WAITFOR DELAY '00:00:10'COMMIT TRANSACTION步骤2 在第二个查询编辑窗口中输入如下代码，创建事务tr2。BEGIN TRANSACTION tr2SELECT * FROM student WHERE sn='冯明'COMMIT TRANSACTION——步骤3 切换至事务tr1的查询编辑窗口，执行代码，此时“结果”窗口如图8-3所示。图8-3事务tr1执行时的“结果”窗口高手点拨事务tr1的代码中，WAITFO表示指定语句、存储过程或事务执行的时间或时间间隔；DELAY表示时间间隔，即其后的代码将在过了设定的时间间隔后再继续执行；WITH(TABLOCKX)表示添加排他锁。此时事务tr1执行更新操作并将记录锁定，同时因为延时代码，事务将在10秒后结束，所以该记录将锁定10秒。8.2.2锁的类型1．排他锁——步骤4 在事务tr1还未执行完成时，切换至事务tr2的查询编辑窗口，执行代码，此时“结果”窗口与事务tr1的“结果”窗口相同，处于等待状态。步骤5 10秒后，事务tr1执行结束，切换至事务tr1的查询编辑窗口，更新操作已成功执行，“消息”窗口如图8-4所示。图8-4事务tr1执行结束后的“消息”窗口8.2.2锁的类型1．排他锁——步骤6 事务tr1的锁定随之解除，事务tr2得以继续执行，切换至事务tr2的查询编辑窗口，“结果”窗口如图8-5所示。图8-5事务tr2执行结束后的“结果”窗口高手点拨由图8-4与图8-5中“结果”窗口右下角的执行时间可以看出，两个事务的完成时间存在时间差，该时间差为步骤4与步骤5之间的间隔时间。也就是说，该时间差为事务tr1开始执行并进入等待状态后，再开始执行事务tr2的时间差，具体差值取决于读者的手动操作间隔。8.2.2锁的类型1．排他锁8.2.2锁的类型——2．共享锁共享锁又称读锁，简称S锁。共享锁能够保护数据不被改写，还允许多个事务同时读取数据。【例 8-3】创建事务tr1与tr2，使用事务tr1查询数据并添加共享锁，同时将事务tr1延时10秒，执行事务tr1后立即执行事务tr2查询事务tr1所锁定的记录，查看执行情况。步骤1 在数据库scs上打开两个查询编辑窗口，在第一个查询编辑窗口中输入如下代码，创建事务tr1。BEGIN TRANSACTION tr1SELECT sno,sn,age FROM student WITH(HOLDLOCK) WHERE sn='冯明'WAITFOR DELAY '00:00:10'COMMIT TRANSACTION提示WITH(HOLDLOCK)表示添加共享锁。——步骤2 在第二个查询编辑窗口中输入如下代码，创建事务tr2。BEGIN TRANSACTION tr2SELECT * FROM student WHERE sn='冯明'COMMIT TRANSACTION步骤3 切换至事务tr1的查询编辑窗口，执行代码，此时“结果”窗口如图8-6所示。图8-6事务tr1执行结束时的“结果”窗口步骤4 在事务tr1还未执行完成时，切换至事务tr2的查询编辑窗口，执行代码，事务tr2可正常执行，此时“结果”窗口如图8-7所示。图8-7事务tr2执行结束后的“结果”窗口8.2.2锁的类型2．共享锁——步骤5 10秒后，事务tr1执行结束，切换至事务tr1的查询编辑窗口，“结果”窗口如图8-8所示。图8-8事务tr1执行结束后的“结果”窗口高手点拨当事务tr2为更新操作时（如修改为如下代码），则执行事务tr1后立即执行事务tr2将出现例8-2的情况，即事务tr2同样进入等待状态，必须等到事务tr1执行结束后再继续执行。BEGIN TRANSACTION tr2UPDATE student SET age=20 WHERE sn='冯明'COMMIT TRANSACTION这是因为共享锁锁定时允许其他事务查询，但不允许改写。8.2.2锁的类型2．共享锁8.2.2锁的类型——3．其他锁（1）更新锁简称U锁，可以防止多个事务在读取、锁定及更新资源时出现死锁的情况。当事务修改数据时，更新锁会转化为排他锁，其他情况则转化为共享锁。（2）意向锁主要用于保护排他锁和共享锁结构底层的资源。（3）架构锁能够防止对数据表的并发访问，在它释放之前，其他操作都将被阻止。知识库锁也可以应用在一条记录或是一个数据表上。锁定记录（行锁）的命令为ROWLOCK，锁定数据表（表锁）的命令为TABLOCK。8.2.3封锁协议——运用排他锁和共享锁对数据对象封锁时，还需要考虑一定的封锁规则，如封锁时长、何时开始封锁、何时结束封锁等，这些规则称为封锁协议。常用的封锁协议有如下3种级别。（1）一级封锁协议。一级封锁协议是指事务T在修改数据A之前必须对A添加排他锁，直到事务结束。任何企图更新A的事务都必须先添加排他锁，如果未获得排他锁，那么这个事务将进入等待状态，直到获得排他锁才能继续执行事务。（2）二级封锁协议。二级封锁协议是指在一级封锁协议的基础上，要求事务T在读取数据B之前必须先对B添加共享锁，在读取之后释放该共享锁。（3）三级封锁协议。三级封锁协议是指在一级封锁协议的基础上，要求事务T读取数据B之前必须先对B添加共享锁，直到事务结束后释放。——这3种级别的封锁协议的主要区别在于什么操作申请什么封锁，以及何时释放封锁。它们之间的区别和一致性保证如表8-1所示。级别排他锁共享锁一致性保证操作结束释放事务结束释放操作结束释放事务结束释放不丢失更新不脏读数据可重复读取一级封锁协议√√二级封锁协议√√√√三级封锁协议√√√√√表8-1不同级别的封锁协议和一致性保证8.2.3封锁协议8.2.4死锁——死锁的概念1如果事务T1封锁了数据B，事务T2封锁了数据C，然后T1又请求封锁C，但此时C已被T2锁住，T1则必须等待T2释放C。接着，T2又请求封锁B，但此时B已被T1锁住，T2必须等待T1释放B，这时就会造成死锁，即两个事务无限期地等待下去。形成死锁有如下4个必要条件。（1）请求与保持条件，获取资源的进程能够同时申请新的资源。（2）非剥夺条件，已经获取的资源不会被其他进程剥夺。（3）循环等待条件，多个进程能够构成环路，并且每个进程都在等待相邻进程正占用的资源。（4）互斥条件，资源只能被一个进程使用。——死锁的诊断2（1）超时法是指当一个事务的等待时间超过了规定的时限，就认为发生了死锁。超时法实现起来相对简单，但其不足也非常明显，一是可能误判死锁，二是设定的时间过长时不能及时发现死锁。（2）等待图法的“等待图”是指一个有向图的集合，设G=（T，U），T为结点的集合，每个结点表示正在运行的事务；U表示边的集合，每条边表示事务等待的情况。例如，T1等待T2，T2也在等待T1，形成了死锁，它们的等待图如图8-9所示。等待图记录了所有事务的等待情况，当发现其中存在回路时，即表示系统中出现了死锁。图8-9等待图高手点拨数据库管理系统中发现了死锁之后需要将其解除，通常采用的方法是撤销一个能够解除死锁的代价最小的事务，即释放其持有的所有锁，使其他事务能够继续运行。同时，对撤销的事务所执行的数据操作必须加以恢复。8.2.4死锁——减少死锁的策略3（1）在所有事务中以相同的次序使用资源。（2）事务尽可能简短，并在同一批处理中。（3）为死锁设置一个合适的超时时间，当等待时间过长时自动取消，避免进程一直保持挂起状态。（4）避免在事务内与用户交互。（5）每个事务尽量一次性将所有需要使用的数据全部加锁，否则不执行。（6）不进行强行加锁。8.2.4死锁——课堂小结① 初识锁② 锁的类型③ 封锁协议④ 死锁

展开更多......

收起↑