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
----------
2
我们发现即使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
-----------
3
如果改变触发器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
as
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
-------
15
自主事务的主要用途
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;
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;
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;
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;
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;
--------------
10
20
20
在每一个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
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的功能
============ 欢迎各位老板打赏~ ===========
与本文相关的文章
- · 数据库先写日志还是先写数据？
- · 有一种心酸，叫靠自己
- · [未解决]ORACLE视图用rownum报错：missing right parenthesis
- · oracle自增列
- · 数据库 面试记录（面试题）
- · missing PARTITION or SUBPARTITION keyword
- · Linq2db.Oracle使用详解
- · oracle修改字段类型
- · [已解决]Oracle数据库长时间空闲后自动断开的解决办法
- · plsql 安装后database下拉没有东西
- · oracle64位客户端安装版 Instant Client v11.2.0.3.0(64-bit)
- · 腾讯《前端特工》闯关记
