13 October, 2007

Inserts waiting on Locks ? Inserts holding Locks ?

When could we have a situation where a user says that his transaction is "slow" and it is merely inserting rows into a table -- an insert which used to complete in less than a minute but now has been "hanging" for a long time ? (He runs an "INSERT INTO TABLE A SELECT * FROM ....") You'd think that if the INSERT uses the SELECT query to build the data it is most likely the SELECT that is slow. You think that you need to tune the SELECT. (and worse where the SELECT is based on a distributed query -- you try to tune joins across databases).

But stop and think. If this INSERT was always much faster ("till yesterday !" he says), and is "hanging" now, you should look at where the user's session is Waiting.
Here's a case study (which I developed and tested on my Home PC) where an INSERT might wait ("forever") because it is actually attempting to insert a Duplicate Value (when there is a Unique Index) and is waiting on a "lock". Inserts should not need to wait on Locks. Moreover, if the duplicate value exists in the table, the INSERT should immediately fail with an ORA-0001 error. Yet we have a case where the INSERT waits on a lock !

On the flip side, an Insert does not need to "hold a lock", you expect because it is not updating data that someone else might attempt to update, it is after all "creating new data that no one else knows yet and, therefore, no one else needs to reference yet !".
In the case study, you see how Session 43 seems to hold a Lock and Session 45 does actually wait on a Lock. I've also suggested how you can extend the case study by creating a session where the second INSERT is based on a SELECT and your (or your DBA) would attempt to tune the SELECT if he doesn't look at session waits.

22 comments:

Anonymous said...

this is expected behavior..and not any surprise.

This is from docs (Concepts Guide)

Row Locks (TX)
---------------
"A transaction acquires an exclusive row lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause."

Its the expected behavior to ensure data consistency is not compromised.

Hemant K Chitale said...

A "lock" held by an INSERT isn't intuitive. Till the INSERT commits, that "row" will NOT be visible to others -- therefore there is no need to "lock" the row.
As I say "an Insert does not need to "hold a lock", you expect because it is not updating data that someone else might attempt to update".
If you see my Case Study, my query on v$session, v$access and v$lock does not show when there isn't a Unique Index and till the second session doesn't start "waiting" for the unique key that is held by the first session.

Anonymous said...

Here is a much simpler version:
-------------------------------
Create table t1 (c1 number primary);

session1:
insert into t1 values (1);

session2:
insert into t1 values (1);
-- would hang

v$lock would reveal session 1 blocking session2 on TX lock

why? because you have a constraint where you are asking Oracle to check uniqueness of data (unique index/unique constraint / primary key).

So Oracle looks as session-2's data and checks the existing data..it sees session-1's data uncommitted. Now Oracle doesn't know if session-1 would commit or rollback. so it has to wait. this is by design.its always been like this.(so session 2 has to enqueue behind session 1 )

if session-1 commits, session would fail since unique key constraint is violated..if session-1 rolls back, session-2 would go through fine.


Its a lock based on "data" . It doesn't matter what your statement is..("insert into table values.." or "insert based on select" )..the behavior would be the same.


You don't see the 2 sessions blocking each other (inserting the same key) when you don't have any consistency checks (unique index/constraint). Oracle knows it can allow any data..so no locks based on data.

Not sure why it sounds unintuitive to you..but its the way Oracle is designed to work and it has always been working this way.

Hemant K Chitale said...

I have presented exactly
the same thing in the Case Study. Have you read it ?
The Lock held by Session 1
is visible to us only when
Session 2 starts waiting on it, not till then.
(unlike UPDATE locks on _existing_ rows).

You (ok, not you you but most people I meet), do not expect an INSERT of a *new* row into a table to have to hold a lock.

As for whether it is an "insert" or "insert based on select" , read my posting again.
Let's say you are the DBA or Support Analyst for the application. The Tester or a User comes to you and says that "my insert into the table is taking a very long time. and oh ! by the way, it is based on a select from two other tables {or a select from a remote database}". What would most DBAs / Analysts first start looking at ?
They would think that the SELECT is slow and that is why the Insert isn't happening yet. Unless they look for locks (hey ! who thinks of looking for a lock when you are inserting a new row {which, "by definition" doesn't exist yet in the table} and doesn't need to lock or wait on a lock.)

Get my point ?

1. Most people wouldn't think of lock holders and lock waiters on an INSERT statement , generally

2. If the INSERT is based on a SELECT, most people would think that the SELECT is "slow" or "hanging".

Hemant

RajeshSahu said...

good, pls. provide the statement to check the locks in the v$ views.

Hemant K Chitale said...

If you read the case study.... you also read the sql.

Anonymous said...

Does this mean whenever we have primary key defined on a table, we can not do multiple session (concurrent) inserts with non unique values? For non unique values also second session has to wait for first session to commit.

Hemant K Chitale said...

Yes, it should be the same
behaviour, whether the
index is a Unique index
by itself or the Index is
a Unique index used to
enforce a Primary Key.

Wonder how a PK without an
Index would enforce this ...

Anonymous said...

I think it shows the locking information in v$lock, right? You can see a TM in shared mode(3) and a TX in EX mode (6) when you perform an insert. Please take a look:

SQL> create table t(c1 number primary key);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> select object_id from dba_objects where object_name='T' and owner='SYS';

OBJECT_ID
----------
140760

SQL> select distinct sid from v$mystat;

SID
----------
159

SQL> select * from v$lock where type in ('TX','TM');


ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00002AC85D420920 00002AC85D420980 159 TM 140760 0 3 0 4 0
0000000082674AE0 0000000082674B58 159 TX 589852 14722 6 0 4 0

2 rows selected.


Thanks,
Rijesh

Hemant K Chitale said...

The post is not about querying the status of the lock-holder (although I *have* demonstrated the locks in the Case Study).
It is about a session that mysteriously waits when attempting to insert a new row. In this case, you would wonder "why does an INSERT wait on a lock ?".

Anonymous said...

Thanks Hemant!!

This info helped me as i was trying to tune my query when INSERT based on SELECT was taking long time.

Anonymous said...

And is there any settings or resolution to overcome this situation?

Hemant K Chitale said...

Anonymous,

If you use a Sequence to generate the Unique numbers for the Primary / Unique key (or part of the key), you ensure that two different sessions do NOT attempt the same key value.

Hemant

Karan said...

This was brilliant! Very very helpful..

I'd expect my SMON process to find and repair such deadlocks automatically.

Does this mean that when inserting data in a highly concurrent environment the table should NOT have any primary keys?

What if I cannot ensure when 2 rows are being inserted, they might have the same primary key..? What do I do then?

Have you found a solution to avoid this scenarios during runtime?

Hemant K Chitale said...

Karan,

1 It is not a deadlock. Session B is waiting on Session A but Session A is not waiting on Session B. So it is not a deadlock, it is a "normal" enqueue.

2 It is not SMON's job to find and repair this situation

3 You should have Primary Keys but you should avoid two sessions generating the same Key value. A sequence, for example, prevents such a situation.

Hemant

Muhammad Ismail said...

Simple query if we have case like

Create table t1 (c1 number primary);

session1:
insert into t1 values (1);

session2:
insert into t1 values (2);
it would hang/wait for session1 to commit/rollback transaction or not. As we are inserting different values

Muhammad Ismail said...

Also in case both sessions inserted the same value, first will succeed, is there ways that 2nd session update that row instead of throwing error. Like we have another column for timestamp & want that 2nd session instead of throwing error simple update the timestamp column ?

Hemant K Chitale said...

Muhammad,
In your first case, session 2 would NOT hang/wait for session 1. It is a different key value so there is no wait on the index. (there can be boundary conditions with a pre-existing index and concurrent dml where leaf block splitting may occur and waits may occur for undo slots or itl entries).

In your second example, what you'd have to do is write code to "trap" the error (i.e. exception) returned and retry with an update. Straight SQL can't do it. You'd have to use PL/SQL or a programming language (Java or C)

Hemant

Unknown said...


Hi Hemant, This case study link is not working so could you please mail this it to me at it.nitin@gmail.com. Thanks

Hemant K Chitale said...

Nitin,
I've updated the URL to another site on on google docs.

oracle9205 said...

Hemant,
I am unable to download case study, error is "no preview available". Can you fix this please.

Hemant K Chitale said...

ora9205 :
I have updated the link to the Case Study. It is a PDF file.

Please try accessing it again.