Oracle里未成功commit的数据我们也可能能看到
Posted: January 28, 2012 | Author:
| Filed under:
我们大家都知道的一个常识是——Oracle里未commit的数据除了当前session之外，其他session是看不到的。
我这里演示了一个有趣的例子，在这个例子里我们可以看到，Oracle里未成功commit的数据我们也可能能看到。
我同时启4个session。
先在session 1里创建一个表t1，插入一条数据但不commit：
Session 1:
Connected to Oracle Database 11g Enterprise Edition Release <st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="12" year=".0.1.0
Connected as scott
SQL& create table t1(id number, name varchar2(10));
Table created
SQL& insert into t1 values(1,&#8217;CUIHUA&#8217;);
1 row inserted
SQL& select * from t1;
&&&&&&& ID NAME
&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;-
&&&&&&&& 1 CUIHUA
此时跳到session 2，因session 1里刚插入的那条数据还未commit，所以这个时候session 2是看不到这条数据的：
Session 2:
SQL& select * from t1;
&&&&&&& ID NAME
&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;-
接着我们跳到session 3，把lgwr进程suspend住：
Session 3:
SQL& select spid from v$process where pname=&#8217;LGWR&#8217;;
&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;
SQL& oradebug setospid 2316
Oracle pid: 11, Windows thread id: 2316, image: ORACLE.EXE (LGWR)
SQL& oradebug suspend
已处理的语句
现在我们回到session 1，执行commit命令，因为lgwr进程已经被我们suspend住了，所以当前session 1里要执行的commit操作一定会被hang住：
Session1：
……这里hang住了
Oracle里commit操作的流程是这样的：
1、Oracle先去改这个transaction所对应的undo segment header中slot的状态；
2、改完状态后再flush log buffer；
现在我们把lgwr hold住了，所以上述步骤2 Oracle是没法做了，但步骤1还是可以做的。而只要步骤1做完了，其他的session就能看到这个transaction所做的改变了（通过ITL中记录的transaction id去check相应的undo segment header中slot的状态），也就是说对于其他session而言，这个transaction已经commit了，虽然这个transaction其实并没有成功commit。
好了，我们现在回到session 2，看一下我们现在能否看到刚才insert的那条记录：
Session 2：
SQL& select * from t1;
&&&&&&& ID NAME
&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;-
&&&&&&&& 1 CUIHUA
从结果里可以看到，刚才看不到的那条记录现在我们已经能看到了，即session 1对于session2而言已经commit了，虽然session1的commit操作其实并没有成功的做完。
现在我们跳到session 4，执行shutdown abort：
Session 4：
SQL& shutdown abort
ORACLE 例程已经关闭。
然后我们再在session 4中执行startup，startup后我们从alert log里可以很明显的看到Oracle做了instance recovery：
Sat Jan 28 19:14:03 2012
alter database mount exclusive
Successful mount of redo thread 1, with mount id
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
&parallel recovery started with 2 processes
Started redo scan
Completed redo scan
&read 918 KB redo, 126 data blocks need recovery
Started redo application at
&Thread 1: logseq 44, block 77312
Recovery of Online Redo Log: Thread 1 Group 2 Seq 44 Reading mem 0
& Mem# 0: C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO02.LOG
Completed redo application of 0.52MB
Completed crash recovery at
&Thread 1: logseq 44, block 79149, scn 1652578
&126 data blocks read, 95 data blocks written, 918 redo k-bytes read
等待上述库成功startup后，我们再次回到session 1，看一下刚才我们已经commit的那条数据是否还在：
Session 1：
SQL& select * from t1;
&&&&&&& ID NAME
&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;-
从结果里可以看到，刚才我们insert且执行了commit操作的那条记录现在已经丢失了。查看: 621|回复: 6
为什么必须要commit?
论坛徽章:1
&&一个提交的问题请教一下大家，是什么原因造成了，必须添加commit?
经测试，在我的测试环境数据库中并不需要添加，而到了生产库中就出现了此问题，无论是生产库还是测试库都安装的是Oracle 12.1.0.2.0
EMPCURSOR := GET_DAYS(V_START_DATE, V_END_DATE, V_IS_WEEKEND);
&&IF EMPCURSOR%ISOPEN THEN
& && &FETCH EMPCURSOR
& && &&&INTO REC_EMP;
& && &EXIT WHEN EMPCURSOR%NOTFOUND;
& && &DBMS_OUTPUT.PUT_LINE('' || '==&' || REC_EMP.DAY_OF_WEEK_ON);
& && &DBMS_OUTPUT.PUT_LINE('' || '==&' || REC_EMP.DAY);
& && &V_PRE_USER_ID := 0;
& && &V_COUNT& && & := 0;
& && &FOR V IN (SELECT *
& && && && && && &FROM T_PLAN_AGENCY T
& && && && && &&&WHERE T.PLAN_ID = V_PLAN_ID
& && && && && && & AND T.USER_ID = V_USER_ID
& && && && && && & AND IS_ADD_INSPECTION_RECORDS = 0&&--1 不加commit的话，这里会查询得到我本意已更新为1的记录
& && && && && &&&ORDER BY T.USER_ID) LOOP
& && &&&DBMS_OUTPUT.PUT_LINE('======');
& && &&&IF (V_PRE_USER_ID && V.USER_ID) THEN
& && && & V_COUNT := 0;
& && && & SELECT SNT_INSPECTION_RECORDS.NEXTVAL
& && && && &INTO V_INSPECTION_RECORDS_ID
& && && && &FROM DUAL;
& && && & INSERT INTO T_INSPECTION_RECORDS
& && && && &(INSPECTION_RECORDS_ID,
& && && && & P_INSPECTION_RECORDS_ID,
& && && && & PLAN_ID,
& && && && & PLAN_AGENCY_ID,
& && && && & STANDARD_ID,
& && && && & AGENCY_ID,
& && && && & PLAN_DATE,
& && && && & ACTUAL_DATE,
& && && && & CHECK_STATUS_ID,
& && && && & IS_FILING,
& && && && & STAMP,
& && && && & ISDELETED,
& && && && & UPDATEUSERID,
& && && && & UPDATESTAMP)
& && && & VALUES
& && && && &(V_INSPECTION_RECORDS_ID,
& && && && & 0,
& && && && & V.PLAN_ID,
& && && && & V.PLAN_AGENCY_ID,
& && && && & V_STANDARD_ID,
& && && && & V.AGENCY_ID,
& && && && & REC_EMP.DAY,
& && && && & NULL,
& && && && & 723,
& && && && & 0,
& && && && & SYSDATE,
& && && && & 0,
& && && && & V.USER_ID,
& && && && & SYSDATE);
& && && & V_PRE_USER_ID := V.USER_ID;
& && && & V_COUNT& && & := V_COUNT + 1;
& && &&&ELSE
& && && & IF (V_COUNT & V_AVG_PLAN_AGENCY_COUNT) THEN
& && && && &SELECT SNT_INSPECTION_RECORDS.NEXTVAL
& && && && &&&INTO V_INSPECTION_RECORDS_ID
& && && && &&&FROM DUAL;
& && && && &INSERT INTO T_INSPECTION_RECORDS
& && && && &&&(INSPECTION_RECORDS_ID,
& && && && && &P_INSPECTION_RECORDS_ID,
& && && && && &PLAN_ID,
& && && && && &PLAN_AGENCY_ID,
& && && && && &STANDARD_ID,
& && && && && &AGENCY_ID,
& && && && && &PLAN_DATE,
& && && && && &ACTUAL_DATE,
& && && && && &CHECK_STATUS_ID,
& && && && && &IS_FILING,
& && && && && &STAMP,
& && && && && &ISDELETED,
& && && && && &UPDATEUSERID,
& && && && && &UPDATESTAMP)
& && && && &VALUES
& && && && &&&(V_INSPECTION_RECORDS_ID,
& && && && && &0,
& && && && && &V.PLAN_ID,
& && && && && &V.PLAN_AGENCY_ID,
& && && && && &V_STANDARD_ID,
& && && && && &V.AGENCY_ID,
& && && && && &REC_EMP.DAY,
& && && && && &NULL,
& && && && && &723,
& && && && && &0,
& && && && && &SYSDATE,
& && && && && &0,
& && && && && &V.USER_ID,
& && && && && &SYSDATE);
& && && && &V_PRE_USER_ID := V.USER_ID;
& && && && &V_COUNT& && & := V_COUNT + 1;
& && && & ELSE
& && && && &CONTINUE;
& && && & END IF;
& && &&&END IF;
& && &&&UPDATE T_PLAN_AGENCY
& && && &&&SET IS_ADD_INSPECTION_RECORDS = 1
& && && &WHERE PLAN_AGENCY_ID = V.PLAN_AGENCY_ID;
& && && &COMMIT; --2、为什么这里必须加这一句，否则在后面的语句为还是会取到？ （见红色标号1、3的地方）
& && &&&SELECT AGENCY_NAME
& && && & INTO V_TASKS_NAME
& && && & FROM T_AGENCY T
& && && &WHERE T.AGENCY_ID = V.AGENCY_ID;
& && &&&P_INSERTT_TASKS(V_TASKS_TYPE_ID,
& && && && && && && && &V_TASKS_FROM_ID& && &=& V_INSPECTION_RECORDS_ID,
& && && && && && && && &V_TASKS_NAME& && && &=& V_TASKS_NAME,
& && && && && && && && &V_TASKS_DECS& && && &=& V_TASKS_NAME,
& && && && && && && && &V_TASKS_FROM_USER_ID =& V.USER_ID,
& && && && && && && && &V_TASKS_TO_USER_IDS&&=& V.USER_ID,
& && && && && && && && &V_TASKS_STATUS_ID& & =& 885,
& && && && && && && && &V_UPDATEUSERID& && & =& V.USER_ID,
& && && && && && && && &V_VALIDITY_DATE& && &=& REC_EMP.DAY,
& && && && && && && && &V_WORK_FLOW_DEF_ID& &=& 0,
& && && && && && && && &ORESULT& && && && &&&=& OPRO_ID,
& && && && && && && && &OPRO_DESC& && && && &=& OPRO_DESC);
& && &END LOOP;
& & END LOOP;
&&CLOSE EMPCURSOR;
&&-----本段是对按已有设置还有没有处理的检查单位的处理 开始-------------
&&V_MORE_COUNT := 0;
SELECT COUNT(*)
& & INTO V_MORE_COUNT
& & FROM T_PLAN_AGENCY T
& &WHERE T.PLAN_ID = V_PLAN_ID
& &&&AND T.USER_ID = V_USER_ID
& &&&AND IS_ADD_INSPECTION_RECORDS = 0; --3 如果不加commit的话，我仍然能得到本意已更新为1的记录& && && && && && && && && &&&（应该在一个存储过程中是不用这样去提交的吗？如果在这里提交的话，我不是无法做统一回滚？）
&&IF V_MORE_COUNT & 0 THEN
& & PLAN_AGENCY_COUNT := V_MORE_COUNT;
& & GOTO MORE;
论坛徽章:1
不好意思，发现问题了，是由于调用的那个存储过程中执行了回滚操作。
论坛徽章:393
不好意思，发现问题了，是由于调用的那个存储过程中执行了回滚操作。
按理不应该在存储过程中commit/rollback
论坛徽章:1
按理不应该在存储过程中commit/rollback
是由于里面调用了一个子存储过程P_INSERTT_TASKS，而这个存储过程中使用了回滚，结果就把这一层的也回滚了，如果不在存储过程中回滚的话如果部分出错的话那不是会造成数据问题？
论坛徽章:393
是由于里面调用了一个子存储过程P_INSERTT_TASKS，而这个存储过程中使用了回滚，结果就把这一层的也回滚 ...
只要不commit就不会
论坛徽章:3
放同一个事物里面要commit一起commit，要回滚一起回滚
论坛徽章:1
谢谢各位，找到问题了！
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号14:27 提问
数据库哪些操作需要用到事务（就是说那些语句需要执行commit才会生效）？
是不是数据库，或者说oracle 除了 insert update delete 这几个语句之外，其他的操作写不写commit都没什么所谓
按赞数排序
可以这么理解吧，除了增删改（三者都是对数据的修改），必须保证数据的一致性、正确性；
剩下的查询，没必要提交了。
先吐槽：基本操作除了 insert update delete 就只剩下 select 了，你这个“除了”的范围够大的！
启动了事务（BEGIN TRAN）才需要 COMMIT/ROLLBACK，没 BEGIN 写什么 COMMIT。
至于需不需要事务看多条/多表的数据是不是一个原子。假如只增删改一条记录，本身已经是原子了，不需要事务。
常见的操作除了dml就是ddl对表结构进行操作，ddl都不需要commit，dml对表数据操作除了select语句差不多都要提交
还有drop/truncate都是DDL语句,执行后会自动提交。
有一些是隐式提交的，回滚也没用，比如创一个新的table，其他的只要涉及对数据的增删改都要提交或者回滚吧，不然会造成死锁，在你开启事物的前提下
其他相似问题