通过学习
Read committed | This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data. ----------------------------- Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms. | Oracle 默认使用的事务隔离级别。事务内执行的查询只能看到查询执行前(而非事务开始前)就已经提交的数据。Oracle 的查询永远不会读取脏数据(未提交的数据)。 ----------------------- Oracle 不会阻止一个事务修改另一事务中的查询正在访问的数据,因此在一个事务内的两个查询的执行间歇期间,数据有可能被其他事务修改。举例来说,如果一个事务内同一查询执行两次,可能会遇到不可重复读取(nonrepeatable read)或不存在读取(phantom)的现象。 |
Serializable | Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms. | 串行化隔离的事务只能看到事务执行前就已经提交的数据,以及事务内 INSERT,UPDATE,及 DELETE 语句对数据的修改。串行化隔离的事务不会出现不可重复读取或不存在读取的现象。 |
Read-only | Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements. | 只读事务只能看到事务执行前就已经提交的数据,且事务中不能执行 INSERT,UPDATE,及DELETE 语句。 |
(注:上图截至Oracle_Concepts)
改变事务的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;
改变会话的隔离级别
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;
实验:
READ COMMITTED
SQL> conn scott/tiger
已连接。
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> set transaction isolation level read committed;
事务处理集
Time | Session A | Session B | |||
Time1 | insert deptno= 50 | ||||
Time2 | select * from dept | ||||
可以看到deptno= 50的数据 | |||||
Time3 | select * from dept | ||||
看不到deptno= 50的数据 | |||||
Time4 | update dept set deptno = deptno-10 | ||||
commit | |||||
Time5 | select * from dept | ||||
看到的数据为deptno=0,10,20,30,50的数据 | |||||
遇见了不可重复读取的现象 | |||||
Time6 | insert deptno=60 | ||||
Time7 | select * from dept | ||||
看到的数据为deptno=0,10,20,30,50,60的数据 | |||||
遇见了不存在读取的现象 |
SQL> set transaction isolation level serializable;
Time | Session A | Session B |
Time1 | insert deptno= 50 | |
Time2 | select * from dept | |
可以看到deptno= 50的数据 | ||
Time3 | select * from dept | |
看不到deptno= 50的数据 | ||
Time4 | update dept set deptno = deptno-10 | |
报错:ERROR位于第1行: | ||
ORA-08177:无法连续访问此事务处理 | ||
You will get this message whenever you attempt to update a row that has changed since | ||
your transaction began---当你试图修改其他事务已经更改的行 | ||
Time5 | select * from dept | |
看到的数据为deptno=10,20,30,40,50的数据 | ||
与Time2看到的数据一致 | ||
Time6 | insert deptno=60 | |
commit; | ||
Time7 | 看到的数据为deptno=10,20,30,40,50的数据 | |
与Time2看到的数据一致 |
关于serializable的误解
serializable不意味着事务是以串行的方式一个接一个的执行的
session A:
SQL> set transaction isolation level serializable;
事务处理集。
SQL> insert into a select count(*) from b;
已创建1行。
SQL> commit;
提交完成。
SQL> select * from a;
X
----------
0
session B:
SQL> set transaction isolation level serializable;
事务处理集。
SQL> insert into b select count(*) from a;
已创建1行。
SQL> commit;
提交完成。
SQL> select * from b;
Y
----------
0
两个表中数据都是0,如果事务是串行的,后一个执行insert的语句的值应该为1
关于如何查询隔离级别
SQL>altersessionsetisolation_level=serializable;
Session altered.
SQL>deletefromt1whererownum=1;
1row deleted.
SQL>selectflagfromv$transaction;
FLAG
----------
268439043
SQL>commit;
Commitcomplete.
SQL>altersessionsetisolation_level=READCOMMITTED;
Session altered.
SQL>deletefromt1whererownum=1;
1row deleted.
SQL> selectflagfromv$transaction;
FLAG
----------
3587
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15489979/viewspace-743848/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15489979/viewspace-743848/