Let’s consider “hypothetical” case when it’s required to check if row is blocked without attempting to lock it.
And maybe we’ll find a way to see uncommitted data in Oracle. 🙂
But first let’s try to lock it and see what information we can get from system views
create table t(id,padding) as
select rownum, lpad('*', mod(rownum - 1, 100) + 1, '*')
from dual
connect by level <= 1000;
SQL> update t set padding = 'Hello' where id = 666;
1 row updated.
SQL> select userenv('sid') sid from dual;
SID
----------
248
SQL> select userenv('sid') sid from dual;
SID
----------
247
SQL> update t set padding = '777' where id = 666;
SQL> select event, blocking_session from v$session v where sid = 247;
EVENT BLOCKING_SESSION
-------------------------------------------------- ----------------
enq: TX - row lock contention 248
SQL>
SQL> select event,
2 (select max(sql_text) from v$sql where sql_id = vs.sql_id) curr_text,
3 (select max(sql_text) from v$sql where sql_id = vs.prev_sql_id) prev_text
4 from v$session vs
5 where sid = 248;
EVENT CURR_TEXT PREV_TEXT
-------------------------------------------------- ---------------------------------------- ----------------------------------------
SQL*Net message from client select userenv('sid') sid from dual select userenv('sid') sid from dual
OK. So it’s quite simple to figure out who is locking row by attempting to do the same.
Similar can be achieved by using v$lock, dba_dml_locks etc.
But it’s possible to get much more information about locking from data block itself.
This data block may not be flushed to disk so it exists in current mode in buffer cache.
In any given point in time there may be only one version of block in current mode and any block from buffer cache may be dumped to trace file.
That trace file will include one current version and any number of consistent version for specific SCN’s (since number of sessions may require their own consistent version of the same block).
Individual Buffer Dump
Let’s create procedure which will dump block for given rowid and parse dump to check transaction information and details for specified row.
This also will require creating directory pointing to user_dump_dest to read trace file and view based on x$bh which contains block address.
(unfortunately v$bh does not contain this information)
create or replace view v as
select x.ts#,
x.file#,
x.dbablk block#,
x.obj,
x.ba,
decode(x.state,
0,
'free',
1,
'xcur',
2,
'scur',
3,
'cr',
4,
'read',
5,
'mrec',
6,
'irec',
7,
'write',
8,
'pi',
9,
'memory',
10,
'mwrite',
11,
'donated',
12,
'protected',
13,
'securefile',
14,
'siop',
15,
'recckpt',
16,
'flashfree',
17,
'flashcur',
18,
'flashna') state
from sys.x$bh x;
create or replace procedure get_row_info(p_rowid in rowid) is
l_block number;
l_rdba number;
l_rn number;
l_object_id number;
l_ba varchar2(30);
l_ts number;
l_ident varchar2(4000) := sys_guid;
l_file_name varchar2(240);
l_clob clob;
l_transaction_info varchar2(32000);
l_row_info varchar2(32000);
function file2clob(p_filename in varchar2) return clob is
l_clob clob;
l_bfile bfile := bfilename('UDUMP', p_filename);
l_dest_offset integer := 1;
l_src_offset integer := 1;
l_lang_context integer := dbms_lob.default_lang_ctx;
l_warning integer;
begin
dbms_lob.createtemporary(l_clob, false, dbms_lob.call);
dbms_lob.fileopen(l_bfile);
dbms_lob.loadclobfromfile(l_clob,
l_bfile,
dbms_lob.lobmaxsize,
l_dest_offset,
l_src_offset,
dbms_lob.default_csid,
l_lang_context,
l_warning);
dbms_lob.fileclose(l_bfile);
return l_clob;
end;
begin
select dbms_rowid.rowid_block_number(p_rowid) block,
dbms_utility.make_data_block_address(dbms_rowid.rowid_relative_fno(p_rowid),
dbms_rowid.rowid_block_number(p_rowid)) rdba,
dbms_rowid.rowid_row_number(p_rowid) rn,
dbms_rowid.rowid_object(p_rowid) object
into l_block, l_rdba, l_rn, l_object_id
from t
where rowid = p_rowid;
select lower(substr(ba, 6)), ts# + 1
into l_ba, l_ts
from sys.v
where obj = l_object_id
and block# = l_block
and state = 'xcur';
execute immediate 'alter session set tracefile_identifier = ''' ||
l_ident || '''';
execute immediate 'alter session set events ''immediate trace name set_tsn_p1 level ' || l_ts || '''';
execute immediate 'alter session set events ''immediate trace name buffer level ' ||
l_rdba || '''';
execute immediate 'alter session set events ''immediate trace name set_tsn_p1 level 0''';
execute immediate 'alter session set events ''immediate trace name buffer level 0''';
select regexp_replace(value, '(.*\\)(.*)', '\2')
into l_file_name
from v$diag_info
where name = 'Default Trace File';
l_clob := file2clob(l_file_name);
select regexp_substr(current_version,
'( Itl Xid Uba Flag Lck Scn/Fsc.*?)bdba:',
1,
1,
'n',
1) transaction_info,
regexp_substr(current_version,
'(tab 0, row ' || l_rn || '.*?)tab 0',
1,
1,
'n',
1) row_info
into l_transaction_info, l_row_info
from (select regexp_substr(l_clob,
'ba: 0x' || l_ba || '.*?end_of_block_dump',
1,
1,
'n') current_version
from dual);
dbms_output.put_line(l_transaction_info);
dbms_output.put_line(l_row_info);
end get_row_info;
SQL> select rowid from t where id = 666; ROWID ------------------ AAAWdnAAGAAAHqoABD
SQL> set serveroutput on
SQL> set lines 330
SQL> exec get_row_info(chartorowid('AAAWdnAAGAAAHqoABD'));
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00246f20
0x02 0x0006.01a.0000087e 0x01404dac.0177.15 ---- 1 fsc 0x003d.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
tab 0, row 67, @0x461
tl: 13 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 3] c2 07 43
col 1: [ 5] 48 65 6c 6c 6f
PL/SQL procedure successfully completed.
So this output shows that row is locked by transaction 0x2 which is 0x0006.01a.0000087e and value for padding was changed and to “48 65 6c 6c 6f” or in readable format to
SQL> select utl_raw.cast_to_varchar2(replace('48 65 6c 6c 6f', ' ')) x from dual;
X
--------------------
Hello
SQL> select s.sid, '0x' || to_char(t.xidusn, 'fm0xxx') || '.' || to_char(t.xidslot, 'fm0xx') || '.' || to_char(t.xidsqn, 'fm0xxxxxxx') dumped_xid
2 from v$transaction t, v$session s
3 where t.ses_addr = s.saddr
4 and t.addr = s.taddr
5 and '0x' || to_char(t.xidusn, 'fm0xxx') || '.' || to_char(t.xidslot, 'fm0xx') || '.' || to_char(t.xidsqn, 'fm0xxxxxxx') = '0x0006.01a.0000087e';
SID DUMPED_XID
---------- ----------------------
248 0x0006.01a.0000087e
So this technics allows not only checking if row is locked without trying to lock it but also reveals uncommitted data from another session!
However please consider this information as an educational material regarding Oracle internals.
Also this code does not contain special handling for chained/migrated rows and for a lot of other details but I hope that’s enough to show how to parse data for given rowid from buffer cache.
After rolling back update in session one get_row_info return following data for the same rowid
SQL> exec get_row_info(chartorowid('AAAWdnAAGAAAHqoABD'));
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00246f20
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
tab 0, row 67, @0x417
tl: 74 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 3] c2 07 43
col 1: [66]
2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
PL/SQL procedure successfully completed.
SQLMDX 