RE: Still the case that rolling back to savepoint does not restart blocked sessions?
- From: "McPeak, Matt" <vxsmimmcp@xxxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>, "xt.and.r@xxxxxxxxx" <xt.and.r@xxxxxxxxx>
- Date: Fri, 30 May 2014 14:42:50 +0000
Jonathan,
Thanks for your answer! I wasn't aware of that special condition: I'll have to
think about that.
Thanks again!
Matt
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, May 29, 2014 5:17 PM
To: xt.and.r@xxxxxxxxx; McPeak, Matt
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Still the case that rolling back to savepoint does not restart
blocked sessions?
No change in 12c.
There is a special case (in all versions) for pl/sql blocks -
if the first modification of the pl/sql block is the first modification of a
transaction, and the savepoint is created before the first change, then the
rollback to savepoint becomes a rollback of the complete transaction, viz: the
following does a complete rollback:
commit;
begin
savepoint a;
update t1 set v1 = upper(v1) where id = 1;
rollback to savepoint;
end;
/
But this one leaves a transaction and lock in place:
commit;
begin
update t1 set v1 = lower(v1) where id = 1;
savepoint a;
update t1 set v1 = upper(v1) where id = 1;
rollback to savepoint;
end;
/
I recall a conversation many years ago describing some plans to allow the
blocked session to check the locked row every 3 seconds to see if the lock had
been released - but it looks as if that hasn't happened yet. (It's an
interesting quirk of this situation that a row change that has been rolled back
by a rollback to savepoint can be updated by a third session while the second
session is still waiting for it (though really it's underlying transaction) to
commit or rollback.)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx] on behalf of Sayan Malakshinov
[xt.and.r@xxxxxxxxx]
Sent: 29 May 2014 19:30
To: Matt McPeak
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: Still the case that rolling back to savepoint does not restart
blocked sessions?
Oh, sorry, I have understood and even tested it on 12c: the same behaviour
persists on 12.1 too:
Fixed test:
--first session:
drop table xt_test purge;
create table xt_test(id primary key, x) as select level,level from dual connect
by level<=10;
begin
update xt_test set x=-x where id=1;
savepoint sp1;
update xt_test set x=-x where id=2;
dbms_lock.sleep(10);
rollback to sp1;
dbms_lock.sleep(30);
end;
/
-- lock in second until full commit/rollback:
update xt_test set x=x*10 where id=2;
On Thu, May 29, 2014 at 10:16 PM, Sayan Malakshinov
<xt.and.r@xxxxxxxxx<mailto:xt.and.r@xxxxxxxxx>> wrote:
Matt,
could you provide your example? Because I never seen such behaviour(or maybe i
didn't understand you), and my test that I've made now, showed normal unlock.
My test:
--First session:
create table xt_test(id primary key, x) as select level,level from dual connect
by level<=10;
begin
update xt_test set x=-x where id=1;
savepoint sp1;
update xt_test set x=-x where id=2;
rollback to sp1;
dbms_lock.sleep(30);
end;
/
-- and now in second:
update xt_test set x=x*10 where id=2;
On Thu, May 29, 2014 at 10:02 PM, McPeak, Matt
<vxsmimmcp@xxxxxxxxxx<mailto:vxsmimmcp@xxxxxxxxxx>> wrote:
If transaction A is waiting on a lock held by transaction B and transaction B
rolls back to a savepoint (releasing the lock), transaction A remains blocked
until transaction B ends completely (with either a full rollback or a commit).
Does anyone know if this is still the case in 12c? I really hate this behavior.
Thanks,
Matt
--
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org
--
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org
Other related posts:
