什么是事务的语句级原子性？

客户发出的语句会完全成功或者完全失败

测试：

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>

这里测试的是一个触发器，当对有触发器的表进行操作时，如果成功，则触发器对其所影响的表操作也成功，如果对有触发器的表进行操作时发生失败，则触发器对其所影响的表操作也不成功

那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博客

测试TOM=事务的语句级原子性

最新文章