数据保护(事务和锁).ppt

第八章数据保护之事务和锁 8 3事务处理8 4锁 8 3事务处理 一 事务简介1 事务由来2 什么是事务3 事务属性及分类二 管理事务1 隐性事务2 自动提交事务3 显式事务 一 事务简介1 事务由来 使用DELETE命令或UPDATE命令对数据库进行更新时一次只能操作一个表 这会带来数据库的数据不一致的问题 即使已经设置级联删除或修改 计算机内部也是分别先后删除每一个表 例如 在FACTORY数据库中 若取消了 市场部 需要将 市场部 从depart表中删除 要修改depart表 而WORKER表中的部门号与市场部相对应的职工也应删除 因此 两个表都需要修改 这种修改只能通过两条DELETE语句进行 假设市场部编号为 3 第一条DELETE语句修改depart表deletefromdepartwhere部门号 3 第二条DELETE语句修改WORKER表deletefromWORKERwhere部门号 3 在执行第一条DELETE语句后 数据库中的数据已处于不一致的状态 因为此时已经没有 市场部 了 但WORKER表中仍然保存着属于 市场部 的员工记录 只有执行了第二条DELETE语句后数据才重新处于一致状态 但是 如果执行完第一条语句后 计算机突然出现故障 无法再继续执行第二条DELETE语句 则数据库中的数据将处于永远不一致的状态 因此 必须保证这两条DELETE语句同时执行 为解决类似的问题 数据库系统通常都引入了事务 Transaction 的概念 2 什么是事务 事务是单独的逻辑工作单元 也是一个操作序列 该单元中可以包含多个操作以完成一个完整的任务 如果事务成功 在事务中所做的所有的操作都会在提交时完成并且永久地成为数据库的一部分 如果事务遇到错误 则必须取消或回滚 这样所有的操作都将被消除 就象什么也没有执行过一样 事务作为一个整体 只有两种状态 要么成功 要么失败 事务具有ACID属性 原子性 Atomicity 一致性 Consistency 隔离性 Isolation 持续性 Duration 事务可以分为显式事务和自动提交事务 隐式事务 3 事务属性及分类 1 显式事务2 自动提交事务3 隐性事务 二 管理事务 1 显式事务 在显式事务中 事务的语句在BEGINTRANSACTION和COMMITTRANSACTION子句间组成一组 使用下列四条语句来管理事务 1 BEGINTRANSACTION 2 COMMITTRANSACTION 3 ROLLBACKTRANSACTION 4 SAVETRANSACTION 1 BEGINTRANSACTION 标记一个显式本地事务的起始点 SQLServer可使用该语句来开始一个新的事务 语法格式如下 BEGINTRAN SACTION transaction name tran name variable WITHMARK description 注意 任何有效的用户都具有默认的BEGINTRANSACTION权限 2 COMMITTRANSACTION COMMITTRANSACTION标志一个成功的隐性事务或显示事务的结束 语法格式如下 COMMIT TRAN SACTION transaction name tran name variable 注意 事务的提交使得占用的资源被释放 所做的修改在数据库中成为永久有效 3 ROLLBACKTRANSACTION ROLLBACKTRANSACTION将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点 语法格式如下 ROLLBACK TRAN SACTION transaction name tran name variable savepoint name savepoint variable 回滚事务还可以使用ROLLBACKWORK语句 其语法格式为 ROLLBACK WORK 注意 不带savepoint name和transaction name的ROLLBACKTRANSACTION回滚到事务的起点 4 SAVETRANSACTION SAVETRANSACTION是在事务内设置保存点 用户可以在事务内设置保存点或标记 保存点定义如果有条件地取消事务的一部分 事务可以返回的位置 语法格式如下 SAVETRAN SACTION savepoint name savepoint variable 5 事务日志 WITHMARK使得事务名被记录到事务日志中 以便维护数据库的一致性并为恢复提供援助 日志是一片存储区 并自动追踪数据库的所有变化 但非日志运算不记录到日志中 在进行数据更新执行过程中 修改行数据在未写入数据库前 先被记录到日志中 SQLServer采用下列方式应用 前滚 或收回 回滚 每个事务 1 在应用事务日志时 事务将前滚 2 当收回未完成的事务时 事务将回滚 注意 部分语句不能应用于事务处理中 即这些操作对数据库的影响是不能恢复的 其中包括CREATEDATABASE ALTERDATABASE DROPDATABASE RESTOREDATABASE LOADDATABASE BACKUPLOG RESTORELOG UPDATESTATISTICS GRANT DUMPTRANSACTION DISKINIT RECONFIGURE 例1 DECLARE TranNameVARCHAR 20 定义一个局部变量SELECT TranName MyTransaction 给局部变量赋值BEGINTRANSACTION TranName 事务开始GOUSEpubsGOUPDATEroyschedSETroyalty royalty 1 10WHEREtitle idLIKE Pc GO 提交事务COMMITTRANSACTIONMyTransactionGO 例2 设置事务保存点 USEXJGLGOSELECTCOUNT FROMSTUDENT 假如STUDENT中原有22条记录 BEGINTRANMYTRAN1INSERTINTOSTUDENT SNO SNAME VALUES 1000 AAA SELECTCOUNT FROMSTUDENT 几条记录 SAVETRANMYTRAN1INSERTINTOSTUDENT SNO SNAME VALUES 2000 BBB SELECTCOUNT FROMSTUDENT 几条记录 ROLLBACKTRANMYTRAN1COMMITTRANSELECTCOUNT FROMSTUDENT 几条记录 例3 设计一个事务并执行 将课程 数据库原理 和 操作系统的 的课程号互换 BEGINTRANUSEXJGLSELECT FROMCOURSEGODECLARE CNO1CHAR 2 CNO2CHAR 2 SELECT CNO1 CNOFROMCOURSEWHERECNAME 数据库原理 SELECT CNO2 CNOFROMCOURSEWHERECNAME 操作系统 UPDATECOURSESETCNO CNO1WHERECNAME 操作系统 UPDATECOURSESETCNO CNO2WHERECNAME 数据库原理 COMMITTRANSELECT FROMCOURSE USEFACTORYSELECTCOUNT FROMWORKERBEGINTRANINSERTINTOWORKER 职工号 姓名 VALUES 80 AAA SELECTCOUNT FROMWORKERROLLBACKSELECTCOUNT FROMWORKER 2自动提交事务 它是SQLServer的默认事务管理模式 每个Transact SQL语句在完成时 都被提交或回滚 如果一个语句成功地完成 则提交该语句 如果遇到错误 则回滚该语句 只要自动提交模式没有被显式或隐性事务替代 SQLServer连接就以自动提交事务为默认模式进行操作 即SQLServer连接在BEGINTRANSACTION语句启动显式事务模式 在隐性事务模式设置为打开后 启动隐性事务模式 当提交或回滚显式事务 或者关闭隐性事务模式时 SQLServer将返回到自动提交模式 3 隐性事务 隐性事务将在提交或回滚当前事务后自动启动新事务 无须描述事务的开始 只需提交或回滚每个事务 隐性事务模式生成连续的事务链 在SQLServer中 通过SETIMPLICIT TRANSACTIONSON语句将连接设置为隐性事务模式 通过SETIMPLICIT TRANSACTIONSOFF语句将连接设置为返回到自动提交事务模式 隐性事务 在将隐性事务模式设置为打开之后 当SQLServer首次执行下列任何语句时 都会自动启动一个事务 启动事务的SQL语句 8 4 1并发问题8 4 2锁 锁的分类 死锁 8 4数据的锁定 什么是并发问题 并发问题带来什么异常 1 更新丢失2 脏读3 不可重复读4 幻像读 8 4 1并发问题 8 4 1并发问题 1 更新丢失当两个或多个事务选择同一行 然后基于最初选定的值更新该行时 会发生丢失更新问题 每个事务都不知道其它事务的存在 最后的更新将重写由其它事务所做的更新 这将导致数据丢失 a 丢失修改 8 4 1并发问题 2 脏读当第二个事务选择其它事务正在更新的行时 会发生未确认的相关性问题 第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改 c 读 脏 数据 8 4 1并发问题 3 不可重复读当第二个事务多次访问同一行而且每次读取不同的数据时 会发生不一致的分析问题 不一致的分析与未确认的相关性类似 因为其它事务也是正在更改第二个事务正在读取的数据 然而 在不一致的分析中 第二个事务读取的数据是由已进行了更改的事务提交的 而且 不一致的分析涉及多次 两次或更多 读取同一行 而且每次信息都由其它事务更改 因而该行被非重复读取 不可重复读 8 4 1并发问题 4 幻像读当对某行执行插入或删除操作 而该行属于某个事务正在读取的行的范围时 会发生幻像读问题 事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中 因为该行已被其它事务删除 同样 由于其它事务的插入操作 事务的第二次或后续读显示有一行已不存在于原始读中 8 4 2并发控制 1 悲观并发控制2 乐观并发控制3 隔离级别 8 4 2并发控制 1 悲观并发控制 SQLSERVER使用 锁定系统阻止用户以影响其它用户的方式修改数据 如果用户执行的操作导致应用了某个锁 则直到这个锁的所有者释放该锁 其它用户才能执行与该锁冲突的操作 该方法主要用在数据争夺激烈的环境中 以及出现并发冲突时用锁保护数据的成本比回滚事务的成本低的环境中 因此称该方法为悲观并发控制 8 4 2并发控制 2 乐观并发控制在乐观并发控制中 用户读数据时不锁定数据 在执行更新时 系统进行检查 查看另一个用户读过数据后是否更改了数据 如果另一个用户更新了数据 将产生一个错误 一般情况下 接收错误信息的用户将回滚事务并重新开始 该方法主要用在数据争夺少的环境内 以及偶尔回滚事务的成本超过读数据时锁定数据的成本的环境内 因此称该方法为乐观并发控制 8 4 2并发控制 3 隔离级别事务准备接受不一致数据的级别称为隔离级别 隔离级别是一个事务必须与其它事务进行隔离的程度 较低的隔离级别可以增加并发 但代价是降低数据的正确性 相反 较高的隔离级别可以确保数据的正确性 但可能对并发产生负面影响 应用程序要求的隔离级别确定了SQLServer使用的锁定行为 SQLServer支持的隔离级别 未提交读 事务隔离的最低级别 仅可保证不读取物理损坏的数据 提交读 SQLServer默认级别 可重复读 可串行读 事务隔离的最高级别 事务之间完全隔离 8 4 2并发控制 事务隔离级别与并发问题的可能性 8 4 3什么是锁 数据库的锁代表的是对数据库资源的一种权限 SQLSERVER2000有一种共享锁 就是当DBA执行SELECT语句时 对数据需要加一种共享锁 就代表我申请访问数据 SQLSERVER2000有一种更新锁 就是当DBA执行UPDATE语句时 对数据需要加一种更新锁 就代表我申请更新数据 谁来加锁呢 SQLSERVER2000系统本身会根据用户对数据的操作来自动完成 DBA一般不需要手工进行干预 但有时为了设计更有效的应用程序 可以自定义锁 锁的分类 SQLServer可以锁定的资源 SQLServer锁定模式 8 4 3锁的分类 自己看 1 共享锁共享 S 锁允许并发事务读取 SELECT 一个资源 资源上存在共享 S 锁时 任何其它事务都不能修改数据 一旦已经读取数据 便立即释放资源上的共享 S 锁 除非将事务隔离级别设置为可重复读或更高级别 或者在事务生存周期内用锁定提示保留共享 S 锁 2 更新锁更新 U 锁可以防止通常形式的死锁 一般更新模式由一个事务组成 此事务读取记录 获取资源 页或行 的共享 S 锁 然后修改行 此操作要求锁转换为排它 X 锁 如果两个事务获得了资源上的共享模式锁 然后试图同时更新数据 则一个事务尝试将锁转换为排它 X 锁 共享模式到排它锁的转换必须等待一段时间 因为一个事务的排它锁与其它事务的共享模式锁不兼容 发生锁等待 第二个事务试图获取排它 X 锁以进行更新 由于两个事务都要转换为排它 X 锁 并且每个事务都等待另一个事务释放共享模式锁 因此发生死锁 若要避免这种潜在的死锁问题 请使用更新 U 锁 一次只有一个事务可以获得资源的更新 U 锁 如果事务修改资源 则更新 U 锁转换为排它 X 锁 否则 锁转换为共享锁 3 排它锁排它 X 锁可以防止并发事务对资源进行访问 其它事务不能读取或修改排它 X 锁锁定的数据 4 意向锁意向锁表示SQLServer需要在层次结构中的某些底层资源上获取共享 S 锁或排它 X 锁 例如 放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 S 锁 在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 X 锁 意向锁可以提高性能 因为SQLServer仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁 而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表 5 架构锁执行表的数据定义语言 DDL 操作 例如添加列或除去表 时使用架构修改 Sch M 锁 当编译查询时 使用架构稳定性 S