|
X$BH
|
Last time, I performed UPDATE query in a single server to examine when CR
block was created. CR block is created to ensure the consistent read.
In other words, CR block is created when data version (SCN) to be accessed
is different from the data version you actually want (i.e. SCN is larger than the
necessary SCN.)
In order to create a CR block, it is necessary to make the block version
older by using UNDO block. Transaction entry in the block is required
to identify the UNDO block.
We have covered the topic transaction entry in several issues.
This time, I get the buffer dump to observe the relationship between
transaction entry and lock.
I briefly review the mechanism of the lock.
The following is the testing environment.
*************************************************************
(Environment)
Linux 2.4.2-2
Oracle9i EE Release 9.2.0.1.0
(Table configuration)
SQL> desc test
Name Type
--------- ------------------
ID1 NUMBER
ID2 NUMBER
TEXT VARCHAR2(2000)
*Grant INDEX (TEST_IDX) to ID1.
Table contains the following data.
ID1 ID2 TEXT
---------- ---------- --------------------
1 1 insight
2 2 insight
3 3 insight
*************************************************************
|
First of all, I perform UPDATE query to row 2 to see how the lock is obtained.
*************************************************************
SQL> update test set id1=1 where id1>1;
Two rows updated
OBJECT_NAME STATE DBABLK BA
-------------------- ----- ---------- --------
TEST xcur 70913 54D12000 <-segment header
TEST xcur 70914 54D0E000 <-data block
TEST_IDX xcur 96394 54D10000 <-index
Data are updated as follows.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ID1 ID2 TEXT
---------- ---------- --------------------
1 1 insight
1 2 insight
1 3 insight
-Execute the following command to get the buffer dump.
SQL> alter session set events 'immediate trace name buffers level 6';
*************************************************************
|
Now, I examine the dump.
Buffer dump of each block is configured as follows:
[Buffer header]
.
[Block header]
.
[Data block]
.
.
[Buffer header]
Buffer header contains buffer address (BA) that is used to connect to the data
in X$BH table. Buffer header also contains the SCN that enables you
to identify the current block version.
For more information about the buffer header, refer to the topic Oracle9i.
[Block header]
The following is the part of block header of the buffer dump.
*************************************************************
Block header (table)
Block header dump: 0x00411502
Object id on Block? Y
seg/obj: 0x82d0 csc: 0x00.887cc20 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.028.00027e99 0x00800974.09bd.3b C--- 0 scn 0x0000.08877c26
0x02 0x0002.02d.000281f5 0x0080083b.08ee.28 ---- 2 fsc 0x0000.00000000
| |
Itl entry number Number of rows locked
*************************************************************
|
[Itl, Lck]
Take a look at 0x02 above. Transaction entry locks two rows.
Flag of 0x01 indicates C, which means that it is already committed.
Transaction entry binds the rollback information. I will talk about this next week.
This time, I will examine the relationship between the transaction entry above
and each record in the block. Transaction of 0x02 locks two rows. How does this transaction
entry bind the record?
I actually view the data block.
*************************************************************
Data block (table)
data_block_dump,data header at 0x54d5405c
===============
.
((skipped))
.
0x16:pri[2] offs=0x1f6d
block_row_dump:
tab 0, row 0, @0x1f8f
tl: 17 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 02 ^^^^^^^ <- ID1 = 1
col 1: [ 2] c1 02 <- ID2 = 1
col 2: [ 7] 69 6e 73 69 67 68 74 <- TEXT= insight
tab 0, row 1, @0x1f5c
tl: 17 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 02 ^^^^^^^
col 1: [ 2] c1 03 <- ID2 = 2
col 2: [ 7] 69 6e 73 69 67 68 74
tab 0, row 2, @0x1f6d
tl: 17 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 02 ^^^^^^^
col 1: [ 2] c1 04 <- ID2 = 3
col 2: [ 7] 69 6e 73 69 67 68 74
end_of_block_dump
*************************************************************
|
lb: 0x0 above is not locked. lb: 0x2, on the other hand,
indicates that rows are locked.
The data proves that it is possible to lock each row and to
identify the transaction that holds the lock.
I briefly examine the index
*************************************************************
Block header (index)
Block header dump: 0x0041788a
Object id on Block? Y
seg/obj: 0x82cf csc: 0x00.888c646 itc: 3 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0008.028.00027e99 0x00800974.09bd.3c C--- 0 scn 0x0000.08877c26
0x03 0x0002.02d.000281f5 0x0080083b.08ee.2a ---- 4 fsc 0x001c.00000000
|
Four rows are locked
*************************************************************
Data block (index)
Leaf block dump
===============
.
((skipped))
E
row#0[7996] flag: -----, lock: 0
col 0; len 2; (2): c1 02 <- ID1 = 1
col 1; len 6; (6): 00 41 15 02 00 00 <- ROWID
row#1[7912] flag: -----, lock: 3 <- Itl 3 obtains a lock
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 15 02 00 01
row#2[7924] flag: -----, lock: 3
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 15 02 00 02
row#3[7984] flag: ---D-, lock: 3 <- Pre-updated data contains Delete (D) flag
col 0; len 2; (2): c1 03 <- ID1 = 2
col 1; len 6; (6): 00 41 15 02 00 01
row#4[7972] flag: ---D-, lock: 3 <- Pre-updated data contains Delete (D) flag
col 0; len 2; (2): c1 04 <- ID1 = 3
col 1; len 6; (6): 00 41 15 02 00 02
----- end of leaf block dump -----
*************************************************************
|
Index holds four locks because the index contains the pre-updated
data. If you want to compare the updated data with pre-updated data,
find the data with the same ROWID. In this case, 00 41 15 02 00 01
of col 1 is the ROWID.
Today's analysis proves that the transaction entry enables locking a row.
Yoshihiro Uratsuji
|
|
