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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-611009/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-611009/