Oracle事务学习笔记

最新推荐文章于 2022-03-17 16:00:34 发布

Tiwen

最新推荐文章于 2022-03-17 16:00:34 发布

阅读量2.4k

点赞数

分类专栏：数据库文章标签： oracle insert integer session table application

本文链接：https://blog.csdn.net/tiwen818/article/details/7044049

版权

数据库专栏收录该内容

29 篇文章 0 订阅

订阅专栏

ORACLE事务学习笔记

有关事务的语句

commit, rollback, savepoint, settransaction

drop table t2 purge;

create table t2 (x int);

drop table t3 purge;

create table t3 (x int check(x>0));

create or replace trigger t_trigger

before insert or delete on t3 for each row

declare

begin

if(inserting) then

update t2 set x=x+1;

else

update t2 set x=x-1;

end if;

dbms_output.put_line(sql%rowcount||'条记录已经被触发并且更新');

endt_trigger;

insert into t2 values(1);

insert into t3 values(1);

--1条记录已经被触发并且更新

insert into t3 values(-1);

--1条记录已经被触发并且更新

select * from t2

----------

我们发现即使insert into t3 values(-1);失败，触发器也被触发一次，但是被回滚了

如果在触发器中增加一个提交

insert into t3 values(-1);

insert into t3 values(-1)

ORA-04092: COMMIT 不能在触发器中

ORA-06512: 在"SCOTT.T_TRIGGER", line 10

ORA-04088: 触发器 'SCOTT.T_TRIGGER' 执行过程中出错；

如果一定要在触发器中记录操作，则可以采用自主事务

create or replace trigger t_trigger

before insert or delete on t3 for each row

declare

pragma autonomous_transaction;

begin

if(inserting) then

update t2 set x=x+1;

else

update t2 set x=x-1;

end if;

dbms_output.put_line(sql%rowcount||'条记录已经被触发并且更新');

commit;

end t_trigger;

insert into t3 values(-1)

select * from t2

-----------

如果改变触发器before为after又如何？

Commits时，到底发生了什么？

Commit之前的操作

在内存SGA产生了回滚段

在内存SGA产生了修改数据块

在内存SGA产生重做

取决于前面3项的大小与消耗的时间，一部分数据已经刷新到磁盘

已经获得所有的锁定

commit;---------

为系统产生一个系统更改好SCN

进程LGWR将余下的缓冲区中的redo存储在磁盘上；并在redo中记录SCN;

（LGWR的刷新条件1、3s; 2、满1/3或者1m; 3、commit）

释放所有的锁定；释放队列中所占用的所有事项；

访问多个修改事务块，对缓存中的快速访问或者“清除”；

基于commit的内部触发动作（如基于commit的mv的快速刷新）

如果理解上面的话，应该理解下面的一些事实：

1、 commit是一个极快的动作；

2、在一个过程中，平衡地使用commit；

[察看当前session的事务所产生redo的语句]

selecta.sid,a.statistic#,a.value

fromv$sesstat a, v$statname b

wherea.STATISTIC#=b.STATISTIC# and

b.NAME like 'redo size' and

sid=( select sid from v$mystat where rownum=1)

drop table t4 purge;

create table t4

(

a char(200),

b char(200)

);

declare

-- Local variables here

i integer;

redo_amount_start integer;

redo_amount_end integer;

start_time integer;

v_a integer;

v_b integer;

begin

select a.value into redo_amount_start

fromv$sesstat a, v$statname b

wherea.STATISTIC#=b.STATISTIC# and

b.NAME like 'redo size' and

sid=( select sid from v$mystat where rownum=1);

start_time:=dbms_utility.get_time;

for i in 1..10000 loop

v_a:=i;

v_b:=i+100;

insert into t4 values(v_a,v_b);

commit;

end loop;

select a.value into redo_amount_end

fromv$sesstat a, v$statname b

wherea.STATISTIC#=b.STATISTIC# and

b.NAME like 'redo size' and

sid=( select sid from v$mystat where rownum=1);

dbms_output.put_line('redo_size='||(redo_amount_end-redo_amount_start));

dbms_output.put_line((dbms_utility.get_time-start_time)/100);

execute immediate 'truncate table t4';

select a.value into redo_amount_start

from v$sesstata, v$statname b

wherea.STATISTIC#=b.STATISTIC# and

