第05章 管理事务.ppt

1 第五章管理事务 事务的概念事务的特性事务的类型事务的工作机制锁 2 事务的由来 使用DELETE命令或UPDATE命令对数据库进行更新时 一次只能操作一个表 这会带来数据库的数据不一致的问题 例如企业取消了后勤部 需要将后勤部从department表中删除 要修改department表 而employee表中的部门编号与后勤部相对应的员工也应删除 因此 两个表都需要修改 这种修改只能通过两条DELETE语句进行 假设后勤部编号为1012第一条DELETE语句修改department表deletefromdepartmentwheredept id 1012 第二条DELETE语句修改employee表deletefromemployeewheredept id 1012 3 在执行第一条DELETE语句后 数据库中的数据已处于不一致的状态 因为此时已经没有后勤部了 但employee表中仍然保存着属于后勤部的员工记录 只有执行了第二条DELETE语句后 数据才重新处于一致状态 但是如果执行完第一条语句后 计算机突然出现故障 无法再继续执行第二条DELETE语句 则数据库中的数据将处于永远不一致的状态 因此必须保证这两条DELETE语句同时执行 为解决类似的问题 数据库系统通常都引入了事务Transaction的概念 4 事务的概念及特性 事务 指作为单个逻辑工作单元执行的一系列操作 而这些逻辑工作单元需要具有原子性 一致性 隔离性和持久性四个属性 统称ACID特性 原子性 指事务必须是原子工作单元 即对于事务所进行数据修改 要么全都执行 要么全都不执行 5 一致性 指事务在完成时 必须使所有的数据都保持一致性状态 而且在相关数据库中 所有规则都必须应用于事务的修改 以保持所有数据的完整性 事务结束时 所有的内部数据结构都必须是正确的 6 隔离性 指由并发事务所做的修改必须与任何其他并发事务所做的修改相隔离 事务查看数据时数据所处的状态 要么是被另一并发事务修改之前的状态 要么是被另一事务修改之后的状态 即事务不会查看正在由另一个并发事务正在修改的数据 持久性 指事务完成之后 它对于系统的影响是永久性的 即使出现系统故障也是如此 7 事务的类型 隐式事务 每次执行SQLServer的任何数据修改语句时 它都是一个隐式事务 例如 下列SQL语句是一个独立事务 Inserttalbevalues 1 abc Updatetablesetcol1 5wherecol1 1Deletefromtable1wherecol1 5go 8 显式事务 通常在程序中用BEGINTRANSACTION命令来标识一个事务的开始 用COMMITTRANSACTION命令标识事务结束 这两个命令之间的所有语句被视为一体 只有执行到COMMITTRANSACTION命令时 事务中对数据库的更新操作才算确认 和BEGIN END命令类似 这两个命令也可以进行嵌套 即事务可以嵌套执行 9 这两个命令的语法如下BEGINTRAN SACTION transaction name tran name variable COMMIT TRAN SACTION transaction name tran name variable 其中BEGINTRANSACTION可以缩写为BEGINTRAN COMMITTRANSACTION可以缩写为COMMITTRAN或COMMIT transaction name 指定事务的名称 只有前32个字符会被系统识别 tran name variable 用变量来指定事务的名称变量 只能声明为CHARVARCHARNCHAR或NVARCHAR类型 10 例 删除后勤部declare transaction namevarchar 32 select transaction name my transaction delete begintransaction transaction namegousesamplegodeletefromdepartmentwheredept id 1012 godeletefromemployeewheredept id 1012 gocommittransactionmy transaction deletego 11 事务回滚 事务回滚 TransactionRollback 是指当事务中的某一语句执行失败时 将对数据库的操作恢复到事务执行前或某个指定位置 事务回滚使用ROLLBACKTRANSACTION命令 其语法如下ROLLBACK TRAN SACTION transaction name tran name variable savepoint name savepoint variable 其中savepoint name和 savepoint variable参数用于指定回滚到某一指定位置 如果要让事务回滚到指定位置 则需要在事务中设定保存点SavePoint 12 所谓保存点是指定其所在位置之前的事务语句不能回滚的语句 即此语句前面的操作被视为有效 其语法如下 SAVETRAN SACTION savepoint name savepoint variable 各参数说明如下 savepoint name 指定保存点的名称 同事务的名称一样 只有前32个字符会被系统识别 savepoint variable 用变量来指定保存点的名称变量 只能声明为CHAR VARCHAR NCHAR或NVARCHAR类型 13 例 删除后勤部再将后勤部的职工划归到经理室begintransactionmy transaction deleteusesamplegodeletefromdepartmentwheredept id 1012 savetransactionafter deleteupdateemployeesetdept id 1001 wheredept id 1012 if error 0or rowcount 0thenbeginrollbacktranafter delete 回滚到保存点after delete如果使用rollbackmy transaction delete则会回滚到事务开始前 Committranprint 更新员工信息表时产生错误 Returnendcommittransactionmy transaction deletego 14 事务工作机制 例如 begintranInserttable1values 1 abc Updatetable1setcol1 5wherecol1 1Deletefromtable1wherecol1 5Committran 15 1 当begintran语句到达数据库时 sqlserver分析出这是事务的开始 SQLserver找到下一个可用的内存日志页面 并给新事务分配一个事务ID 2 接着运行插入语句 新的行被记录到事务日志中 数据页面在内存中进行修改 若所需页面不在内存中 则从磁盘调出 3 update语句以类似方式运行 4 当sqlserver收到comminttran时 日志页面被写道数据库的日志设备上 这样才能保证日志页面可被恢复 由于日志变化写入了硬盘 它保证事务是可恢复的 即使掉电了或在数据页写入磁盘时数据库崩溃了 也能进行事务恢复 16 检查点处理 问题 系统何时将数据页写入磁盘呢 日志页面是在committran时写入磁盘的 那么何时将数据页面写入磁盘呢 在处理检查点时 检查点的概念 是sqlserver将数据页面从内存拷贝到磁盘时的内部处理点 检查点帮助确保恢复已提交事务而不会花费额外的时间 当检查点出现时 将写入一个日志项以指示所有修改了的页面已写入磁盘 17 锁 锁的概念 锁 Lock 是在多用户环境下对资源访问的一种限制机制 当对一个数据源加锁后 此数据源就有了一定的访问限制 我们就称对此数据源进行了锁定 在SQLServer中可以对以下的对象进行锁定 数据行 数据页中的单行数据索引行 索引页中的单行数据即索引的键值页 页是SQLServer存取数据的基本单位其大小为8KB盘区 一个盘区由8个连续的页组成表数据库 18 锁的类别 在SQLServer中 从数据库系统的角度来看 锁分为以下三种类型 独占锁ExclusiveLock 独占锁锁定的资源只允许进行锁定操作的程序使用 其它任何对它的操作均不会被接受 执行数据更新命令 即INSERT UPDATE或DELETE命令时SQLServer会自动使用独占锁 但当对象上有其它锁存在时 无法对其加独占锁 独占锁一直到事务结束才能被释放 19 共享锁SharedLock 共享锁锁定的资源可以被其它用户读取 但其它用户不能修改它 在SELECT命令执行时 SQLServer通常会对对象进行共享锁锁定 通常加共享锁的数据页被读取完毕后 共享锁就会立即被释放更新锁UpdateLock 更新锁是为了防止死锁而设立的 当SQLServer准备更新数据时 它首先对数据对象作更新锁锁定 这样数据将不能被修改 但可以读取 等到SQLServer确定要进行更新数据操作时 它会自动将更新锁换为独占锁 但当对象上有其它锁存在时 无法对其作更新锁锁定 20 查看锁 可以通过企业管理器或存储过程来查看锁用EnterpriseManager查看锁 在企业管理器中选择目录树窗口中 管理 文件夹下 当前活动 中的锁 进程ID节点 则可以查看当前锁定的进程 选择同级的锁 对象节点下的相应字节点 则可以查看当前锁定的对象 若右键单击任务板窗口中的对象 从快捷菜单中选择属性选项 则会出现锁的进程细节对话框 在此可以刷新或杀死锁的进程 杀死进程还可以用如下Transact SQL命令来进行KILLspidspid是SystemProcessID 即系统进程编号的缩写 21 用系统存储过程Sp lock查看锁存储过程Sp lock的语法如下sp lockspidSQLServer的进程编号spid可以在master dbo sysprocesses系统表中查到 spid是INT类型的数据 如果不指定spid则显示所有的锁 例 显示当前系统中所有的锁usemasterexecsp lock例 usemasterexecsp lock52 22 死锁及其防止 死锁是在多用户或多进程状况下 为使用同一资源而产生的无法解决的争用状态 通俗地讲 就是两个用户各占用一个资源 两人都想使用对方的资源但同时又不愿放弃自己的资源 就一直等待对方放弃资源 如果不进行外部干涉就将一直耗下去 死锁会造成资源的大量浪费 甚至会使系统崩溃 在SQLServer中解决死锁的原则是牺牲一个比