Re: Still the case that rolling back to savepoint does not restart blocked sessions?

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Thu, 29 May 2014 22:30:23 +0400
Message-ID: <CAOVevU4-i-8g2JsMiFke5fEzAW1TLyeQWNXK_+ovYw+VUWTvcQ_at_mail.gmail.com>

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_at_gmail.com>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_at_subaru.com>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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 29 2014 - 20:30:23 CEST