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