Oracle11g第06讲事务及锁.ppt

1、事务,目标,通过本章学习，您将可以: 使用 DML 语句 向表中插入数据 更新表中数据 从表中删除数据 将表中数据和并 控制事务,事务概念,包含一组数据库命令，构成单一逻辑工作单元的操作集合 访问并可能更新各种数据项的一个程序执行单元，是不可分割的工作逻辑单元 执行并发操作的最小控制单位,事务的特性,原子性（AAtomicity） 一致性（CConsistency） 隔离性（IIsolation) 持久性（DDurability）,原子性、一致性,原子性:就是事务应作为一个工作单元,事务处理完成，所有的工作要么都在数据库中保存下来，要么完全回滚，全部不保留 一致性: 事务完成或者撤销后，都应该

2、处于一致的状态,隔离性、永久性,隔离性 多个事务同时进行，它们之间应该互不干扰.应该防止一个事务处理其他事务也要修改的数据时，不合理的存取和不完整的读取数据 永久性 事务提交以后，所做的工作就被永久的保存下来,事务并发处理会产生的问题,丢失更新 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题、每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。,丢失更新：,假设产品的当前库存60，现有两个事务：T1购入400个，T2卖出40,正常执行过程：,假设产品的当前库存60，现有两个事务：T1购入400个，T2卖出40,脏读 当第二个事

3、务选择其它事务正在更新的行时，会发生未确认的相关性问题。 第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。 ,T2使用T1未提交数据：,假设产品的当前库存60，现有两个事务：T1购入400个，在没有提交前撤消了该操作，T2卖出40,正常执行过程：,假设产品的当前库存60，现有两个事务：T1购入400个，在没有提交前撤消了该操作，T2卖出40,脏读,会话A Update emp set sal=2000 where ename=SCOTT; 会话B Select sal from emp where ename=SCOTT;,数据库事务,数据库事务由以下的部分组成: 一个或多个

4、DML 语句 一个 DDL 语句 一个 DCL 语句,数据库事务,以第一个 DML 语句的执行作为开始 以下面的其中之一作为结束: COMMIT 或 ROLLBACK 语句 DDL 或 DCL 语句（自动提交） 用户会话正常结束 系统异常终了,打开事务的自动提交,show autocommit set autocommit on;,Commit,事务提交命令。 在Oracle中，在内存中将为每个客户机建立工作区，客户机对数据库进行操作处理的事务都在工作区内完成，只有在输入commit命令后，工作区内的修改内容才写入到数据库上，称为物理写入. 这样可以保证在任意的客户机没有物理提交修改以前，别的

5、客户机读取的后台数据库中的数据是完整的、一致的.,COMMIT和ROLLBACK语句的优点,使用COMMIT 和 ROLLBACK语句,我们可以: 确保数据完整性。 数据改变被提交之前预览。 将逻辑上相关的操作分组。 当执行了Commit语句之后，会确认事务变化、结束事务、删除保存点、释放锁。当使用commit语句后，其他会话将可以看到事务变化后的新数据,ORACLE事务控制-回退段,ORACLE为了适应事务控制设置了回退段这一数据库对象.系统利用回退段来确保诸如读一致性、数据库恢复等管理功能。 ORACLE在缺省情况下,读数据不加锁,通过回退段(Rollback Segment)保证用户不读

6、脏数据和可重复读. 表空间中的数据按段来组织,数据段、索引段、暂存段和回退段，回退段是一块磁盘存储区域，回退段可以由用户创建，但只能由系统进程使用。,ORACLE事务控制-回退段,事务的执行过程（采用日志和回退段双重记录事务活动）：,进入回退段,写入回退信息,从数据段读入缓冲区,SQL处理,记载日志文件,提交,写更改结果到磁盘,回退,写回退信息到磁盘,事务第一条更新语句,事务结束,N,Y,Update t set col=0 where col=123;commit;,数据库首先把该语句的整个操作包括数据0与123写入日志缓冲区 然后把123和一些信息写入回滚段， 最后把0修改到数据缓冲区。

7、当发出提交命令时，如果日志缓冲区内容没有写入日志文件则必须写入日志文件，回滚段把该事务标记为已经提交，数据缓冲区中的这个事务也标记为已提交 如果回退这个事务，则数据库将回滚段中123读出写回数据缓冲区，这个回退变化也被写入日志文件。,自动提交事务,当执行DDL语句时会自动提交事务， 当执行DCL语句（grant revoke） 当退出SQL*Plus时,将某一事务设为只读事务,Set transaction read only 只读事务，不生成回滚信息，在整个事务中就不能有修改操作 注意是针对当前事务，不是另一个事务 Set transaction read only | read write

8、 通过rollback取消设定。,只读事务,只读事务只允许执行查询操作，而不允许执行任何DML操作事务。 当使用只读事务可以确保取得特定的时间点的数据。 例如： 假定企业需要在每天16点统计最近24小时的销售信息，而不统计当天16点之后的销售信息，那么用户可以使用只读事务。 在设置了只读事务之后，尽管其他事务可能会提交新事务，但只读事务不会取得新的数据变化。,示例：,会话A:Set transaction read only; 会话B:Update emp set sal=3000 where ename=SMITH; 会话A:Select sal from emp where ename=S

9、MITH; Set transaction isolation level serializable; 顺序事务，在只读事务的基础特性上还可以对数据进行DML操作,控制事务,ROLLBACK to SAVEPOINT B,ROLLBACK to SAVEPOINT A,ROLLBACK,UPDATE. SAVEPOINT update_done; Savepoint created. INSERT. ROLLBACK TO update_done; Rollback complete.,回滚到保留点,使用 SAVEPOINT SAVEPOINTname语句在当前事务中创建保存点。 使用 ROL

10、LBACK TO SAVEPOINTname 语句回滚到创建的保存点。 用于取消部分事务,自动提交在以下情况中执行: DDL 语句。 DCL 语句。 不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。 会话异常结束或系统异常会导致自动回滚。,事务进程,提交或回滚前的数据状态,改变前的数据状态是可以恢复的 其他用户不能看到当前用户所做的改变，直到当前用户结束事务。 DML语句所涉及到的行被锁定， 其他用户不能操作。,提交后的数据状态,数据的改变已经被保存到数据库中。 改变前的数据已经丢失。 所有用户可以看到结果。 锁被释放， 其他用户可以操作涉及到的数据。 所有保存点被释

11、放。,COMMIT; Commit complete.,改变数据 提交改变,DELETE FROM employees WHERE employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, Corporate Tax, NULL, 1700); 1 row inserted.,提交数据,数据回滚后的状态,使用 ROLLBACK 语句可使数据变化失效: 数据改变被取消。 修改前的数据状态可以被恢复。 锁被释放。,DELETE FROM copy_emp; 22 rows deleted. ROLLBACK;

12、Rollback complete.,语句级回滚,单独 DML 语句执行失败时，只有该语句被回滚。 Oracle 服务器自动创建一个隐式的保留点。 其他数据改变仍被保留。 用户应执行 COMMIT 或 ROLLBACK 语句结束事务。,锁,Oracle 数据库中，锁是 : 并行事务中避免资源竞争。 避免用户动作。 自动使用最低级别的限制。 在事务结束结束前存在。 两种类型: 显示和隐式。,锁,两种模式: 独占锁: 屏蔽其他用户。 共享锁: 允许其他用户操作。 高级别的数据并发性: DML: 表共享，行独占 Queries（查询）: 不需要加锁 DDL: 保护对象定义 提交或回滚后锁被释放。,排

13、它锁：若事务T对数据D加X锁（独占锁），则其它任何事务都不能再对D加任何类型的锁，直至T释放D上的X锁；一般要求在修改数据前要向该数据加排它锁，所以排它锁又称为写锁。 共享锁：若事务T对数据D加S锁（共享锁），则其它事务只能对D加S锁，而不能加X锁，直至T释放D上的S锁；一般要求在读取数据前要向该数据加共享锁，所以共享锁又称为读锁。,加锁方法,自动加锁 INSERT UPDATE DELETE 人工加锁 SELECT FOR UPDATE OF LOCK TABLE IN 锁类型MODE,基本的锁类型有两种： 排它锁Exclusive locks记为X锁 共享锁Share locks记为S锁

14、Oracle DML锁共有两个层次，即行级锁和表级锁。 Oracle的TX锁（行级锁、事务锁） TM锁（表级锁）,Oracle的DML锁（数据锁） 其行级锁虽然只有一种（即X锁）， 但其TM锁（表级锁）类型共有5种， 共享锁（S锁）、排它锁（X锁）、行级共享锁（RS 锁）、行级排它锁（RX锁）、共享行级排它锁（SRX锁）， 注意:Oracle在行级只提供 X锁，所以与RS锁（通过SELECT FOR UPDATE语句获得）对应的行级锁也是X锁（但是该行数据实际上还没有被修改）。,当Oracle执行SELECTFOR UPDATE、INSERT、UPDATE、DELETE等DML语句时，系统自动

15、在所要操作的表上申请表级RS锁（SELECTFOR UPDATE）或RX锁（INSERT、UPDATE、DELETE），当表级锁获得后，系统再自动申请TX锁，并将实际锁定的数据行的锁标志位置位（指向该TX锁）； 也可以通过LOCK TABLE语句来指定获得某种类型的TM锁。下表总结了Oracle中各SQL语句产生TM锁的情况：,Oracle中各SQL语句产生TM锁的情况：,自动加锁,Oracle自动加锁有4种类型 数据锁 行级锁数据锁是防止多个事务对同一个表或表中同一行操作时产生的冲突 当事务执行以下DML语句，INSERT UPDATE DELETE SELECT FOR UPDATE OF

16、. 表级锁当事务获得行锁后，此事务也自动获得表级共享锁，以防止其他事务进行DDL语句同样可以使用LOCK TABLE人工定义表级共享锁,Lock table table_name in row exclusive mode行加锁加重服务器的负担 Lock table table_name in exclusive mode 锁定命令可以一次锁定多个表Lock table tab1,tab2 in exclusive mode 锁类型： Row share、row exclusive、share update Share、share row exclusive、exclusive,封锁机制的监控

17、,v$lock视图列出当前系统持有的或正在申请的所有锁的情况，其主要字段说明如下： v$locked_object视图列出当前系统中哪些对象正被锁定,查看锁等待的进程,查看当前的用户会话和对应的锁信息 Select s.sid,s.serial#,s.username,s.status,l.id1,l.lmode,l.request from v$session s,v$lock l where s.sid=l.sid and s.username is not null 动态性能试图v$session 查看造成锁等待的锁信息 Select l.id1,l.lmode,l.request fr

18、om v$session s,v$lock l where s.lockwait=l.kaddr 删除无效的会话进程 Alter system kill session sid,serial#;,死锁,会话1：SQL SELECT * FROM TEST FOR UPDATE; 会话2：SQL SELECT * FROM TEMP FOR UPDATE; 再回到会话1：SQL SELECT * FROM TEMP FOR UPDATE; . 处于等待状态 再回到会话2：SQL SELECT * FROM TEST FOR UPDATE;. 处于等待状态 ERROR 位于第1行：ORA-00060: 等待资源时检测到死锁,关于锁冲突的解决方案,通过SQL语句检测死锁：Select oracle_username,os_user_name,session_id,locked_mode from v$locked_object;如果有结果显示，表示有数据锁存在。 查询到该锁对应的表：Select owner| | object,type from v$access where sid=?是从上面查询得到的的session_id 查询该锁的sql语句：Select user_name,sid,sql_text from v$o