oracle的事务与锁与回滚段 的block 的一点摸索

jianliang

system >desc x$bh;
ERROR:
ORA-04043: object x$bh does not exist

muhaook

这两句话还是有差别的：

同一个事务可能具有多个 SCN ,实际上每一个 DML 操作都有一个SCN

SCNs are assigned on each DML that create a transaction

Yong Huang

最初由 muhaook 发布
[B]这两句话还是有差别的：

同一个事务可能具有多个 SCN ,实际上每一个 DML 操作都有一个SCN

SCNs are assigned on each DML that create a transaction [/B]

Indeed every transaction may have more than one SCN. But not every DML is assigned a *new* SCN. Only those that succeed in updating/deleting/inserting a row or rows are. As I said, if your table only has a value 123 and your DML is "update mytable set mycolumn=234 where mycolumn=234", then this DML won't get a new SCN.

I should have said this instead:
A*new* SCN is assigned on each DML that creates a transaction. But every DML, and in fact every operation including a query in Oracle, is assigned an SCN, not necessarily a new SCN.

Yong Huang

wolf2602

收藏
！

biti_rainy

当同一个事务里面存在多个 DML 的时候，（DML可能存在于同一个block里面）

oracle 在这些DML 之间通过什么联系起来？  
Itl Xid Uba Flag Lck Scn/Fsc   中的  Scn/Fsc   ?
这些DML怎么表示为一个共同的事务，根据  v$lock 信息？ enquene source ?
这些信息在rollback segment 中的存储又是怎样联系的

假如commit ，要更改 回滚段头，回滚段头记录了 事务的活动性信息，按说应该从这里开始能顺利的理出整个事务的 DML 所变化的数据，这样如果roll  back 才可以成功执行

既然我们说每个DML都可能产生一个SCN，那么事务的标记，就不应该是以 SCN区分的
他们之间的联系，怎么才能查看  回滚段头 的数据？以及回滚段中实际存储的数据？
commit 的时候 写进日志文件的到底是什么?

说的很零碎和罗嗦，归根结底的问题 就是： oracle 到底是怎样维护事务的原子性的 ?

lucy_wang

我觉得这个好象是oracle最难理解的地方了，这几天学习正被卡到这个地方了。

就上面讨论的多个image的问题。

假如存在顺序未提交事务 a,b,c
分别存在before image a1,b1,c1
a1 是原始数据
b1 包含了a 的改变
c1 包含了a和b的改变

如果现在a 事务被提交了，那么 image a1 通过何种机制从data buffer中flush的。 a1  被提交后，执行了事务d, 那么d 事务用的image 是b1吗？

minger

Indeed every transaction may have more than one SCN. But not every DML is assigned a *new* SCN. Only those that succeed in updating/deleting/inserting a row or rows are. As I said, if your table only has a value 123 and your DML is "update mytable set mycolumn=234 where mycolumn=234", then this DML won't get a new SCN.

i have test statment such as "update mytable set mycolumn=234 where mycolumn=234",

SQL> update a set a=234 where a=234;

0 rows updated.

but the scn has been changed

SQL> update a set a=234 where a=234;
0 rows updated.

Yong Huang

最初由 minger 发布
[B]Indeed every transaction may have more than one SCN. But not every DML is assigned a *new* SCN. Only those that succeed in updating/deleting/inserting a row or rows are. As I said, if your table only has a value 123 and your DML is "update mytable set mycolumn=234 where mycolumn=234", then this DML won't get a new SCN.

i have test statment such as "update mytable set mycolumn=234 where mycolumn=234",

SQL> update a set a=234 where a=234;

0 rows updated.

but the scn has been changed

SQL> update a set a=234 where a=234;
0 rows updated. [/B]

Can you tell us exactly what you did? How did you find the SCN?

Here's what I did. On a very quiet database, create table t (a number). Insert into t values (123). Commit. Select * from dba_extents where segment_name = 'T' and see that file_id = 7 and block_id = 969. So run this
alter system dump datafile 7 block 970;
Go to udump directory and grep scn the trace file.

In sqlplus, also run this (http://www.ixora.com.au/q+a/0009/20125947.htm)
select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

But the 2 SCNs you get may not be the same. I don't quite understand this. Anyway, what I find is that if you update t set a = 1 where a = 234 (234 doesn't exist in T), followed by dumping the block, you'll see the SCN in the trace file still that number. But the SCN from x$ktuxe is incremented because of alter system dump datafile. If you run Steve Adams' SQL after you update t set a = 1 where a = 234 but before alter system dump datafile, even the x$ktuxe SCN won't change.

Yong Huang

minger

I test in table a have 100 rows, not incluing value 234
SQL> update a set a=234 where a=234;
0 rows updated.
before and after this dml ,the scns was different in the trace file.

biti_rainy

select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

ktuxescnw  正好是表示高位的 2  bytes   0x0000  ,
ktuxescnb  正好是低位的 4bytes
4 bytes 能表示的最大数正好是  power(16,8) = power(2,32) = 4294967296

至于 SCN 为什么要这样分开来存储，偶的猜想是 4294967296  正好是 C 中的long 型整数的最大值，也就是 long 是 4字节存储
而 integer 是2  bytes存储，这样就有  

SQL> select power(2,16) from dual;

POWER(2,16)
-----------
      65536

考虑到integer 分正负，则最大是

SQL>  select power(2,16)/2 from dual;

POWER(2,16)/2
-------------
        32768

所以在c里面integer 最大是 32768
而 long 型的正整数类型就是 4294967296

于是进而猜测由于 oracle 早先大部分书C写的，使用 double 似乎不好，使用 long 为 SCN 的编号，这样存在着 有可能不够用的可能，就采取了这种折中方案
SQL> select power(16,8)/366/24/3600 from dual;

POWER(16,8)/366/24/3600
-----------------------
             135.820409


偶这样解释这个问题，不知道  Yong Huang 有什么看法