My Blog

Saving you 15 minutes: TX Row Lock Contention and Corrective Actions Part 1

by Philip Brown on 21st July 2014 No comments

TX row lock contention issues are a bit of a pain as they are normally intermittent and require a bit of co-ordination between DBAs and application developers at the exact time they are happening.  To try and circumvent this I wanted to be able to automatically notify the application developers when this issue was occurring and what the affected order was.

To do this I generated a bit of SQL:

select distinct decode(sql_id,
 '716c1whwnhn77','select * from.apps.order ',
 '0jhvf4y5xmmhn','select * from apps.order_item ',
 '98sfcwp2zp6kx','select * from apps.order_relationship ','SQL NOT CURRENTLY KNOWN')||'
 where rowid = dbms_rowid.rowid_create (1,'||row_wait_obj#||','||row_wait_file#||','||row_wait_block#||','||row_wait_row#||')'
 from v$session where
 state='WAITING'
 and event='enq: TX - row lock contention'
 and seconds_in_wait > 640;

Using ASH I could determine the SQL_ID candidates which were the most likely to be waiting in a TX lock issue.  As it seemed to be a couple of SQL IDs, I then embedded this in a decode statement.

The decode statement wouldtake the relevant SQL ID and then translate that into a select statement, this would then be concatenated with a translation of row_wait_obj#, row_wait_file#, and row_wait_row# from v$session to get a rowid.  When you put these together you would then get the relevant select statement which would allow application support to pinpoint the actual orders which were being locked.

select * from apps_order where rowid = dbms_rowid.rowid_create (1,1256151,16,357335);

So the next to do is setup 12c to fire the SQL when an application row lock occurs.

Philip BrownSaving you 15 minutes: TX Row Lock Contention and Corrective Actions Part 1

Related Posts

Take a look at these posts

Join the conversation