b.NAME like 'redo size' and

sid=( select sid from v$mystat where rownum=1);

start_time:=dbms_utility.get_time;

for i in 1..10000 loop

v_a:=i;

v_b:=i+100;

insert into t4 values(v_a,v_b);

end loop;

commit;

select a.value into redo_amount_end

fromv$sesstat a, v$statname b

wherea.STATISTIC#=b.STATISTIC# and

b.NAME like 'redo size' and

sid=( select sid from v$mystat where rownum=1);

dbms_output.put_line('redo_size='||(redo_amount_end-redo_amount_start));

dbms_output.put_line((dbms_utility.get_time-start_time)/100);

end;

-------------------

redo_size=9191340

1.24

redo_size=6641732

.45

rollback会发生什么事情；

在游标中使用commit是一个很不好的习惯

create table t5

select cast(substr(object_name,1,1) as varchar2(2)) first_char from all_objects

declare

i integer;

begin

for x in (select * from t5) loop

delete from t5 where first_char= x.first_char;

dbms_output.put_line(sql%rowcount);

commit;

end loop;

end;

自主事务

关键字

PRAGMA AUTONOMOUS_TRANSACTION;

表示为一个自主事务，独立于父事务，就是说自主事务的提交与否不影响父事务的提交或者回滚。

举例说明

create table emp_dup as select * from empwhere 1=2

自主事务

create or replace procedure p_log(p_row_count in integer) is

pragma autonomous_transaction;

begin

insert into log_table values(p_row_count);

commit;

end p_log;

主事务

create or replace procedure p_main is

begin

insert into emp_dup select * from emp;

p_log(sql%rowcount);

rollback;

end p_main;

执行测试

begin

p_main;

end;

select * from log_table

-------

自主事务的主要用途

1、审计

2、避免变异表错误

3、躲开ora-14552错误

避免变异表错误

[在9i下执行][在11g没有出现04091错误]

create or replace trigger t_mutate_table

before update on emp_dup

for each row

declare

row_count int;

begin

select count(*）into row_count from emp where empno=:new.empno;

ifrow_count>0 then

insert into log_table values(:new.empno);

end if;

end t_mutate_table;

update emp_dup

set sal=sal+10

where empno=5555

select * from log_table

解决方法

create or replace trigger t_mutate_table

before update on emp_dup

for each row

declare

pragma autonomous_transaction;

row_count int;

begin

select count(*）into row_count from emp where empno=:new.empno;

ifrow_count>0 then

insert into log_table values(:new.empno);

commit;

end if;

躲开ora-14552错误

truncate table emp_dup

create type o_emp as object

(

EMPNO NUMBER(4) ,

ENAME VARCHAR2(10) ,

JOB VARCHAR2(9) ,

MGR NUMBER(4) ,

HIREDATE DATE ,

SAL NUMBER(7,2) ,

COMM NUMBER(7,2) ,

DEPTNO NUMBER(2)

);

select empno,ename, job, mgr, hiredate, sal, comm, deptno from emp_dup

create type t_emp_dup is table of o_emp

create or replace function f_get_emp_dup(p_deptnoin integer) return t_emp_dup is

Result t_emp_dup;

begin

insert into emp_dup select * from emp where deptno=p_deptno;

select o_emp(empno, ename, job, mgr,hiredate, sal, comm, deptno)

bulk collect into Result from emp_dup;

return(Result);

endf_get_emp_dup;

select * from table(f_get_emp_dup(20))

ORA-14551: 无法在查询中执行 DML 操作

ORA-06512:在"SCOTT.F_GET_EMP_DUP", line 5

修改函数

create or replace functionf_get_emp_dup(p_deptno in integer)

return t_emp_dup is

pragma autonomous_transaction;

Result t_emp_dup;

begin

insert into emp_dup

select * from emp where deptno = p_deptno;

commit;

select o_emp(empno, ename, job, mgr,hiredate, sal, comm, deptno) bulk collect

into Result

from emp_dup;

return(Result);

end f_get_emp_dup;

select * from table(f_get_emp_dup(20));

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- -------------------- --------- ------

5555 武大郎 CLERK 7900 2008-07-22 0.00 20

7369SMITH CLERK 7902 1980-12-17 800.00 20

7566JONES MANAGER 7839 1981-04-02 2975.00 20

7788SCOTT ANALYST 7566 1987-04-19 3000.00 20

7902FORD ANALYST 7566 1981-12-03 3000.00 20

7876 ADAMS CLERK 7788 1987-04-23 1100.00 20

自主事务与父事务享用同一个进程

create or replace function return_sid return integer is

Result integer;

pragma autonomous_transaction;

begin

select sid into Result from v$mystat where rownum = 1;

return(Result);

end return_sid;

declare

i integer;

begin

select sid into i from v$mystat where rownum = 1;

dbms_output.put_line('main session is '||i);

dbms_output.put_line('autonomous_transactionsession is '||return_sid);

end;

----------------

main session is 158

autonomous_transactionsession is 158

这会引起一个问题：

create global temporary table table_temp

(

x int

) on commit delete rows;

create or replace procedure p_auto_trancaction is

pragma autonomous_transaction;

begin

for x in (select x from table_temp)

loop

null;

end loop;

end p_auto_trancaction;

insert into table_temp values(2);

begin

p_auto_trancaction;

end;

begin

p_auto_trancaction;

end;

ORA-14450: 试图访问已经在使用的事务处理临时表

ORA-06512: 在"SCOTT.P_AUTO_TRANCACTION", line 4

ORA-06512: 在 line 3

因此在自主事务避免用临时表；

在自主事务中调用正常事务，自主事务提交，会提交正常事务。

create or replace procedurenormal_transaction(x in int) is

begin

insert intolog_table values(x);

end normal_transaction;

declare

pragmaautonomous_transaction;

i integer;

begin

insert into log_table values(10000);

commit;

normal_transaction(10);

end;

ORA-06519: 检测到活动的独立的事务处理, 已经回退

ORA-06512: 在 line 10

declare

pragmaautonomous_transaction;

i integer;

begin

insert into log_table values(10000);

normal_transaction(10);

commit;

end;

变异表中的自主事务问题：

制定一个规则：某部门每一个员工的工资不能超过该部门所有员工平均工资的2倍。

create or replace procedure sal_check(p_deptno in integer) is

avg_sal number;

max_sal number;

begin

select max(sal),avg(sal) into max_sal,avg_sal from emp where deptno=p_deptno;

ifmax_sal>2*avg_sal then

raise_application_error(-200001,'wa la wa la wa la');

end if;

end sal_check;

建立一个触发器检查规则

create or replace trigger trigger_sal_change

after insert or update or delete on emp

for each row

declare

begin

if (inserting or updating) then

sal_check(:new.deptno);

end if;

if (deleting or updating) then

sal_check(:old.deptno);

end if;

end trigger_sal_change;

update emp set sal=99999 where empno=7566

ORA-04091: 表 SCOTT.EMP发生了变化, 触发器/函数不能读它

ORA-06512: 在"SCOTT.SAL_CHECK", line 5

ORA-06512: 在"SCOTT.TRIGGER_SAL_CHANGE", line 5

ORA-04088: 触发器'SCOTT.TRIGGER_SAL_CHANGE' 执行过程中出错

解决这个问题

create or replace proceduresal_check(p_deptno in integer) is

pragma autonomous_transaction;

avg_sal number;

max_sal number;

begin

select max(sal),avg(sal) into max_sal,avg_sal from emp wheredeptno=p_deptno;

if max_sal>2*avg_sal then

raise_application_error(-200001,'wala wa la wa la');

end if;

endsal_check;

update emp set sal=99999 where empno=7566;

1 rowupdated

但是我们看看

select max(sal),avg(sal) from emp where deptno=20

MAX(SAL)	AVG(SAL)
99999	17983.16667

显然最大的工资比平均工资的2倍要大的多，业务规则没有起作用

再来更新一次

update emp set sal=99999 where empno=7566

ORA-20001:wa la wa la wa la

ORA-06512: 在 "SCOTT.SAL_CHECK", line 8

ORA-06512: 在 "SCOTT.TRIGGER_SAL_CHANGE", line 5

ORA-04088: 触发器 'SCOTT.TRIGGER_SAL_CHANGE' 执行过程中出错

为什么会出现这个情况？这是因为自主事务根本看不到父事务的数据变化而引起的。在开发过程中要小心

如果讲规则放在trigger中，也有变异表问题

这样写也有问题

create or replace trigger trigger_sal_change

after insert or update or delete on emp

for each row

declare

pragma autonomous_transaction;

avg_sal number;

max_sal number;

begin

if (inserting or updating) then

select max(sal), avg(sal)

into max_sal, avg_sal

from emp

where deptno = :new.deptno;

if max_sal > 2 * avg_sal then

raise_application_error(-20001, 'wa la wa la wa la');

end if;

if (deleting or updating) then

select max(sal), avg(sal)

into max_sal, avg_sal

from emp

where deptno = :old.deptno;

if max_sal > 2 * avg_sal then

raise_application_error(-20001, 'wa la wa la wa la');

end if;

commit;

end trigger_sal_change;

怎么解决这个问题

修改触发器

create or replace triggertrigger_sal_change

after insert or update or delete on emp

for each row

declare

pragma autonomous_transaction;

avg_sal number;

max_sal number;

r_count integer;

begin

select max(sal), avg(sal),count(*)

into max_sal,avg_sal,r_count

from emp

where deptno =:new.deptno;

if(inserting) then

max_sal:=greatest(max_sal,:new.sal);

avg_sal:=(avg_sal*r_count +nvl(:new.sal,0))/(r_count+1);

if max_sal> 2 * avg_sal then

raise_application_error(-20001, 'wa la wala wa la');

end if;

if(updating) then

max_sal:=greatest(max_sal,:new.sal);

avg_sal:=(avg_sal*r_count +nvl(:new.sal,0)-nvl(:old.sal,0))/r_count;

if max_sal> 2 * avg_sal then

raise_application_error(-20001, 'wa la wala wa la');

end if;

if (deleting) then

有点复杂

end if;

end;

最好的方法是用过程代替触发器。

SAVEPOINT

select * from emp_dup where empno=5555;

create unique index idx_empno on emp_dup(empno);

DECLARE

BEGIN

update emp_dup set sal=sal+10;

SAVEPOINT do_insert;

INSERT INTO emp_dup

select * from emp_dup where empno=5555;

commit;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK TO do_insert;

--commit;

DBMS_OUTPUT.PUT_LINE('Insert was rolledback');

END;

可以设定多个savepoint

从上述例子看出，如果第2个dml失败，整个事务只是退到savapoint

DECLARE

v_sales number;

BEGIN

select sal into v_sales from emp_dup where empno=5555;

dbms_output.put_line(v_sales);

update emp_dup set sal=sal+10;

select sal into v_sales from emp_dup where empno=5555;

dbms_output.put_line(v_sales);

SAVEPOINT do_insert;

INSERT INTO emp_dup

select * from emp_dup where empno=5555;

commit;

exception

WHEN others THEN

select sal into v_sales from emp_dup where empno=5555;

dbms_output.put_line(v_sales);

END;

--------------

在每一个dml中都包含一个隐性的savepoint。

事务的完整性

scenery-1:

session1

>-----------------t1(opencursor)------------------------t2(output)------------------t3(closecursor)--------------commit-------<

session2

>------t1(insert commit newdate)----------------------------------------------------------------------------------------------------<

question1

does session1’s output include new dateinserted by session2 ?

question2

Is there any methods to prevevt the newdate inserted by session2 from outputting of session1?

scenery-2:

session1

>-----------------t1(opencursor)------------------------t2(output)------------------t3(closecursor)--------------commit-------<

session2

>--------------------------------------t1(insertcommit new date)--------------------------------------------------------------------<

does session1’s output include new dateinserted by session2 ?

scenery-2:

session1

>--------t1(opencursor)--------t2(output)-------- t1(insert commit new date)----------t3(closecursor)--------------commit-------<

session2

does session1’s output include new dateinserted by session1 ?

test code

declare

CURSOR c1 IS SELECT deptno,loc from dept order by deptno;

s c1%rowtype;

begin

open c1;

dbms_lock.sleep(6);

dbms_output.put_line(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));

update dept set deptno=75 where deptno=13;

commit;

loop

EXIT WHEN c1%NOTFOUND;

fetch c1 into s;

dbms_output.put(s.deptno);

dbms_output.put('--------------');

dbms_output.put(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));

dbms_output.new_line;

dbms_lock.sleep(1);

end loop;

close c1;

end;

commit;

end;

begin

insert into dept values(18,'yyyy','yyyy');

commit;

dbms_output.put_line(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));

end;

看到一个DML开始，从打开游标，到关闭游标，其间即使有其他session,对该数据集合进行dml操作，并作提交，也不会影响到当前DML。要不然会完成事务的完整性了。

例子运算累计存款期间，发生了存款数据的DML操作。

上面有没有一个方法，设定过程（含有多个DML操作）事务，也能如此？

事务隔离等级

declare

CURSOR c1 IS SELECT deptno,loc from dept order by deptno;

s c1%rowtype;

begin

commit;

SET TRANSACTION READ ONLY NAME 'xxx';

dbms_lock.sleep(6); --in this period, we insertinto new date into dept is another session

open c1;

dbms_lock.sleep(6);

dbms_output.put_line(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));

dbms_lock.sleep(2);

loop

EXIT WHEN c1%NOTFOUND;

fetch c1 into s;

dbms_output.put(s.deptno);

dbms_output.put('--------------');

dbms_output.put(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));

dbms_output.new_line;

dbms_lock.sleep(1);

end loop;

close c1;

commit;

end;

--------------

10--------------yyyy--------------2008-10-03 12:11:26

20--------------CHICAGO--------------2008-10-0312:11:27

40--------------55--------------2008-10-03 12:11:28

55--------------55--------------2008-10-0312:11:29

begin

insert into dept values(18,'yyyy','yyyy');

commit;

dbms_output.put_line(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));

dbms_lock.sleep(6);

insert into dept values(19,'yyyy','yyyy');

commit;

dbms_output.put_line(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));

end;

只读事务，只能SELECTINTO, OPEN, FETCH, CLOSE, LOCKTABLE, COMMIT, and ROLLBACK语句，如果我们要

如果要让事务允许写操作(输出能够看到打开游标前的DML)

declare

CURSOR c1 IS SELECT deptno,loc from dept order by deptno;

s c1%rowtype;

begin

commit;

SET TRANSACTION read write NAME 'xxx';

dbms_lock.sleep(6); --in this period, weinsert into new date into dept is another session

update dept set loc='gg20' where deptno=20;

--commit;

open c1;

dbms_lock.sleep(6);

dbms_output.put_line(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));

update dept set loc='gg30' where deptno=30;

commit;

dbms_lock.sleep(2);

loop

EXIT WHEN c1%NOTFOUND;

fetch c1 into s;

dbms_output.put(s.deptno);

dbms_output.put('--------------');

dbms_output.put(s.loc);

dbms_output.put('--------------');

dbms_output.put(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));

dbms_output.new_line;

dbms_lock.sleep(1);

end loop;

close c1;

commit;

end;

无论如何，输出结果也看不到游标打开后关闭前的DML操作提交的结果，不管这个这个DML是来自于本session还是来自于其他session。

serializable事务隔离

session_1

declare

row_count integer;

begin

commit;

SET TRANSACTION isolation level serializable NAME 'xxx';

select count(*) into row_count from dept;

dbms_output.put_line(row_count);

dbms_lock.sleep(10);

select count(*) into row_count from dept;

dbms_output.put_line(row_count);

end;

session_2

begin

insert into dept values(18,'yyyy','yyyy');

commit;

dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

dbms_lock.sleep(6);

insert into dept values(19,'yyyy','yyyy');

commit;

dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

end;

-------------------

declare

row_count integer;

begin

commit;

SET TRANSACTION read only NAME 'xxx';

select count(*) into row_count from dept;

dbms_output.put_line(row_count);

dbms_lock.sleep(10);

select count(*) into row_count from dept;

dbms_output.put_line(row_count);

commit;

end;

--------------------

begin

insert into dept values(18,'yyyy','yyyy');

commit;

dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

dbms_lock.sleep(6);

insert into dept values(19,'yyyy','yyyy');

commit;

dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

end;

注意：SET TRANSACTION read write NAME 'xxx';不能保证serializable的功能

原创文章，如果转载，请标注作者：田文 CSDN地址：http://blog.csdn.net/tiwen818

Tiwen

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle事务学习笔记

ORACLE事务学习笔记有关事务的语句commit, rollback, savepoint, settransaction drop table t2 purge;create table t2 (x int); drop table t3 purge;create table t3 (x int check(x>0)); create
复制链接

扫一扫