Concurrent update on the same block

We all know that blocks are cached in buffer cache and there could only be one XCUR cache for each block. What will happen if multiple sessions are updating different rows in the same block simultaneously?

SQL> drop table t1 purge;

Table dropped.

SQL> create table t1(id number, name varchar2(64));

Table created.

SQL> begin
2 for i in 1..1000 loop
3 insert into t1 values(
4 i, ‘INITIAL VALUE OF COLUMN’);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> select
2 dbms_rowid.rowid_relative_fno(rowid) File#,
3 dbms_rowid.rowid_block_number(rowid) Block#,
4 count(1)
5 from t1
6 group by
7 dbms_rowid.rowid_relative_fno(rowid),
8 dbms_rowid.rowid_block_number(rowid)
9 order by 1,2
10 /

FILE# BLOCK# COUNT(1)
———- ———- ———-
4 435 220
4 436 117
4 437 220
4 438 220
4 439 223
select object_id, data_object_id
from user_objects
where object_name=’T1′;

OBJECT_ID DATA_OBJECT_ID
———- ————–
17092 17092

— check blocks in buffer cache
select file#, block#,
decode (class#,
1, ‘data block’,
2, ‘sort block’,
3, ‘save undo block’,
4, ‘segment header’,
5, ‘save undo header’,
6, ‘free list’,
7, ‘extent map’,
8, ‘1st level bmb’,
9, ‘2nd level bmb’,
10, ‘3rd level bmb’,
11, ‘bitmap block’,
12, ‘bitmap index block’,
13, ‘file header block’,
14, ‘unused’,
15, ‘system undo header’,
16, ‘system undo block’,
17, ‘undo header’,
18, ‘undo block’) class_type,
dirty,
status
from v$bh
where objd = 17092
order by 1,2,3;
FILE# BLOCK# CLASS_TYPE D STATUS
———- ———- —————— – ———-
4 434 segment header N xcur
4 435 data block N xcur
4 436 data block N xcur
4 437 data block N xcur
4 438 data block N xcur
4 439 data block N xcur

set lines 200 pages 200
col object_name for a30
select b.dbarfil, b.dbablk,
decode (b.class,
1, ‘data block’,
2, ‘sort block’,
3, ‘save undo block’,
4, ‘segment header’,
5, ‘save undo header’,
6, ‘free list’,
7, ‘extent map’,
8, ‘1st level bmb’,
9, ‘2nd level bmb’,
10, ‘3rd level bmb’,
11, ‘bitmap block’,
12, ‘bitmap index block’,
13, ‘file header block’,
14, ‘unused’,
15, ‘system undo header’,
16, ‘system undo block’,
17, ‘undo header’,
18, ‘undo block’) class_type,
decode(state, 0, ‘free’,
1, ‘xcur’,
2, ‘scur’,
3, ‘cr’,
4, ‘read’,
5, ‘mrec’,
6, ‘irec’,
7, ‘iwrite’,
8, ‘pi’,
9, ‘memory’,
10, ‘mwrite’,
11, ‘donated’) as state,
cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq,
(select object_name from dba_objects where data_object_id = b.obj) as object_name
from sys.x$bh b
where dbarfil = 4
and dbablk = 436
order by CR_SCN_BAS;

DBARFIL DBABLK CLASS_TYPE STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- —————— ——- ———- ———- ———- ———- ———- ——————————
4 436 data block xcur 0 0 0 0 0 T1

— identify rows in a specific block
select min(id), max(id), count(id)
from t1
where dbms_rowid.rowid_block_number(rowid) = 436;

MIN(ID) MAX(ID) COUNT(ID)
———- ———- ———-
884 1000 117

session 1:

select current_scn from v$database;

CURRENT_SCN
———–
4812383

update T1 set name=’AFTER UPDTAE’ where id=884;

x$bh information(notice that there is one more CR block):

DBARFIL DBABLK CLASS_TYPE STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- —————— ——- ———- ———- ———- ———- ———- ——————————
4 436 data block xcur 0 0 0 0 0 T1
4 436 data block cr 4812383 0 0 0 0 T1
SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
—————————
10.3.12172

session 2:

select current_scn from v$database;
CURRENT_SCN
———–
4812405

update T1 set name=’AFTER UPDTAE’ where id=885;

x$bh information(notice that there is one more CR block):

DBARFIL DBABLK CLASS_TYPE STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- —————— ——- ———- ———- ———- ———- ———- ——————————
4 436 data block xcur 0 0 0 0 0 T1
4 436 data block cr 4812383 0 0 0 0 T1
4 436 data block cr 4812405 0 3 8243 991 T1

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
————————————————————————————————————————–
9.27.2651

session 3:

select current_scn from v$database;

CURRENT_SCN
———–
4812427

update T1 set name=’AFTER UPDTAE’ where id=886;

x$bh information(notice that there is one more CR block):

DBARFIL DBABLK CLASS_TYPE STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- —————— ——- ———- ———- ———- ———- ———- ——————————
4 436 data block xcur 0 0 0 0 0 T1
4 436 data block cr 4812383 0 0 0 0 T1
4 436 data block cr 4812405 0 3 8243 991 T1
4 436 data block cr 4812427 0 3 8243 991 T1

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
————————————————————————————————————————–
8.27.954

Dump block to trace:

tab 0, row 0, @0x10aa
tl: 20 fb: –H-FL– lb: 0x2 cc: 2
col 0: [ 3] c2 09 55
col 1: [12] 41 46 54 45 52 20 55 50 44 54 41 45
tab 0, row 1, @0x1096
tl: 20 fb: –H-FL– lb: 0x3 cc: 2
col 0: [ 3] c2 09 56
col 1: [12] 41 46 54 45 52 20 55 50 44 54 41 45
tab 0, row 2, @0x1082
tl: 20 fb: –H-FL– lb: 0x1 cc: 2
col 0: [ 3] c2 09 57
col 1: [12] 41 46 54 45 52 20 55 50 44 54 41 45

SQL> select utl_raw.cast_to_number(replace(‘c2 09 55′,’ ‘)) value from dual;

VALUE
———-
884

We can see that 884 was stored in the 1st slot(slot 0).

Now we dump buffer cache to check block content in memory

oradebug setmypid
oradebug tracefile_name

alter session set events ‘immediate trace name buffers level 3’;

Now we find the CR block (search the data_object_id 17092. Since there are multiple blocks in buffer cache, we only focus on block 436):

CR blocks:

———————————————————–
BH (0x693efe78) file#: 4 rdba: 0x010001b4 (4/436) class: 1 ba: 0x69286000
set: 49 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 1,28
dbwrid: 0 obj: 17092 objn: 17092 tsn: 4 afn: 4 hint: f
hash: [0x69fd93c8,0x7efddc10] lru: [0x693f0090,0x7da7dd00]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.496e8b],[xid: 0xa.3.2f8c],[uba: 0xc02033.3df.16],[cls: 0x0.496e8b],[sfl: 0x1],[lc: 0x0.496e8b] <—-scn 0x0000.00496e8b converts to decimal : 4812427
flags: only_sequential_access
buffer tsn: 4 rdba: 0x010001b4 (4/436)
scn: 0x0000.00496e8c seq: 0x00 flg: 0x00 tail: 0x6e8c0600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

Block header dump: 0x010001b4
Object id on Block? Y
seg/obj: 0x42c4 csc: 0x00.496e8b itc: 3 flg: E typ: 1 – DATA
brn: 0 bdba: 0x10001b0 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.020.00002f35 0x00c00511.03ca.52 C— 0 scn 0x0000.0048df49
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
bdba: 0x010001b4
data_block_dump,data header at 0x6928607c

tab 0, row 0, @0x1077
tl: 31 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 3] c2 09 55
col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e
tab 0, row 1, @0x1058
tl: 31 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 3] c2 09 56
col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e
tab 0, row 2, @0x10fc
tl: 31 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 3] c2 09 57
col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e

———————————————————–
BH (0x69fd9318) file#: 4 rdba: 0x010001b4 (4/436) class: 1 ba: 0x69c22000
set: 61 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,28
dbwrid: 0 obj: 17092 objn: 17092 tsn: 4 afn: 4 hint: f
hash: [0x69fec038,0x693eff28] lru: [0x69fd9530,0x69fd92d0]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.496e75],[xid: 0xa.3.2f8c],[uba: 0xc02033.3df.16],[cls: 0x0.496e75],[sfl: 0x40],[lc: 0x0.48eb03] <— 496e75 converts to decimal 4812405
flags: only_sequential_access
buffer tsn: 4 rdba: 0x010001b4 (4/436)
scn: 0x0000.00496e76 seq: 0x00 flg: 0x00 tail: 0x6e760600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

Block header dump: 0x010001b4
Object id on Block? Y
seg/obj: 0x42c4 csc: 0x00.496e76 itc: 3 flg: E typ: 1 – DATA
brn: 0 bdba: 0x10001b0 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.020.00002f35 0x00c00511.03ca.52 C— 0 scn 0x0000.0048df49
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
bdba: 0x010001b4
data_block_dump,data header at 0x69c2207c

tab 0, row 0, @0x108b
tl: 31 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 3] c2 09 55
col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e
tab 0, row 1, @0x10be
tl: 31 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 3] c2 09 56
col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e
tab 0, row 2, @0x10fc
tl: 31 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 3] c2 09 57
col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e

