《oracle事务.ppt》由会员分享，可在线阅读，更多相关《oracle事务.ppt（48页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、oracle事务事务2-50目标目标通过本章学习，您将可以通过本章学习，您将可以:使用使用 DML 语句语句向表中插入数据向表中插入数据更新表中数据更新表中数据从表中删除数据从表中删除数据将表中数据和并将表中数据和并控制事务控制事务3-50事务概念事务概念包含一组数据库命令，构成单一逻辑工作包含一组数据库命令，构成单一逻辑工作单元的操作集合单元的操作集合访问并可能更新各种数据项的一个程序执访问并可能更新各种数据项的一个程序执行单元，是不可分割的工作逻辑单元行单元，是不可分割的工作逻辑单元执行并发操作的最小控制单位执行并发操作的最小控制单位4-50事务的特性事务的特性原子性（原子性（AAtomi
2、city）一致性（一致性（CConsistency）隔离性（隔离性（IIsolation)持久性（持久性（DDurability）5-50原子性、原子性、一致性一致性原子性原子性: :就是事务应作为一个工作单元就是事务应作为一个工作单元, ,事务处理完成事务处理完成，所有的工作要么都在数据库中保存下来，要，所有的工作要么都在数据库中保存下来，要么完全回滚，全部不保留么完全回滚，全部不保留一致性一致性: :事务完成或者撤销后，都应该处于一致的状态事务完成或者撤销后，都应该处于一致的状态6-50隔离性、永久性隔离性、永久性隔离性隔离性多个事务同时进行，它们之间应该互不干扰多个事务同时进行，它们之间
3、应该互不干扰. .应该防止一个事务处理其他事务也要修改的数应该防止一个事务处理其他事务也要修改的数据时，不合理的存取和不完整的读取数据据时，不合理的存取和不完整的读取数据永久性永久性事务提交以后，所做的工作就被永久的保存下事务提交以后，所做的工作就被永久的保存下来来7-50事务并发处理会产生的问题事务并发处理会产生的问题 丢失更新丢失更新当两个或多个事务选择同一行，然后基于最初当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题、选定的值更新该行时，会发生丢失更新问题、每个事务都不知道其它事务的存在。最后的更每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所
4、做的更新，这将导致数新将重写由其它事务所做的更新，这将导致数据丢失。据丢失。8-50丢失更新：丢失更新：假设产品的当前库存假设产品的当前库存60，现有两个事务：，现有两个事务：T1购入购入400个，个，T2卖出卖出40顺序顺序事务事务步骤步骤存储的值存储的值1T1读在库数量读在库数量602T2读在库数量读在库数量603T1在库数量在库数量=60+4004T2在库数量在库数量=60-405T1写在库数量写在库数量460（将被丢失）（将被丢失）6T2写在库数量写在库数量209-50正常执行过程：正常执行过程：假设产品的当前库存假设产品的当前库存60，现有两个事务：，现有两个事务：T1购入购入400
5、个，个，T2卖出卖出40顺序顺序事务事务步骤步骤存储的值存储的值1T1读在库数量读在库数量602T1在库数量在库数量=60+4003T1写在库数量写在库数量4604T2读在库数量读在库数量4605T2在库数量在库数量=460-406T2写在库数量写在库数量42010-50脏读脏读 当第二个事务选择其它事务正在更新的行时，当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。会发生未确认的相关性问题。 第二个事务正在读取的数据还没有确认并且可第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。能由更新此行的事务所更改。11-50T2使用使用T1未提交数据：未提交数据：假
6、设产品的当前库存假设产品的当前库存60，现有两个事务：，现有两个事务：T1购入购入400个，在没有提交前撤消了该操作，个，在没有提交前撤消了该操作，T2卖出卖出40顺序顺序事务事务步骤步骤存储的值存储的值1T1读在库数量读在库数量602T1在库数量在库数量=60+4003T1写在库数量写在库数量4604T2读在库数量读在库数量460（读未提交数据）（读未提交数据）5T2在库数量在库数量=460-406T1rollback606T2写在库数量写在库数量42012-50正常执行过程：正常执行过程：假设产品的当前库存假设产品的当前库存60，现有两个事务：，现有两个事务：T1购入购入400个，在没有提
7、交前撤消了该操作，个，在没有提交前撤消了该操作，T2卖出卖出40顺序顺序事务事务步骤步骤存储的值存储的值1T1读在库数量读在库数量602T1在库数量在库数量=60+4003T1写在库数量写在库数量4604T1RollBack605T2读在库数量读在库数量606T2在库数量在库数量=60-407T2写在库数量写在库数量2013-50脏读脏读会话会话AUpdate emp set sal=2000 where ename=SCOTT;会话会话BSelect sal from emp where ename=scott;14-50数据库事务数据库事务数据库事务由以下的部分组成数据库事务由以下的部分组
8、成:一个或多个一个或多个DML 语句语句一个一个 DDL 语句语句一个一个 DCL 语句语句15-50数据库事务数据库事务以第一个以第一个 DML 语句的执行作为开始语句的执行作为开始以下面的其中之一作为结束以下面的其中之一作为结束:COMMIT 或或 ROLLBACK 语句语句DDL 或或 DCL 语句（自动提交）语句（自动提交）用户会话正常结束用户会话正常结束系统异常终了系统异常终了16-50show autocommitset autocommit on;17-50Commit事务提交命令。事务提交命令。在在OracleOracle中，在内存中将为每个客户机建立工作区，客中，在内存中将为
9、每个客户机建立工作区，客户机对数据库进行操作处理的事务都在工作区内完成，户机对数据库进行操作处理的事务都在工作区内完成，只有在输入只有在输入commitcommit命令后，工作区内的修改内容才写入命令后，工作区内的修改内容才写入到数据库上，称为物理写入到数据库上，称为物理写入. .这样可以保证在任意的客户机没有物理提交修改以前，这样可以保证在任意的客户机没有物理提交修改以前，别的客户机读取的后台数据库中的数据是完整的、一致别的客户机读取的后台数据库中的数据是完整的、一致的的. .18-50COMMIT和和ROLLBACK语句的优点语句的优点使用使用COMMIT 和和 ROLLBACK语句语句,
10、我们可以我们可以: 确保数据完整性。确保数据完整性。数据改变被提交之前预览。数据改变被提交之前预览。将逻辑上相关的操作分组。将逻辑上相关的操作分组。当执行了当执行了Commit语句之后，会确认事务变化、结束事语句之后，会确认事务变化、结束事务、删除保存点、释放锁。当使用务、删除保存点、释放锁。当使用commit语句后，其语句后，其他会话将可以看到事务变化后的新数据他会话将可以看到事务变化后的新数据19-50ORACLEORACLE事务控制事务控制- -回退段回退段ORACLE为了适应事务控制设置了为了适应事务控制设置了回退段这一数据库对象这一数据库对象.系统利用回退段来确保诸如读一致性、数据库
11、恢复等管理系统利用回退段来确保诸如读一致性、数据库恢复等管理功能。功能。ORACLE在缺省情况下在缺省情况下,读数据不加锁读数据不加锁,通过回退段通过回退段(Rollback Segment)保证用户不读脏数据和可重复读保证用户不读脏数据和可重复读.表空间中的数据按段来组织表空间中的数据按段来组织,数据段、索引段、暂存段和回数据段、索引段、暂存段和回退段，回退段是一块磁盘存储区域，退段，回退段是一块磁盘存储区域，回退段可以由用户创建，但只能由系统进程使用。，但只能由系统进程使用。20-50ORACLEORACLE事务控制事务控制- -回退段回退段事务的执行过程（采用日志和回退段双重记录事务活动
12、）：事务的执行过程（采用日志和回退段双重记录事务活动）：进入回退段进入回退段, ,写入回退信息写入回退信息从数据段读入缓冲区从数据段读入缓冲区, ,SQLSQL处理处理记载日志文件记载日志文件提交提交, ,写更改结果到磁盘写更改结果到磁盘 回退回退, ,写回退信息到磁盘写回退信息到磁盘 事务第一条更新语句事务第一条更新语句事务结束事务结束NY21-50Update t set col=0 where col=123;commit;数据库首先把该语句的整个操作包括数据数据库首先把该语句的整个操作包括数据0与与123写入日志缓冲区写入日志缓冲区然后把然后把123和一些信息写入回滚段，和一些信息写入
13、回滚段，最后把最后把0修改到数据缓冲区。修改到数据缓冲区。当发出提交命令时，如果日志缓冲区内容没有写入日志当发出提交命令时，如果日志缓冲区内容没有写入日志文件则必须写入日志文件，回滚段把该事务标记为已经文件则必须写入日志文件，回滚段把该事务标记为已经提交，数据缓冲区中的这个事务也标记为已提交提交，数据缓冲区中的这个事务也标记为已提交如果回退这个事务，则数据库将回滚段中如果回退这个事务，则数据库将回滚段中123读出写回读出写回数据缓冲区，这个回退变化也被写入日志文件。数据缓冲区，这个回退变化也被写入日志文件。22-50自动提交事务自动提交事务当执行当执行DDL语句时会自动提交事务，语句时会自动提
14、交事务，当执行当执行DCL语句（语句（grant revoke）当退出当退出SQL*Plus时时23-50将某一事务设为只读事务将某一事务设为只读事务Set transaction read only只读事务，不生成回滚信息，在整个事务中就不能有修只读事务，不生成回滚信息，在整个事务中就不能有修改操作改操作注意是针对当前事务，不是另一个事务注意是针对当前事务，不是另一个事务Set transaction read only | read write通过通过rollback取消设定。取消设定。24-50只读事务只读事务只读事务只允许执行查询操作，而不允许执行任何只读事务只允许执行查询操作，而不允
15、许执行任何DML操作事务。操作事务。当使用只读事务可以确保取得特定的时间点的数据。当使用只读事务可以确保取得特定的时间点的数据。例如：例如：假定企业需要在每天假定企业需要在每天16点统计最近点统计最近24小时的销售信息，小时的销售信息，而不统计当天而不统计当天16点之后的销售信息，那么用户可以使用点之后的销售信息，那么用户可以使用只读事务。只读事务。在设置了只读事务之后，尽管其他事务可能会提交新事在设置了只读事务之后，尽管其他事务可能会提交新事务，但只读事务不会取得新的数据变化。务，但只读事务不会取得新的数据变化。25-50示例：示例：会话会话A:Set transaction read on
16、ly;会话会话B:Update emp set sal=3000 where ename=SMITH;会话会话A:Select sal from emp where ename=SMITH;Set transaction isolation level serializable;顺序事务，在只读事务的基础特性上还可以对数据进行顺序事务，在只读事务的基础特性上还可以对数据进行DML操作操作26-50控制事务控制事务保存点保存点 B保存点保存点 ADELETEINSERTUPDATEINSERTCOMMITTime 事务事务ROLLBACK to SAVEPOINT BROLLBACK to SA
17、VEPOINT AROLLBACK27-50UPDATE.SAVEPOINT update_done;INSERT.ROLLBACK TO update_done;回滚到保留点回滚到保留点使用使用 SAVEPOINT SAVEPOINTname语句在当前事务中语句在当前事务中创建保存点。创建保存点。使用使用 ROLLBACK TO SAVEPOINTname 语句回滚到创建语句回滚到创建的保存点。的保存点。用于取消部分事务用于取消部分事务28-50自动提交在以下情况中执行自动提交在以下情况中执行:DDL 语句。语句。DCL 语句。语句。不使用不使用 COMMIT 或或 ROLLBACK 语句提
18、交或回滚，正常结语句提交或回滚，正常结束会话。束会话。会话异常结束或系统异常会导致自动回滚。会话异常结束或系统异常会导致自动回滚。事务进程事务进程29-50提交或回滚前的数据状态提交或回滚前的数据状态改变前的数据状态是可以恢复的改变前的数据状态是可以恢复的其他用户不能看到当前用户所做的改变，直到当前用户结束事其他用户不能看到当前用户所做的改变，直到当前用户结束事务。务。DML语句所涉及到的行被锁定，语句所涉及到的行被锁定， 其他用户不能操作。其他用户不能操作。30-50提交后的数据状态提交后的数据状态数据的改变已经被保存到数据库中。数据的改变已经被保存到数据库中。改变前的数据已经丢失。改变前的
19、数据已经丢失。所有用户可以看到结果。所有用户可以看到结果。锁被释放，锁被释放， 其他用户可以操作涉及到的数据。其他用户可以操作涉及到的数据。所有保存点被释放。所有保存点被释放。31-50COMMIT;改变数据改变数据提交改变提交改变DELETE FROM employeesINSERT INTO departments VALUES (290, Corporate Tax, NULL, 1700);提交数据提交数据32-50数据回滚后的状态数据回滚后的状态使用使用 ROLLBACK 语句可使数据变化失效语句可使数据变化失效:数据改变被取消。数据改变被取消。修改前的数据状态可以被恢复。修改前的数
20、据状态可以被恢复。锁被释放。锁被释放。DELETE FROM copy_emp;ROLLBACK;33-50语句级回滚语句级回滚单独单独 DML 语句执行失败时，只有该语句被回滚。语句执行失败时，只有该语句被回滚。Oracle 服务器自动创建一个隐式的保留点。服务器自动创建一个隐式的保留点。其他数据改变仍被保留。其他数据改变仍被保留。用户应执行用户应执行 COMMIT 或或 ROLLBACK 语句结束事务。语句结束事务。34-50锁锁Oracle 数据库中，锁是数据库中，锁是 :并行事务中避免资源竞争。并行事务中避免资源竞争。避免用户动作。避免用户动作。自动使用最低级别的限制。自动使用最低级别
21、的限制。在事务结束结束前存在。在事务结束结束前存在。两种类型两种类型: 显示和隐式。显示和隐式。35-50锁锁两种模式两种模式:独占锁独占锁: 屏蔽其他用户。屏蔽其他用户。共享锁共享锁: 允许其他用户操作。允许其他用户操作。高级别的数据并发性高级别的数据并发性:DML: 表共享，行独占表共享，行独占Queries: 不需要加锁不需要加锁DDL: 保护对象定义保护对象定义提交或回滚后锁被释放。提交或回滚后锁被释放。36-50排它锁：若事务排它锁：若事务T T对数据对数据D D加加X X锁，则其它任何锁，则其它任何事务都不能再对事务都不能再对D D加任何类型的锁，直至加任何类型的锁，直至T T释放
22、释放D D上的上的X X锁；一般要求在修改数据前要向该数据锁；一般要求在修改数据前要向该数据加排它锁，所以排它锁又称为写锁。加排它锁，所以排它锁又称为写锁。共享锁：若事务共享锁：若事务T T对数据对数据D D加加S S锁，则其它事务锁，则其它事务只能对只能对D D加加S S锁，而不能加锁，而不能加X X锁，直至锁，直至T T释放释放D D上上的的S S锁；一般要求在读取数据前要向该数据加锁；一般要求在读取数据前要向该数据加共享锁，所以共享锁又称为读锁。共享锁，所以共享锁又称为读锁。37-50加锁方法加锁方法自动加锁自动加锁INSERT UPDATE DELETE人工加锁人工加锁SELECT F
23、OR UPDATE OFLOCK TABLE IN 锁类型锁类型MODE38-50基本的锁类型有两种：基本的锁类型有两种：排它锁排它锁 Exclusive locksExclusive locks记为记为X X锁锁共享锁共享锁 Share locksShare locks记为记为S S锁锁Oracle DMLOracle DML锁共有两个层次，即行级锁和表级锁。锁共有两个层次，即行级锁和表级锁。 Oracle的的TX锁（行级锁、事务锁）锁（行级锁、事务锁）TM锁（表级锁）锁（表级锁）39-50OracleOracle的的DMLDML锁（数据锁）锁（数据锁）其行级锁虽然只有一种（即其行级锁虽然只
24、有一种（即X X锁），锁），但其但其TMTM锁（表级锁）类型共有锁（表级锁）类型共有5 5种，种， 共享锁（共享锁（S S锁）、排它锁（锁）、排它锁（X X锁）、行级共享锁锁）、行级共享锁（RS RS 锁）、行级排它锁（锁）、行级排它锁（RXRX锁）、共享行级排锁）、共享行级排它锁（它锁（SRXSRX锁），锁）， 注意注意: :OracleOracle在行级只提供在行级只提供 X X锁，所以与锁，所以与RSRS锁（锁（通过通过SELECT FOR UPDATESELECT FOR UPDATE语句获得）对应的语句获得）对应的行级锁也是行级锁也是X X锁（但是该行数据实际上还没有被锁（但是该行数
25、据实际上还没有被修改）。修改）。40-50当当OracleOracle执行执行SELECTFOR UPDATESELECTFOR UPDATE、INSERTINSERT、UPDATEUPDATE、DELETEDELETE等等DMLDML语句时，系统自动在所语句时，系统自动在所要操作的表上申请表级要操作的表上申请表级RSRS锁（锁（SELECTFOR SELECTFOR UPDATEUPDATE）或或RXRX锁（锁（INSERTINSERT、UPDATEUPDATE、DELETEDELETE），当表级锁获得后，系统再自动申请当表级锁获得后，系统再自动申请TXTX锁，并锁，并将实际锁定的数据行的锁
26、标志位置位（指向该将实际锁定的数据行的锁标志位置位（指向该TXTX锁）；锁）；也可以通过也可以通过LOCK TABLELOCK TABLE语句来指定获得某种类语句来指定获得某种类型的型的TMTM锁。下表总结了锁。下表总结了OracleOracle中各中各SQLSQL语句产语句产生生TMTM锁的情况：锁的情况：41-50OracleOracle中各中各SQLSQL语句产生语句产生TMTM锁的情况：锁的情况：42-50自动加锁自动加锁Oracle自动加锁有自动加锁有4种类型种类型数据锁数据锁行级锁行级锁数据锁是防止多个事务对同一个表或表中同一行操作时数据锁是防止多个事务对同一个表或表中同一行操作时
27、产生的冲突产生的冲突当事务执行以下当事务执行以下DML语句，语句，INSERT UPDATE DELETE SELECT FOR UPDATE OF.表级锁表级锁当事务获得行锁后，此事务也自动获得表级共享锁，以当事务获得行锁后，此事务也自动获得表级共享锁，以防止其他事务进行防止其他事务进行DDL语句语句同样可以使用同样可以使用LOCK TABLE人工定义表级共享锁人工定义表级共享锁43-50Lock table table_name in row exclusive mode行加锁加重服务器的负担行加锁加重服务器的负担Lock table table_name in exclusive mod
28、e锁定命令可以一次锁定多个表锁定命令可以一次锁定多个表Lock table tab1,tab2 in exclusive mode锁类型：锁类型：Row share、row exclusive、share updateShare、share row exclusive、exclusive44-50封锁机制的监控封锁机制的监控v$lockv$lock视图列出当前系统持有的或正在申请的所有锁的情视图列出当前系统持有的或正在申请的所有锁的情况，其主要字段说明如下：况，其主要字段说明如下：v$locked_objectv$locked_object视图列出当前系统中哪些对象正被锁定视图列出当前系统中哪
29、些对象正被锁定45-50查看锁等待的进程查看锁等待的进程查看当前的用户会话和对应的锁信息查看当前的用户会话和对应的锁信息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 from v$session s,v$lock l whe
30、re s.lockwait=l.kaddr删除无效的会话进程删除无效的会话进程Alter system kill session sid,serial#;46-50死锁死锁会话会话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行
31、：行：ORA-00060: 等待资源时检测到死锁等待资源时检测到死锁47-50关于锁冲突的解决方案关于锁冲突的解决方案通过通过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查询该
32、锁的查询该锁的sql语句：语句：Select user_name,sid,sql_text from v$open_cursor where sid=?获得获得sid,serial#号：号：select s.sid,s.serial# from v$session s,v$lock l where s.sid=l.sid and s.username is not null强行删除引起锁的会话：强行删除引起锁的会话：Alter session kill session sid.serial#;48-50总结总结功能功能插入插入修正修正删除删除合并合并提交提交保存点保存点回滚回滚语句语句INSERTUPDATEDELETEMERGECOMMITSAVEPOINTROLLBACK通过本章学习通过本章学习, 您应学会如何使用您应学会如何使用DML语句改变数据和事务语句改变数据和事务控制控制