The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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.

We're not taking comments currently, so please try again later if you want to add a comment.

More to Explore

Administration

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

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

Administration