———————————————————–
BH (0x69febf88) file#: 4 rdba: 0x010001b4 (4/436) class: 1 ba: 0x69e1c000
set: 49 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 1,28
dbwrid: 0 obj: 17092 objn: 17092 tsn: 4 afn: 4 hint: f
hash: [0x6a3e9318,0x69fd93c8] lru: [0x69fec1a0,0x69febf40]
obj-flags: object_ckpt_list
ckptq: [0x693effa8,0x7da88018] fileq: [0x7da88098,0x7da88098] objq: [0x77a2ca10,0x77a2ca10] objaq: [0x77a2c9f0,0x77a2c9f0]
use: [NULL] wait: [NULL]
st: XCURRENT md: NULL fpin: ‘kdswh11: kdst_fetch’ tch: 4 txn: 0x7c3ad3d8
flags: buffer_dirty private redo_since_read
LRBA: [0x6f.10c3e.0] LSCN: [0x0.496e76] HSCN: [0x0.496e8b] HSUB: [1]
buffer tsn: 4 rdba: 0x010001b4 (4/436)
scn: 0x0000.00496e8b seq: 0x01 flg: 0x00 tail: 0x6e8b0601 <—– scn 0x0000.00496e8b converts to decimal : 4812427
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

Block header dump: 0x010001b4
Object id on Block? Y
seg/obj: 0x42c4 csc: 0x00.496e8b itc: 3 flg: E typ: 1 – DATA
brn: 0 bdba: 0x10001b0 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.01b.000003ba 0x00c00187.0057.1b —- 1 fsc 0x000b.00000000
0x02 0x000a.003.00002f8c 0x00c02033.03df.16 —- 1 fsc 0x000b.00000000
0x03 0x0009.01b.00000a5b 0x00c0116a.00ca.0a —- 1 fsc 0x000b.00000000
bdba: 0x010001b4
data_block_dump,data header at 0x69e1c07c

tab 0, row 0, @0x10aa
tl: 20 fb: –H-FL– lb: 0x2 cc: 2
col 0: [ 3] c2 09 55
col 1: [12] 41 46 54 45 52 20 55 50 44 54 41 45
tab 0, row 1, @0x1096
tl: 20 fb: –H-FL– lb: 0x3 cc: 2
col 0: [ 3] c2 09 56
col 1: [12] 41 46 54 45 52 20 55 50 44 54 41 45
tab 0, row 2, @0x1082
tl: 20 fb: –H-FL– lb: 0x1 cc: 2
col 0: [ 3] c2 09 57
col 1: [12] 41 46 54 45 52 20 55 50 44 54 41 45
———————————————————–
BH (0x6a3e9268) file#: 4 rdba: 0x010001b4 (4/436) class: 1 ba: 0x6a1d0000
set: 49 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 1,28
dbwrid: 0 obj: 17092 objn: 17092 tsn: 4 afn: 4 hint: f
hash: [0x7efddc10,0x69fec038] lru: [0x7da7dd00,0x6a7e8d60]
lru-flags: moved_to_tail
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL fpin: ‘kdswh11: kdst_fetch’ tch: 2
cr: [scn: 0x0.496e5f],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.496e5f],[sfl: 0x0],[lc: 0x0.48eb03] <—– scn 0x0000.00496e5f converts to decimal 4812383
flags: only_sequential_access
buffer tsn: 4 rdba: 0x010001b4 (4/436)
scn: 0x0000.0048eb03 seq: 0x01 flg: 0x04 tail: 0xeb030601
frmt: 0x02 chkval: 0x69bf type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

Block header dump: 0x010001b4
Object id on Block? Y
seg/obj: 0x42c4 csc: 0x00.48e211 itc: 3 flg: E typ: 1 – DATA
brn: 0 bdba: 0x10001b0 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.020.00002f35 0x00c00511.03ca.52 C— 0 scn 0x0000.0048df49
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
bdba: 0x010001b4
data_block_dump,data header at 0x6a1d007c

tab 0, row 0, @0x10dd
tl: 31 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 3] c2 09 55
col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e
tab 0, row 1, @0x10be
tl: 31 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 3] c2 09 56
col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e
tab 0, row 2, @0x10fc
tl: 31 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 3] c2 09 57
col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e

———————————————————–

Now we commit all the transactions and select the same row for multiple times:

select * from T1 where rowid=dbms_rowid.rowid_create(1,17092,4,436,0);

TCH DBARFIL DBABLK CLASS_TYPE STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- —————— ——- ———- ———- ———- ———- ———- ——————————
5 4 436 data block xcur 0 0 0 0 0 T1
1 4 436 data block cr 4812405 0 3 8243 991 T1
1 4 436 data block cr 4821828 0 3 7381 106 T1
1 4 436 data block cr 4821829 0 3 7381 106 T1
1 4 436 data block cr 4821831 0 3 7381 106 T1
1 4 436 data block cr 4821832 0 3 7381 106 T1
1 4 436 data block cr 4821833 0 3 7381 106 T1
1 4 436 data block cr 4821834 0 3 7381 106 T1

Conclusion:

1 When multiple sessions are updating different rows in the same block, one new CR block will be built for each session.
2 If all the transactions are active, then ALL the CR blocks would contain BEFORE IMAGE of ALL the affected rows and all the update and ITL information will be contained in the XCUR block.
3 TCH is NOT the exact touch count of the block.

Reference:

http://www.eygle.com/refer/Oracle_x$table_list.htm
http://arup.blogspot.jp/2011/04/can-i-fit-80mb-database-completely-in.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s