Irrelevant thoughts of an oracle DBA

24 May 2008

So, you think to know who is blocking you, do you?

Filed under: Uncategorized — Freek D'Hooge @ 23:09

Recently I needed to verify why an update kept hanging on one of our client’s new rac system.
I suspected this to be a locking issue, so I queried v$session with a self join on the blocking_session column to check if the session was indeed blocked and if so who was blocking it.

The result showed that the session was indeed blocked and that the blocking session was issued by the sysman user.
Which was a little bit odd.

After running some queries to find out what the hell sysman was doing (which resulted in zero answers), I ran an older script I have that joins v$lock with v$session (next to v$locked_object and dba_objects) which showed a different session as blocking session.
Which was a little bit odd.

Additional queries confirmed that it was indeed this other session which was holding the lock (comming of course from the same user on a different machine, on which he forgot to issue a commit).

Currious about this, I checked the output from the queries again and I noticed that the two reported sessions where off by 1. In v$session.blocking_session the value was 1 higher then reported from v$lock.
So I did some tests if this result was consistent.

C:\>sqlplus fdh/fdh

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 14 17:40:25 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options

fdh@TRIDION> create table test
  2  ( veld1  number)
  3  tablespace sysaux;

Table created.

fdh@TRIDION> insert into test values (1);

1 row created.

fdh@TRIDION> commit;

Commit complete.

fdh@TRIDION> select sid from v$mystat where rownum = 1;

       SID
----------
       502

fdh@TRIDION> update test set veld1 = 2;

1 row updated.

fdh@TRIDION>

I now have an unfinished transaction in session with sid 502.
I will now open a new session (on the same instance) and try to update the same statement.

C:\>sqlplus fdh/fdh

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 14 17:40:40 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options

fdh@TRIDION> select sid from v$mystat where rownum = 1;

       SID
----------
       322

fdh@TRIDION> update test set veld1 = 3;

I now have a blocking lock, so lets check v$session to see the value of the blocking_session column:

sys@TRIDION> select sid, serial#, username, blocking_session from gv$session where blocking_session is not null;

SID    SERIAL# USERNAME                       BLOCKING_SESSION
---------- ---------- ------------------------------ ----------------
322      17689 FDH                                         503

I did the test several times, each time the value in blocking_session was 1 higher then the actual blocking session.
However, If the 2 sessions where on different nodes, the reported sid was correct. Same for single instances.
So I rule seems to be that if both the blocking and the blocked session are on the same rac instance, the value in the blocking_session column in v$session is 1 higher then the actual blocking sid.

A search on metalink showed that this is in fact a known bug: Bug 5481650, and that it is fixed in 10.2.0.4.
An interim patch is availabe for 10.2.0.2 and 10.2.0.3 for both solaris sparc as linux 64bit (don’t know if the problem is restricted to these two platforms).

So be warned the next time you need to verify a locking issue on your rac database, oracle might lie to you about who is guilty

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: