SQLMDX

Eventually it's all about data

Is a row locked?

Posted by sqlmdx on December 22, 2015

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.

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

 
%d bloggers like this: