《管理事务》PPT课件.ppt

第五章 管理事务 w事务的概念 w事务的特性 w事务的类型 w事务的工作机制 w锁 1 事务的由来 w使用DELETE 命令或UPDATE 命令对数据库进行更 新时，一次只能操作一个表，这会带来数据库的数据 不一致的问题。例如企业取消了后勤部，需要将后勤 部从department表中删除，要修改department 表， 而employee 表中的部门编号与后勤部相对应的员工 也应删除。因此，两个表都需要修改，这种修改只能 通过两条DELETE 语句进行。假设后勤部编号为 1012 w第一条DELETE 语句修改department 表 wdelete from department wwhere dept_id = 1012 w第二条DELETE 语句修改employee 表 wdelete from employee where dept_id = 1012 2 w在执行第一条DELETE 语句后，数据库中的数 据已处于不一致的状态。因为此时已经没有后 勤部了，但employee 表中仍然保存着属于后 勤部的员工记录；只有执行了第二条DELETE 语句后，数据才重新处于一致状态。但是如果 执行完第一条语句后，计算机突然出现故障， 无法再继续执行第二条DELETE 语句，则数据 库中的数据将处于永远不一致的状态。因此必 须保证这两条DELETE 语句同时执行。为解决 类似的问题，数据库系统通常都引入了事务 Transaction 的概念。 3 事务的概念及特性 w事务：指作为单个逻辑工作单元执行的 一系列操作，而这些逻辑工作单元需要 具有原子性，一致性，隔离性和持久性 四个属性，统称ACID特性。 w原子性：指事务必须是原子工作单元， 即对于事务所进行数据修改，要么全都 执行，要么全都不执行 4 w一致性：指事务在完成时，必须使所有 的数据都保持一致性状态，而且在相关 数据库中，所有规则都必须应用于事务 的修改，以保持所有数据的完整性。事 务结束时，所有的内部数据结构都必须 是正确的。 5 w隔离性：指由并发事务所做的修改必须与 任何其他并发事务所做的修改相隔离。事 务查看数据时数据所处的状态，要么是被 另一并发事务修改之前的状态，要么是被 另一事务修改之后的状态，即事务不会查 看正在由另一个并发事务正在修改的数据 。 w持久性：指事务完成之后，它对于系统的 影响是永久性的，即使出现系统故障也是 如此。 6 事务的类型 w隐式事务 ：每次执行SQL Server的任何 数据修改语句时，它都是一个隐式事务。 例如：下列SQL语句是一个独立事务： wInsert talbe values(1,abc) wUpdate table set col1=5 where col1=1 wDelete from table1 where col1=5 wgo 7 显式事务 w通常在程序中用BEGIN TRANSACTION 命令来标识 一个事务的开始，用COMMIT TRANSACTION 命令 标识事务结束；这两个命令之间的所有语句被视 为一体。只有执行到COMMIT TRANSACTION 命令 时，事务中对数据库的更新操作才算确认。和 BEGINEND 命令类似。这两个命令也可以进行 嵌套，即事务可以嵌套执行。 8 w这两个命令的语法如下 wBEGIN TRANSACTION transaction_name | tran_name_variable wCOMMIT TRANSACTION transaction_name | tran_name_variable w其中BEGIN TRANSACTION 可以缩写为BEGIN TRAN 。COMMIT TRANSACTION可以缩写为COMMIT TRAN 或COMMIT 。 wtransaction_name：指定事务的名称，只有前32 个字 符会被系统识别。 wtran_name_variable：用变量来指定事务的名称变量 。只能声明为CHAR VARCHAR NCHAR 或 NVARCHAR 类型 9 w例： 删除后勤部 wdeclare transaction_name varchar(32) wselect transaction_name = my_transaction_delete wbegin transaction transaction_name wgo wuse sample wgo wdelete from department where dept_id = 1012 wgo wdelete from employee where dept_id = 1012 wgo wcommit transaction my_transaction_delete wgo 10 事务回滚 w事务回滚(Transaction Rollback) 是指当事务中 的某一语句执行失败时,将对数据库的操作恢复到 事务执行前或某个指定位置。 w事务回滚使用ROLLBACK TRANSACTION 命令 ，其语法如下 wROLLBACK TRANSACTION transaction_name | tran_name_variable w| savepoint_name | savepoint_variable w其中savepoint_name 和savepoint_variable 参数用于指定回滚到某一指定位置。 w如果要让事务回滚到指定位置，则需要在事务中设 定保存点Save Point 。 11 w所谓保存点是指定其所在位置之前的事务语句 不能回滚的语句，即此语句前面的操作被视为 有效。 w其语法如下： wSAVE TRANSACTION savepoint_name | savepoint_variable w各参数说明如下： wsavepoint_name：指定保存点的名称。同事 务的名称一样，只有前32 个字符会被系统识 别。 wsavepoint_variable：用变量来指定保存点 的名称变量，只能声明为CHAR、VARCHAR 、 NCHAR 或NVARCHAR 类型。 12 w例： 删除后勤部再将后勤部的职工划归到经理室 wbegin transaction my_transaction_delete wuse sample wgo wdelete from department where dept_id = 1012 wsave transaction after_delete wupdate employee set dept_id = 1001 where dept_id = 1012 wif error!=0 or rowcount=0 then wbegin w rollback tran after_delete /* 回滚到保存点after_delete w 如果使用rollback my_transaction_delete 则会回滚到事务开始前 */ w Commit tran w print 更新员工信息表时产生错误 w Return wend wcommit transaction my_transaction_delete wgo 13 事务工作机制 w例如：begin tran wInsert table1 values(1,abc) wUpdate table1 set col1=5 where col1=1 wDelete from table1 where col1=5 wCommit tran 14 w1、当begin tran语句到达数据库时，sql server 分析出这是事务的开始，SQL server找到下一个 可用的内存日志页面，并给新事务分配一个事务 ID。 w2、接着运行插入语句，新的行被记录到事务日志 中，数据页面在内存中进行修改，若所需页面不在 内存中，则从磁盘调出。 w3、update语句以类似方式运行。 w4、当sql server 收到commint tran时，日志页 面被写道数据库的日志设备上，这样才能保证日志 页面可被恢复。由于日志变化写入了硬盘，它保证 事务是可恢复的，即使掉电了或在数据页写入磁盘 时数据库崩溃了，也能进行事务恢复。 15 检查点处理 w问题：系统何时将数据页写入磁盘呢？ w日志页面是在commit tran时写入磁盘 的，那么何时将数据页面写入磁盘呢？在 处理检查点时。 w检查点的概念：是sql server将数据页面 从内存拷贝到磁盘时的内部处理点。 w检查点帮助确保恢复已提交事务而不会花 费额外的时间。当检查点出现时，将写入 一个日志项以指示所有修改了的页面已写 入磁盘。 16 锁-锁的概念 w锁（Lock) 是在多用户环境下对资源访问的一 种限制机制。当对一个数据源加锁后，此数据 源就有了一定的访问限制，我们就称对此数据 源进行了锁定。在SQL Server中可以对以下 的对象进行锁定: w数据行：数据页中的单行数据 w索引行：索引页中的单行数据即索引的键值 w页：页是SQL Server 存取数据的基本单位其 大小为8KB w盘区：一个盘区由8 个连续的页组成 w表 w数据库 17 锁的类别 w在SQL Server 中， 从数据库系统的角度 来看，锁分为以下三种类型： w独占锁Exclusive Lock：独占锁锁定的资 源只允许进行锁定操作的程序使用，其它 任何对它的操作均不会被接受。执行数据 更新命令，即INSERT、UPDATE 或 DELETE 命令时SQL Server 会自动使用 独占锁。但当对象上有其它锁存在时，无 法对其加独占锁，独占锁一直到事务结束 才能被释放。 18 w共享锁Shared Lock：共享锁锁定的资源可以 被其它用户读取，但其它用户不能修改它。在 SELECT 命令执行时，SQL Server 通常会对 对象进行共享锁锁定，通常加共享锁的数据页 被读取完毕后，共享锁就会立即被释放 w更新锁Update Lock：更新锁是为了防止死锁 而设立的。当SQL Server 准备更新数据时， 它首先对数据对象作更新锁锁定，这样数据将 不能被修改，但可以读取。等到SQL Server 确定要进行更新数据操作时，它会自动将更新 锁换为独占锁。但当对象上有其它锁存在时， 无法对其作更新锁锁定。 19 查看锁 w可以通过企业管理器或存储过程来查看锁 w用Enterprise Manager 查看锁：在企业管理器中 选择目录树窗口中“管理” 文件夹下 “当前活动” 中的锁 /进程 ID 节点，则可以查看当前锁定的进程。选择同级的 锁 /对象节点下的相应字节点，则可以查看当前锁定的对 象。若右键单击任务板窗口中的对象，从快捷菜单中选择 属性选项，则会出现锁的进程细节对话框，在此可以刷新 或杀死锁的进程。 w杀死进程还可以用如下Transact-SQL 命令来进行 wKILL spid wspid 是System Process ID， 即系统进程编号的缩写。 20 w用系统存储过程Sp_lock 查看锁 w存储过程Sp_lock 的语法如下 wsp_lock spid wSQL Server 的进程编号spid 可以在 master.dbo.sysprocesses 系统表中查到，spid 是INT 类型的数据，如果不指定spid 则显示所有的锁。 w例： 显示当前系统中所有的锁 wuse master wexec sp_lock w例：use master wexec sp_lock 52 21 死锁及其防止 w死锁 是在多用户或多进程状况下，为使用同一资源而产生的无法 解决的争用状态。通俗地讲，就是两个用户各占用一个资源，两 人都想使用对方的资源但同时又不愿放弃自己的资源，就一直等 待对方放弃资源。如果不进行外部干涉就将一直耗下去。死锁会 造成资源的大量浪费，甚至会使系统崩溃。在SQL Server 中解 决死锁的原