Oracle Tips and Tricks — David Fitzjarrell

April 28, 2008

Locks (No bagels)

Filed under: General — dfitzjarrell @ 16:03

Our intrepid blogger of a few posts back claims the following:

“1. How do you know What are the objects have locks??

Solution:

select * from v$lock where block<>0;

if block=0 then no locks if block=1 then there is locks”

Unfortunately the V$LOCK view contains no object information whatsoever. Also, simply because BLOCK is 0 does not mean there are no locks present, simply that none of the existing locks are blocking anyone. Executing the query supplied against a 10.2.0.3 database provides the following output:

SQL> select * From v$lock where block  0;

no rows selected

SQL>

which, according to the ‘information’ supplied says no locks exist in the database. Nothing could be further from the truth; querying V$LOCK again, absent the supplied WHERE clause, reveals:

SQL> select * From v$lock;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
070000003B8AF020 070000003B8AF040        165 XR          4          0          1          0     130060          0
070000003B8AF0B8 070000003B8AF0D8        165 CF          0          0          2          0     130060          0
070000003B8AF1E8 070000003B8AF208        165 RS         25          1          2          0     130055          0
070000003B8AF318 070000003B8AF338        166 RT          1          0          6          0     130055          0
070000003B8AF4E0 070000003B8AF500        164 TS          3          1          3          0     130049          0
070000003B8AF6A8 070000003B8AF6C8        167 MR         20          0          4          0     130052          0
070000003B8AF740 070000003B8AF760        167 MR          1          0          4          0     130052          0
070000003B8AF7D8 070000003B8AF7F8        167 MR          2          0          4          0     130052          0
070000003B8AF870 070000003B8AF890        167 MR          3          0          4          0     130052          0
070000003B8AF908 070000003B8AF928        167 MR          4          0          4          0     130052          0
070000003B8AF9B8 070000003B8AF9D8        167 MR          5          0          4          0     130052          0
070000003B8AFA50 070000003B8AFA70        167 MR          6          0          4          0     130052          0
070000003B8AFAE8 070000003B8AFB08        167 MR          7          0          4          0     130052          0
070000003B8AFB80 070000003B8AFBA0        167 MR          8          0          4          0     130052          0
070000003B8AFC18 070000003B8AFC38        167 MR          9          0          4          0     130052          0
070000003B8AFCB0 070000003B8AFCD0        167 MR         10          0          4          0     130052          0
070000003B8AFD48 070000003B8AFD68        167 MR         11          0          4          0     130052          0
070000003B8AFDE0 070000003B8AFE00        167 MR         12          0          4          0     130052          0
070000003B8AFE78 070000003B8AFE98        167 MR         13          0          4          0     130052          0
070000003B8AFF10 070000003B8AFF30        167 MR         14          0          4          0     130052          0
070000003B8AFFA8 070000003B8AFFC8        167 MR         15          0          4          0     130052          0
070000003B8B0040 070000003B8B0060        167 MR         16          0          4          0     130052          0
070000003B8B00D8 070000003B8B00F8        167 MR         17          0          4          0     130052          0
070000003B8B0170 070000003B8B0190        167 MR         18          0          4          0     130052          0
070000003B8B0208 070000003B8B0228        167 MR         19          0          4          0     130052          0
070000003B8B02A0 070000003B8B02C0        167 MR         21          0          4          0     130052          0
070000003B8B0350 070000003B8B0370        167 MR         22          0          4          0     130052          0
070000003B8B03E8 070000003B8B0408        167 MR         23          0          4          0     130052          0
070000003B8B0480 070000003B8B04A0        167 MR         24          0          4          0     130052          0
070000003B8B0518 070000003B8B0538        167 MR         25          0          4          0     130052          0
070000003B8B05B0 070000003B8B05D0        167 MR         26          0          4          0     130052          0
070000003B8B0648 070000003B8B0668        167 MR         27          0          4          0     130052          0
070000003B8B06E0 070000003B8B0700        167 MR         28          0          4          0     130052          0
070000003B8B0778 070000003B8B0798        167 MR         29          0          4          0     130052          0
070000003B8B0810 070000003B8B0830        167 MR        201          0          4          0     130052          0

