Re: Transaction size limitation for serializable isolation level?

From: VC <boston103_at_hotmail.com>
Date: Mon, 19 Apr 2004 01:29:50 GMT
Message-ID: <iYFgc.23785$yD1.57369@attbi_s54>

Could you post the piece of code which causes the problem ? Are you quite sure there are no concurrent transactions (I remember you've mentioned there was only one transaction but a single user may have two concurrecnt connections open in different threads).

This sequence will produce a "legitimate" error:

In sqlplus:

Session 1:

SQL> set transaction isolation level serializable;

Transaction set.

SQL> update t1 set x=0;

1 row updated.

Session 2:

SQL> set transaction isolation level serializable;

Transaction set.

SQL> update t1 set x=2;
.. blocked by Session 1

Session 1:

SQL> commit;

Commit complete.

Session 2:

update t1 set x=2

*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

If you are not hitting the bug I mentioned in my previous message and the INITTRANS parameter is resonable (more than, say, 3), the only scenario I can think of is the one I described above.

A "serializable" transaction does not have to be "long" -- it's just that the probability of two (or more) transactions clashing over the same row(s) is proportional to the transaction(s) duration.

Original Message ----- From: "Lusiana Lusiana" <lusiana_at_dummybit.nortelnetworks.com> Newsgroups: comp.databases.oracle.server Sent: Sunday, April 18, 2004 8:27 PM Subject: Re: Transaction size limitation for serializable isolation level?

> Hi VC,
>
> Thank you for the reply. This is the first answer that makes a lot of
sense
> to me.
> I had been trying to find information on this, but none of them were
> conclusive to me.
>
> I tried to run the test script as display on the webpage and it ran
> successfully.
> Querying the table at the end of the run gives me an expected answer
> (one poster made a comment that Oracle put some dodgy fix, and it could be
> shown by querying the table).
>
> Is there any other way to prove that my system (8.1.6) is plagued by this
> bug?
>
> Regards,
> Lucy
>
>
> "VC" <boston103_at_hotmail.com> wrote in message
> news:BYygc.21058$yD1.48596_at_attbi_s54...
> > Hello,
> >
> > Pls. see below:
> >
> > "Lusiana Lusiana" <lusiana_at_dummybit.nortelnetworks.com> wrote in message
> > news:c5nr1t$qqg$1_at_zcars0v6.ca.nortel.com...
> > > Hi All,
> > >
> > > Our system configuration is Oracle 8.1.6 on Solaris 5.8 with
servlet/jsp
> > > using
> > > Tomcat as client. Transaction isolation level is set to serializable.
> > >
> > > We have some long transactions, and we have been getting 'ORA-08177:
> > Cannot
> > > serialize access for this
> > > transaction' for these.
> > > The error were also experienced when there's only one user on the
> database
> > > (ie.one transaction).
> > > Redoing the transaction didn't help.
> >
> >
> > There is an bug related to the SERIALIZABLE IL that Oracle had neglected
> to
> > fix for quite a while until the 9.2 version.
> > Pls. see the link below for more information:
> >
> >
>

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=31e0625e
> >
>

.0310210612.5aa62f57%40posting.google.com&rnum=3&prev=/groups%3Fq%3Dvc%2520s
> >
>

erializable%2520joe%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26sa%3DN%26t
> > ab%3Dwg
> >
> >
> > The "solution", if at all possible, would be to upgrade to 9.2
> >
> > Rgds.
> >
> > VC
> >
> >
>
>
Received on Sun Apr 18 2004 - 20:29:50 CDT