Yes, we experienced this problem; but the Weblogic documentation doesn't
cover the half of it. The problem occurs most frequently during inserts,
not updates. It is not a contention for the same row, but a "contention"
for the same block. Oracle database blocks have a list of ITL (?don't
know what it stands for) entries at the head of each block; these blocks
are consumed as transactions modify rows within the block. If you use
all the blocks up, Oracle can't guarantee that the transaction is still
serializable will return ORA-08177.
You are supposed to be able to change the number of ITL entry blocks by
setting the physical table properties INITTRANS and MAXTRANS. (The
default is 1 for table blocks.) But we found that this didn't help, as
Oracle doesn't reclaim the ITL entries as soon as a transaction is
resolved. This left us vulnerable to the error.
After a lot of wasted time, and a lot of back and forth with Oracle
support, we gave up. We tried everything they suggested, to no avail. I
really wanted to work directly with the developers on this one, but
never managed to get past support.
We also found that if you are going to use SERIALIZABLE isolation, you
are supposed to set a flag "serializable=true" in the init.ora. Setting
this flag causes Oracle to acquire table locks in many cases where it
generally only acquires a row lock. This blows your concurrency all to
hell, dramatically limiting the amount of transactions you can push
through the database.
We were not very happy with Oracle about this issue. Oracle should not
claim support for SERIALIZABLE isolation, because the feature isn't
usable. Upon re-thinking our application design, we have been able to
make it work with READ COMMITTED isolation.
You can fight Oracle support on this one, or you can go with the flow.
I'd recommend the latter, having spent a lot of time attempting the
former.
-eric
-----Original Message-----
From: graham.parsons [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 25, 2000 7:21 AM
To: EJB-INTEREST
Subject: Oracle transaction serialisation problem
With respect to the Weblogic documentation on Oracle transaction
serialisation:
----
In particular, you should be aware that Oracle uses optimistic
concurrency. As a consequence, even with a setting of
TRANSACTION_SERIALIZABLE, it does not detect serialization problems
until commit time. The message returned is:
java.sql.SQLException: ORA-08177: can't serialize access for this
transaction
Even if you use TRANSACTION_SERIALIZABLE, you will get exceptions and
rollbacks in the client if contention occurs between clients for the
same rows. You'll need to write your client to catch any SQL exceptions
that occur, examine them and take appropriate action, such as attempting
the transaction again.
----
1) Has anyone seen this problem - we certainly have !
2) Can the occurrence of the problem be alleviated by changing the
database table structure (we are effectively inserting sequential keyed
rows; will a hash storage structure help) ?
3) Can anyone explain why we are seeing this when two users are
inserting into the database (as opposed to concurrency problems on the
same database table row) ?
4) How have others got around this - are there better solutions than
retries and if not, are you allowed to get an EJB to Thread.sleep()
between attempts ?
Many TIA
Graham
========================================================================
===
To unsubscribe, send email to [EMAIL PROTECTED] and include in the
body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
