Re: Transaction size limitation for serializable isolation level?

From: VC <boston103_at_hotmail.com>
Date: Mon, 19 Apr 2004 01:43:09 GMT
Message-ID: <N8Ggc.23872$yD1.57579@attbi_s54>

Also, before running the test below, make sure the test table has the primary key. Here's a full transcript from an 8.1.7.4 system:

---

Oracle8i Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production

SQL> create table isolationtest (id char(250) primary key, val float); begin

Table created.

SQL> 2 for i in 1..200 loop

  3      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  4      insert into isolationtest (id,val) values (i,100);
  5      update isolationtest set val = 200 where id = i;
  6      COMMIT;

  7 end loop;
  8 end;
  9 /
begin
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction ORA-06512: at line 4

---

VC

"Lusiana Lusiana" <lusiana_at_dummybit.nortelnetworks.com> wrote in message news:c5v68s$8hj$1_at_zcars0v6.ca.nortel.com...
> 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:43:09 CDT