Row Lock Contention: Scalability Killer

We experience major issues with TX Row Lock Contention. The design of the application has two tables that are central and updated from not only the UI but also batch processing and even some ad hoc queries. We don’t queue UI connections outside the database so it’s typical that we can witness 150 plus sessions blocked by a batch process. Prior to my arrival the solution was intermittent commits which as we all are aware can create performance issues.

I decided to drill further into not only the sessions that were during the blocking or being blocked but find out if in fact all the sessions were actually performing updates. I wanted to be able to show the developers what issue needed to be re-mediated with the details. The query I captured from the gv$sessions was very clear:

update table_a set col_a = :B1;

Yes every single session waiting on TX row lock contention was trying to execute the above sql. The question was were these all different rows or the same row.

From the gv$session we are provided with the object, file, block and row number of the row the blocked session is waiting on. We can then utilize dbms_rowid.rowid_create to find the rowid.

select dbms_rowid.rowid_create(1,19175,26,2781508,39) from daul;

19175 object_id
26 file number
2781508 block number
39 row number

The output is a rowid for the row that is waiting to be updated. We can then determine how many are duplicates.

obj file block row no rowid
19175 30 219580 32 AAAErnAAeAAA1m8AAg
19175 30 218792 31 AAAErnAAeAAA1aoAAf
19175 13 1622245 36 AAAErnAANAAGMDlAAk
19175 30 218903 2 AAAErnAAeAAA1cXAAC
19175 13 3776009 25 AAAErnAANAAOZ4JAAZ
19175 26 608808 33 AAAErnAAaAACUooAAh
19175 30 218855 16 AAAErnAAeAAA1bnAAQ
19175 30 219027 12 AAAErnAAeAAA1eTAAM
19175 30 219027 12 AAAErnAAeAAA1eTAAM
19175 13 1622245 36 AAAErnAANAAGMDlAAk
19175 26 608808 33 AAAErnAAaAACUooAAh
19175 28 489128 16 AAAErnAAcAAB3aoAAQ
19175 13 1622245 36 AAAErnAANAAGMDlAAk
19175 30 218903 2 AAAErnAAeAAA1cXAAC
19175 30 218903 2 AAAErnAAeAAA1cXAAC
19175 13 3776009 25 AAAErnAANAAOZ4JAAZ
19175 13 3775121 8 AAAErnAANAAOZqRAAI
19175 30 218903 2 AAAErnAAeAAA1cXAAC
19175 16 2953416 22 AAAErnAAQAALRDIAAW
19175 28 489458 29 AAAErnAAcAAB3fyAAd
19175 26 609544 28 AAAErnAAaAACU0IAAc
19175 26 2781612 2 AAAErnAAaAAKnGsAAC
19175 13 3775121 8 AAAErnAANAAOZqRAAI
19175 16 2953121 29 AAAErnAAQAALQ+hAAd
19175 30 219580 32 AAAErnAAeAAA1m8AAg
19175 30 218903 2 AAAErnAAeAAA1cXAAC

Based on the above output, there are few duplicates but for the most part the sessions are waiting on different rows. What rows are being waited on and is it a legitimate wait or are we updating a row that isn’t actually being updated. The data isn’t in fact being changed.

I can use the output rowid to find out what row in the table is being updated.

select * from table_a where rowid in (select dbms_rowid.rowid_create(1,19175,26,2781508,39) from dual;

At this point I’m not 100% sure what data I want to key in on within the table so i just request it all. Where is the query being generated from a procedure or UI? In my case it is actually a procedure that is executed when table_b is updated. Fortunately for us we have the rows populated with last_changed_by, last_changed_on and last_changed_thru so I have some key columns that I can review to see if they are in fact updating the data.

select b.id b_id, a.id a_id, a.last_changed_on, a.last_changed_by, a.last_changed_thru,
b.last_changed_on, b.last_changed_by, b.last_changed_thru from table_a a, table_b b
where a.id = b.a_id and a.id in (select id from table_a where rowid in
select dbms_rowid.rowid_create(1,19175,26,2781508,39) from dual)) order by b.last_changed_on desc;

When I reviewed the data I found that although table_b was in fact being updated, table_a was not necessarily being updated. I pulled up the code and found that the procedure that updates table_a was being called whenever table_b was updated whether or table_a needed to be updated or not. Oracle wasn’t in fact updating the data even though an update was performed. Which is expected behavior — Oracle is smart enough to not update even when it’s told to update a record if the column data matches. The bad news even though the data isn’t in fact updated, the DML is still executed and a row lock is still obtained.

Once I had all the details behind the problem I was able to show the developers the issue for proper remediation.

This made me think about past occurring blocking locks. Was this same information available to me within the DBA_HIST_ACTIVE_SESS_HISTORY table — provided of course there was no row movement. It would appear so.

select o.object_name, s.sample_time,s.session_id,s.event,s.blocking_session,s.sql_id,current_obj#, current_file#, current_block#, current_row#, dbms_rowid.rowid_create ( 1, o.DATA_OBJECT_ID, current_FILE#, current_BLOCK#, current_ROW# ) from dba_hist_active_sess_history s, dba_objects o where sample_time > sysdate - 1 and
object_name = 'TABLE_A' and blocking_session is not null and event='enq: TX - row lock contention' and s.current_OBJ# = o.OBJECT_ID

I bumped the output of the above query against the output from my earlier search, as well as all the data. The DBA_HIST_ACTIVE_SESS_HISTORY does provide the details for historical research. This will come in handy in very near future.

Advertisements

Tagged: ,

Leave a Reply

Please log in using one of these methods to post your comment:

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: