Re: A Question About Oracle

From: Bhooshan S. Prabhu <bhooshan.prabhu_at_citicorp.com>
Date: 25 Jun 2001 23:16:02 -0700
Message-ID: <2cf4efe2.0106252216.5dd0e27c@posting.google.com>

h_a_noori_at_yahoo.com (H.A. Noori) wrote in message news:<fe2f49e3.0106150747.54aedf7f_at_posting.google.com>...
> Dear all member of comp.database.oracle.server
> I have a problem with oracle as below:
> when I reserve a record for update after a savepoint and then I
> rollback to
> that savepoint, the reserved record stay locked until I run commit or
> rollback command. for example:
> .
> .
> Update ... # Operation A
> .
> .
> Savepoint MySave
> .
> .
> Select *
> From MyTable
> Where MyKey = 1234
> For Update
> .
> .
> Rollback To MySave #At this point I need to release MyTable without
> #losing other database operation like operation A
> .
> .
>
> Can you help me?
> Thanks All.
> H.A. Noori.

Hello,

Came across the following post on metalink. Hope it's of help.

Regards
Bhooshan

---

From: H?kan Etzell 21-Jan-00 08:30
Subject: Savepoints and User-Locks

RDBMS Version: 8.0.6
Operating System and Version: NT4 & HP/UX 11 Error Number (if applicable):
Product (i.e. SQL*Loader, Import, etc.): Product Version: 7.3.4

Savepoints and User-Locks

I have a problem regarding savepoints and user locks, and I don't understand the behaviour in the
database?.

Scenario 1)
1) Call dbms_lock.request(12345,6,0,TRUE). -- Exclusive Request UL 12345. Release commit/rollback.

2) SELECT from emp for update; 
3) Savepoint X; 
4) Call dbms_lock.request(54321,6,0,TRUE). -- Exclusive Request UL

54321. Release commit/rollback
5) SELECT from dept for update;
6) ROLLBACK to savepoint X;

At this point both user locks are still held, but lock on dept table is released. Why? All locks
are released when doing a full rollback.

Scenario 2:
1) Savepoint X;
2) Call dbms_lock.request(54321,6,0,TRUE). -- Exclusive Request UL 54321. Release commit/rollback
3) Rollback to savepoint X;

Here Oracle 7.3.4.5 and Oracle 8.0.5.2, 8.0.6 and 8.1.5 differ in behaviour. Oracle7 releases the
user lock, but any Oracle8 version still holds the lock until a full rollback is made.

Is this a bug?

---

From: Oracle, Ken Robinson 21-Jan-00 21:32 Subject: Re : Savepoints and User-Locks

I think bug 717373 speaks to both scenarios. From the bug, development states,

"...rollback to savepoint frees the row locks, in both version 6, 7 and 8. The issue here has to do with when a session is waiting for a row lock. the behavior as seen is correct."

In other words, rollback to savepoint does not release row level locks, the waiting process "sees" the transaction as still active. Remember that the lock is set under the current transaction, and performing the rollback to savepoint does not cancel the current transaction, it just causes undo to be applied from the current time back to the scn of the savepoint.

Hope that helps.
Regards,
Ken Robinson
Oracle Server EE Analyst

---

Received on Tue Jun 26 2001 - 01:16:02 CDT