Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gyan.

Asked: October 04, 2019 - 9:13 am UTC

Last updated: October 07, 2019 - 1:32 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

In our production environment, sometimes table lock occurs. During table lock, i have identified the blocker and holder session, and sqls. While checking one session is on inactive session, and query for both sessions are like "SELECT" statements. How can we track each rows, datafiles level tracking, and actually why/how the one session cause table lock for other session??

and Connor said...

Check here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=how-does-oracle-determine-locks

and here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=questions-on-locking

for details on locking and some examples on how to query the V$ tables to check on locks.

The only way a SELECT statement will block another is if it has done: select for update.

Don't forget that for the inactive session, just because the *current* statement is a SELECT does not mean that was the statement the created the lock. A common scenario is:

Session 1:
==========
a- update some rows
b- run some selects
c- go inactive

Session 2:
==========
- blocked because session 1 has not committed the rows from the *update* in (a)

As you can see from the links above, Oracle does *not* keep a list of *rows* locked.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database