oracle 怎样控制并事物,oracle实验记录 （事务控制）

commit;

rollback;

savepoint;

rollback to savepoint;

set transaction;

set constraints;

set transaction read write(defalut)

set transaction read only不可以DML

SQL> set transaction name 'userxh';~~~~~~~事务命名

Transaction set.

SQL> insert into t1 values(1);

1 row created.

SQL> select name from v$transaction where name='userxh';

NAME

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

userxh

SQL> set transaction read only;~~~~~~~~等上一个结束才可以设置新的

set transaction read only

*

ERROR at line 1:

ORA-01453: SET TRANSACTION must be first statement of transaction

SQL> commit;~~~~~~~~~~~userxh事务以commit 结束(or rollback)

Commit complete.

SQL> set transaction  read only;~不可以DML

Transaction set.

SQL> insert into t1 values(1);

insert into t1 values(1)

*

ERROR at line 1:

ORA-01456: may not perform. insert/delete/update operation inside a READ ONLY

transaction

select ,alter system ,alter session可以

set transaction  read write(default)

SQL> commit;(提交后 下一个TRANSACTION 自动为read write)

Commit complete.

set transaction isolation level read commited(default) 提供一致读

会出现不可重复读,幻象读(与第一读数据不一样,有新数据)

SQL> conn / as sysdba

Connected.

SQL> select * from xh.t1;第一次查

A

----------

1

SQL> select * from xh.t1; 第2次查

A

----------

2~~~~~~~~~~~不可重复读

3~~~~~~~~~~~~~~~~幻象读

SQL> conn xh/a831115

Connected.

SQL> update t1 set a=2;

1 row updated.

SQL> insert into t1 values(3);

1 row created.

SQL> commit;

Commit complete.

set transaction isolation level  serializable

使用一个可串行化的事务

DML操作的数据是该事务 开始之前 已COMMIT的 ,并且改事务 看不到 别的事物对表的更新 再次读取时候肯定 与第一次一样(如果自己没改) 整个事务期间总可以返回相同的结果,

这样在做报表时候有用

简单点说就是 不允许 dirty read ,不允许 不可重复读, 不允许幻想读 ,是最高级别的隔离

SQL> conn xh/a831115

Connected.

SQL> select * from t1;

A

----------

2

3

SQL> set transaction isolation level serializable;

Transaction set.

SQL> conn / as sysdba~~~~~~~~另一个session

Connected.

SQL> update xh.t1 set a=5 where a=2;

1 row updated.

SQL> commit;

Commit complete.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~xh session

SQL> select * from t1;~~~~~~~~~~~还是2,3 永远读取 设定这个事务之前的

A

----------

2

3

ORA-08177: can't serialize access for this transaction(解释一下这个)

SQL> set transaction isolation level serializable;

Transaction set.

SQL> show user

USER is "XH"

SQL> update t1 set a=888 where a=88;

1 row updated.

SQL> conn zz/a123

Connected.

SQL> show user

USER is "ZZ"

SQL> set transaction isolation level serializable;

Transaction set.

SQL> update xh.t1 set a=888 where a=88;  hange住了 这个是必须的 因为lock的原因

SQL> commit;xh

Commit complete.

SQL> update xh.t1 set a=888 where a=88;     zz

update xh.t1 set a=888 where a=88

*

ERROR at line 1:

ORA-08177: can't serialize access for this transaction

SQL> update xh.t1 set a=888 where a=88;~~~~~~~XH 已commit ZZ再执行还是不行

update xh.t1 set a=888 where a=88

*

ERROR at line 1:

ORA-08177: can't serialize access for this transaction

问题来的 ,若N多事务 又都更新了 同一行,那么只有第一个执行的语句成功,其他都收到这个错误

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> set transaction isolation level serializable;

Transaction set.

SQL> show user

USER is "XH"

SQL> select * from t1;

A

----------

5

888

SQL> update t1 set a=6  where a=5;

1 row updated.

SQL> commit;~~~~~~~马上提交

Commit complete.

SQL> set transaction isolation level serializable;

Transaction set.

SQL> show user

USER is "ZZ"

SQL> select * from xh.t1;

A

----------

5

888

SQL> update xh.t1 set a=6  where a=5; XH commit后才执行的

update xh.t1 set a=6  where a=5

*

ERROR at line 1:

ORA-08177: can't serialize access for this transactio

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> conn xh/a831115~~~~~~~~~~~顺序1

Connected.

SQL> update t1 set a=2;~~~顺序4

15 rows updated.

SQL> commit;

Commit complete.

~~~~~~~~~~~~~~~~~~~~2个SESSION中

CONN ZZ/A123

SQL> set transaction isolation level serializable;~~顺序2

Transaction set.

SQL> select count(*) from xh.t1;~~~~顺序3(只查到该事务 开始之前 已COMMIT的)

COUNT(*)

----------

15

SQL> update xh.t1 set a=5;~~~~顺序5

update xh.t1 set a=5

*

ERROR at line 1:

ORA-08177: can't serialize access for this transaction

~~~~~~~~~~~DML只能修改 该事务开始前的已经COMMIT数据,oracle要判断在此事务前,其它事务对这些数据行的修改已提交或未提交,判断靠block中相关信息(itl solts  能看出是

否提交过)

若想多保存块状态信息 就 alter table XX iNITRANS N

所以当一个serializable transaction修改数据时候 此数据被其它事务修改 commit过,那么报错

SYS用户or sysdba connct join 不能有READ ONLY或SERIALIZABLE事务

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> set transaction isolation level serializable;

set transaction isolation level serializable

*

ERROR at line 1:

ORA-08178: illegal SERIALIZABLE clause specified for user INTERNAL

alter session set isolation_level=serializable;`~~or alter system  设置级别***************

set constraints设置延迟约束检验时机

语法:set constraints 约束名 ,all   immediate|deferred

SQL> alter table t1  add constraint t1c unique(a) deferrable ;

Table altered.

SQL> select * from t1;

A

----------

6

66

SQL> insert into t1 values(66);

insert into t1 values(66)

*

ERROR at line 1:

ORA-00001: unique constraint (XH.T1C) violated

SQL> set  constraint t1c deferred;

Constraint set.

SQL> insert into t1 values(66);

1 row created.

SQL> commit;~~~~~~~~~~~~~~~~延迟到commit

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (XH.T1C) violated

SQL> set  constraint t1c immediate;

Constraint set.

SQL> insert into t1 values(66);

insert into t1 values(66)                立即

*

ERROR at line 1:

ORA-00001: unique constraint (XH.T1C) violated~~~~~~~~~~~~~~

SQL> alter table t1  add constraint t1c unique(a) deferrable initially deferred;~~,另外这样也行

Table altered.

SQL> insert into t1 values(66);

1 row created.

SQL> commit

2  ;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (XH.T1C) violated