K刀论坛

oracle事务隔离级别_笔记9:一致读和当前读

时间:2014-04-19 11:56 来源: 我爱IT技术网作者:微风

一致读和当前读

读一致性对修改有什么影响呢?这么说吧，想像一下你正在对某个数据库表执行以下UPDATE语句：Update t set x=x+1 where y=5;我们知道，查询的WHERE Y=5部分(即读一致阶段)会使用一个一致读来处理(TKPROF报告中的查询模式获取)。这个语句开始执行时表中已提交的WHERE=5记录集就是它将看到的记录(假设使用READ COMMITTED隔离级别，如果隔离级别是SERIALIZABLE，所看到的则是事务开始时存在的WHERE Y=5记录集)。这说明，如果UPDATE语句从开始到结束要花5分钟来进行处理，而有人在此期间向表中增加并提交了一个新记录，其Y列值为5，那么UPDATE看不到这个记录，因为一致读是看不到新记录的。这在预料之中，也是正常的。但问题是，如果两个会话按顺序执行以下语句会发生什么情况呢?

Update t set y=10 where y=5;

Update t set x=x+1 where y=5;

开始UPDATE时Y=5的记录不再是Y=5了。UPDATE的一致读部分指出：“你想更新这个记录，因为我们开始时Y是5”，但是根据块的当前版本，你会这样想“噢，不行，我不能更新这一行，因为Y不再是5了，这可能不对”

如果我们此时只是跳过这个记录，并将其忽略，就会有一个不确定的更新。这可能会破坏数据一致性和完整性。更新的结果(即修改了多少行，以及修改了哪些行)将取决于以何种顺序命中(找到)表中的行以及此时刚好在做什么活动。在两个不同的数据库中，取同样的一个行集，每个数据库都以同样的顺序运行事务，可能会观察到不同的结果，这只是因为这些行在磁盘上的位置不同。

在这种情况下，Oracle会选择重启动更新。如果开始时Y=5的行现在包含值Y=10，Oracle会悄悄地回滚更新(仅回滚更新，不会回滚事物的任何其他部分)，并重启动(假设使用的是READ COMMITTED隔离级别)。如果你使用了SERIALIZABLE隔离级别，此时这个事务就会收到一个ORA-08177：can't serialize access for this transaction错误。采用READ COMMITTED模式，事务回滚你的更新后，数据库会启动更新(也就是说，修改更新相关的时间点)，而且它并非重新更新数据，而是进入SELECT FOR UPDATE模式，并试图为你的会话锁住所有WHERE Y=5的行。一旦完成了这个锁定，它会对这些锁定的数据运行UPDATE，这样可以确保这一次就能完成而不必(再次)重启动。

但是再想想“如果...会发生什么...”，如果重启动更新，并进入SELECT FOR UPDATE模式(与UPDATE一样，同样有读一致块获取和读当前块获取)，开始SELECT FOR UPDATE时Y=5的一行等到你得到它的当前版本时却发现Y=11，会发生什么呢?SELECT FOR UPDATE会重启动，而且这个循环会再来一遍。

实验的结果为何与书上的结果不一致呢?

[oracle@vcenteroracle trace]$ ls

alert_orcl.log

sys@ORCL>exec dbms_monitor.session_trace_enable

PL/SQL procedure successfully completed.

[oracle@vcenteroracle trace]$ ls

alert_orcl.log orcl_ora_6360.trc orcl_ora_6360.trm

sys@ORCL>select * from scott.t;

X

----------

10006

[oracle@vcenteroracle trace]$ tkprof orcl_ora_6360.trc trace.txt print=100 record=sql.txt sys=no

TKPROF: Release 11.2.0.3.0 - Development on Fri Nov 8 22:03:22 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

[oracle@vcenteroracle trace]$ ls

alert_orcl.log orcl_ora_6360.trc orcl_ora_6360.trm sql.txt trace.txt

[oracle@vcenteroracle trace]$ more trace.txt

TKPROF: Release 11.2.0.3.0 - Development on Fri Nov 8 22:03:22 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Trace file: orcl_ora_6360.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

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

Parse 3 0.00 0.00 0 0 0 0

Execute 4 0.00 0.00 2 57 0 3

Fetch 2 0.00 0.00 3 6 0 1

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

total 9 0.01 0.01 5 63 0 4

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 5 0.00 0.00

SQL*Net message from client 4 17.05 17.05

db file sequential read 1 0.00 0.00

Disk file operations I/O 1 0.00 0.00

db file scattered read 1 0.00 0.00

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

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

Parse 6 0.00 0.00 0 0 0 0

Execute 66 0.02 0.02 0 0 0 0

Fetch 67 0.00 0.00 7 195 0 404

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

total 139 0.03 0.03 7 195 0 404

Misses in library cache during parse: 6

Misses in library cache during execute: 6

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

db file sequential read 7 0.00 0.00

4 user SQL statements in session.

12 internal SQL statements in session.

16 SQL statements in session.

********************************************************************************

Trace file: orcl_ora_6360.trc

Trace file compatibility: 11.1.0.7

Sort options: default

1 session in tracefile.

4 user SQL statements in trace file.

12 internal SQL statements in trace file.

16 SQL statements in trace file.

15 unique SQL statements in trace file.

339 lines in trace file.

17 elapsed seconds in trace file.

sys@ORCL>update scott.t t1 set x=x+1;

1 row updated.

[oracle@vcenteroracle trace]$

[oracle@vcenteroracle trace]$ tkprof orcl_ora_6360.trc trace.txt print=100 record=sql.txt sys=no

TKPROF: Release 11.2.0.3.0 - Development on Fri Nov 8 22:05:15 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

[oracle@vcenteroracle trace]$ more trace.txt

TKPROF: Release 11.2.0.3.0 - Development on Fri Nov 8 22:05:15 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Trace file: orcl_ora_6360.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

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

Parse 5 0.01 0.01 0 0 0 0

Execute 6 0.00 0.00 2 63 3 5

Fetch 2 0.00 0.00 3 6 0 1

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

total 13 0.02 0.02 5 69 3 6

Misses in library cache during parse: 2

Misses in library cache during execute: 1

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 7 0.00 0.00

SQL*Net message from client 6 157.15 174.20

db file sequential read 1 0.00 0.00

Disk file operations I/O 1 0.00 0.00

db file scattered read 1 0.00 0.00

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

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

Parse 8 0.00 0.01 0 0 0 0

Execute 98 0.05 0.05 0 0 0 0

Fetch 141 0.00 0.00 7 310 0 456

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

total 247 0.07 0.07 7 310 0 456

Misses in library cache during parse: 8

Misses in library cache during execute: 8

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

db file sequential read 7 0.00 0.00

5 user SQL statements in session.

16 internal SQL statements in session.

21 SQL statements in session.

********************************************************************************

Trace file: orcl_ora_6360.trc

Trace file compatibility: 11.1.0.7

Sort options: default

1 session in tracefile.

5 user SQL statements in trace file.

16 internal SQL statements in trace file.

21 SQL statements in trace file.

20 unique SQL statements in trace file.

528 lines in trace file.

174 elapsed seconds in trace file.

sys@ORCL>update scott.t t2 set x=x+1;

1 row updated.

[oracle@vcenteroracle trace]$ ls -al

total 88

drwxr-x--- 2 oracle oinstall 16384 Nov 8 22:03 .

drwxr-x--- 15 oracle oinstall 4096 Jun 20 11:48 ..

-rw-r----- 1 oracle oinstall 210 Nov 8 22:01 alert_orcl.log

-rw-r----- 1 oracle oinstall 50350 Nov 8 22:08 orcl_ora_6360.trc

-rw-r----- 1 oracle oinstall 503 Nov 8 22:08 orcl_ora_6360.trm

-rw-r--r-- 1 oracle oinstall 0 Nov 8 22:05 sql.txt

-rw-r--r-- 1 oracle oinstall 3615 Nov 8 22:05 trace.txt

[oracle@vcenteroracle trace]$ tkprof orcl_ora_6360.trc trace.txt print=100 record=sql.txt sys=no

TKPROF: Release 11.2.0.3.0 - Development on Fri Nov 8 22:08:59 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

[oracle@vcenteroracle trace]$ more trace.txt

TKPROF: Release 11.2.0.3.0 - Development on Fri Nov 8 22:08:59 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Trace file: orcl_ora_6360.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

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

Parse 7 0.01 0.01 0 0 0 0

Execute 8 0.01 0.00 2 69 4 7

Fetch 2 0.00 0.00 3 6 0 1

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

total 17 0.02 0.02 5 75 4 8

Misses in library cache during parse: 3

Misses in library cache during execute: 1

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 9 0.00 0.00

SQL*Net message from client 8 209.43 383.64

db file sequential read 1 0.00 0.00

Disk file operations I/O 1 0.00 0.00

db file scattered read 1 0.00 0.00

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

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

Parse 8 0.00 0.01 0 0 0 0

Execute 98 0.05 0.05 0 0 0 0

Fetch 141 0.00 0.00 7 310 0 456

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

total 247 0.07 0.07 7 310 0 456

Misses in library cache during parse: 8

Misses in library cache during execute: 8

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

db file sequential read 7 0.00 0.00

6 user SQL statements in session.

16 internal SQL statements in session.

22 SQL statements in session.

********************************************************************************

Trace file: orcl_ora_6360.trc

Trace file compatibility: 11.1.0.7

Sort options: default

1 session in tracefile.

6 user SQL statements in trace file.

16 internal SQL statements in trace file.

22 SQL statements in trace file.

21 unique SQL statements in trace file.

546 lines in trace file.

383 elapsed seconds in trace file.

[oracle@vcenteroracle trace]$

本文来源我爱IT技术网 http://www.52ij.com/jishu/5226.html 转载请保留链接。

评论列表（网友评论仅供网友表达个人看法，并不表明本站同意其观点或证实其描述）

oracle事务隔离级别_笔记9:一致读和当前读

最新文章

相关文章