35 rows selected.

SQL>

Gee, there are 35 locks existing in the database; of course none of which are blocking anyone (30 of the locks are media recovery locks* with the remaining 5 locks identified as follows: CF — Control file enqueue lock, RS — row shared lock, RT — Redo thread enqueue, TS — Temporary segment enqueue lock, XR — Forced logging enqueue due to checkpoints, I suspect). Which doesn’t mean they don’t exist, just that no one is waiting on one or more of them to be released so that the transaction in waiting can complete. [The MR locks are associated with the data files and temp file associated with the database in question, one per file.]

* – Media recovery locks are held for one of two reasons: to prevent anyone from bringing a tablespace online which is under recovery or to prevent someone from attempting recovery on an online tablespace. Thus they are always present in a database as a protection mechanism.

Of course this was not enough misinformation to provide the user community so this poor soul offers this nugget of wisdom:

“2. How do you know locked tables? and how do you remove locks?

Solution:

1. select a.object_id, a.session_id, substr(b.object_name, 1, 40) from v$locked_object a, dba_objects b where a.object_id = b.object_id order by b.object_name ;

2. select sid, serial#, command, taddr from v$session where sid=

3. alter system kill session ”;”

My, what wonderful and considerate advice: just kill whatever session is blocking someone else, whether or not that session has completed its task and without any notification to the soon-to-be affected user. To act in such an irrational and arbitrary manner is wrong, to say the least. Yes, there MAY be situations where such action is warranted but, invariably, such action follows user complaints and/or physical problems not resolvable in any other way. A power outage (no matter how small) may render users unable to re-connect as the prior sessions still exist in the database. [This would usually be an application issue, although if resource profiles are in use this could generate an Oracle error.] Such is one valid case for simply killing sessions to free resources; remember, though, that a complaint was lodged regarding the inability to perform necessary work and that notice was given regarding said act. Another might be that an application, due to network or client resource problems, appeared to ‘hang’ while processing a transaction and the end-user simply clicked on the red ‘X’ in the upper right hand corner of the window, killing the local process but leaving the database session still in existence. Killing the offending session in such a scenario would be a valid course of action, again preceded by notification from the user community. But, to kill sessions simply because they lock an object or objects (for even the smallest length of time) that YOU want to access is irresponsible.

The Internet is an interesting place, full of wit, wisdom, humour and, unfortunately, plenty of mis-information. Myths abound, and half-truths become gospel truths simply because they are found there. Such could be the fate of these examples of ‘a little knowledge can be a dangerous thing.’ And, truly, the latter of the two examples is a dangerous piece of ‘advice’, based solely upon the author’s ill-conceived notion of locks and locking in an Oracle database. Of course, if your goal is to madden the user community at large and make it virtually impossible to perform any meaningful work then, by all means, follow his recommendation. Of course if you do then don’t be surprised when a ‘pink slip’ from HR lands upon your desk, as those who blindly follow such instructions invariably find themselves standing in the unemployment line.

Advertisements

2 Comments »

  1. […] written here regarding Oracle locks but apparently the post didn’t explain enough about the views […]

    Pingback by Let My Data Go! « Oracle Tips and Tricks — July 23, 2012 @ 09:06 | Reply

  2. […] a large number of requests to google.com about Oracle database locks. I’ve blogged before on locks and how to see who has which object locked, but I haven’t said much as to the nature and […]

    Pingback by Lock ‘Em Up | Oracle Tips and Tricks -- David Fitzjarrell — November 11, 2016 @ 08:02 | Reply


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

Create a free website or blog at WordPress.com.

%d bloggers like this: