Hi,

My system configuration: Oracle 8.1.6 on Solaris 5.8 with servlet/jsp using
Tomcat as client.
Transaction isolation level is set to serializable.

One of the tables has records referencing other records on the same table
(no circular referencing).
For a versioning purposes, when a record is updated, a new version of this
record is created.
And in turn, using triggers, new version will be created for every
referencing records.
A commit is performed at the end of these..

I have been getting the error 'ORA-08177: Cannot serialize access for this
transaction'
when updating records that are referenced by a large number of records
(when updating records that generates large number of other record updates).
They're fine for other smaller transactions.

From Oracle manual:
'Oracle generates an error when a serializable transaction tries to update
or
delete data modified by a transaction that commits after the serializable
transaction began'
but I'm the only person initiating the transaction (I'm the only one on the
test server),
so as far as I understand, there shouldn't be other transaction doing
commits (???)

What could be causing the error?

Are there any system/table parameters to adjust to avoid getting this error?

Thank you,
Lucy

"Lusiana Lusiana" <lusiana@dummybit.nortelnetworks.com> wrote in message
news:c28pj3$k0t$1@zcars0v6.ca.nortel.com...
> Hi,
>
> My system configuration: Oracle 8.1.6 on Solaris 5.8 with servlet/jsp
using
> Tomcat as client.
> Transaction isolation level is set to serializable.
>
> One of the tables has records referencing other records on the same table
> (no circular referencing).
> For a versioning purposes, when a record is updated, a new version of this
> record is created.
> And in turn, using triggers, new version will be created for every
> referencing records.
> A commit is performed at the end of these..
>
> I have been getting the error 'ORA-08177: Cannot serialize access for this
> transaction'
> when updating records that are referenced by a large number of records
> (when updating records that generates large number of other record
updates).
> They're fine for other smaller transactions.
>
> From Oracle manual:
> 'Oracle generates an error when a serializable transaction tries to update
> or
> delete data modified by a transaction that commits after the serializable
> transaction began'
> but I'm the only person initiating the transaction (I'm the only one on
the
> test server),
> so as far as I understand, there shouldn't be other transaction doing
> commits (???)
>
> What could be causing the error?
>
> Are there any system/table parameters to adjust to avoid getting this
error?
>
> Thank you,
> Lucy
>
>

Lucy

If you repeat the same transaction, do you get the same error ?
We use to treat 08177s as "Data locked try again later" type errors.
We would rollback then delay exponentially (with a limit) until it
completed.
It certainly seems strange if you only had one connection to the database at
that time.

Regards

eric


--
Remove the dross to contact me directly

"Eric Parker" <eric.parkerthedross@virgin.net> wrote in message
news:ArZ1c.763$sq2.444@newsfe2-gui.server.ntli.net...
[stuff deleted]
>
> Lucy
>
> If you repeat the same transaction, do you get the same error ?
> We use to treat 08177s as "Data locked try again later" type errors.
> We would rollback then delay exponentially (with a limit) until it
> completed.
> It certainly seems strange if you only had one connection to the database
at
> that time.
>
> Regards
>
> eric
>
>
> --
> Remove the dross to contact me directly
>
>
Hi Eric,

Yes, I re-tried the same transaction and it kept returning the same error.

I have had other kinds of transactions returning the same error on the
production system
where there are other users. Retrying these transaction solved the problem.
But not this particular transaction.
So, I'm baffled by this.

Cheers,
Lucy