什么是事务的语句级原子性?
客户发出的语句会完全成功或者完全失败
测试:
SQL> create table s (cnt int);
Table created.
SQL> insert into s values (0);
1 row created.
SQL> commit;
Commit complete.
SQL> create table s1 (x int check (x>0));
Table created.
SQL> create trigger s1_trigger 2 before insert or delete on s1 for each row 3 begin 4 if (inserting) then 5 update s set cnt=cnt+1; 6 else 7 update s set cnt=cnt-1; 8 end if; 9 dbms_output.put_line('I fired and updated '||sql%rowcount||' rows'); 10 end; 11 /
Trigger created.
SQL> set serveroutput on; SQL> select * from s1;
no rows selected
SQL> select * from s;
CNT ---------- 0
SQL> insert into s1 values (1); I fired and updated 1 rows
1 row created.
SQL> select * from s;
CNT ---------- 1
SQL> insert into s1 values (-1); I fired and updated 1 rows insert into s1 values (-1) * ERROR at line 1: ORA-02290: check constraint (SCOTT.SYS_C006297) violated
SQL> select * from s;
CNT ---------- 1
SQL> 这里测试的是一个触发器,当对有触发器的表进行操作时,如果成功,则触发器对其所影响的表操作也成功,如果对有触发器的表进行操作时发生失败,则触发器对其所影响的表操作也不成功
Table created.
SQL> insert into s values (0);
1 row created.
SQL> commit;
Commit complete.
SQL> create table s1 (x int check (x>0));
Table created.
SQL> create trigger s1_trigger 2 before insert or delete on s1 for each row 3 begin 4 if (inserting) then 5 update s set cnt=cnt+1; 6 else 7 update s set cnt=cnt-1; 8 end if; 9 dbms_output.put_line('I fired and updated '||sql%rowcount||' rows'); 10 end; 11 /
Trigger created.
SQL> set serveroutput on; SQL> select * from s1;
no rows selected
SQL> select * from s;
CNT ---------- 0
SQL> insert into s1 values (1); I fired and updated 1 rows
1 row created.
SQL> select * from s;
CNT ---------- 1
SQL> insert into s1 values (-1); I fired and updated 1 rows insert into s1 values (-1) * ERROR at line 1: ORA-02290: check constraint (SCOTT.SYS_C006297) violated
SQL> select * from s;
CNT ---------- 1
SQL> 这里测试的是一个触发器,当对有触发器的表进行操作时,如果成功,则触发器对其所影响的表操作也成功,如果对有触发器的表进行操作时发生失败,则触发器对其所影响的表操作也不成功
那ORACLE内部如何实现语句级原子性:伪代码如下
Savepoint statement1;
Insert into t values ( 1 );
If error then rollback to statement1;
Savepoint statement2;
Insert into t values ( -1 );
If error then rollback to statement2;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-731238/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-731238/