Graham,
I haven't used Oracle, but I've definitely wrestled with locking issues in
the past. Some ideas:
1) What's the locking granularity on your underlying database tables? You
almost certainly want row-level locking (most DBMSs offer locking at row,
page, and table level).
2) Maybe your isolation levels are too high. The _results_ of isolation
levels are pretty well-described, but how they're implemented can vary. On
Informix, for example, if there's a query performed at a high isolation
level, the rows returned are locked until the transaction commits. If
another transction attempts a query that could possibly contain one of
these rows, the second transaction receives an exception.
Knowing nothing else about your situation, I'd guess it's one (or a
combination) of the above. If it's 1), your life might improve simply by
setting locking to a finer granularity.
Tom
On Tue, 25 Apr 2000, Graham Parsons wrote:
> 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".
>
============================================================================
Tom Valesky -- [EMAIL PROTECTED] -- http://www.patriot.net/users/tvalesky
===========================================================================
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".
