>
> A few more details to back up my earlier assertions -- which
> I still stand by.
>
[...]
>
> However, the error reported by Oracle for both cases
> is identical
If so then this has changed since I last looked in detail (or my memory is
playing tricks, either is possible.) You get "ORA-08177 can't serialize
access for this transaction" if the changed data was committed since you
read it (only possible if isolation level is not Serializable). My memory
says you got a different error in the "don't know" case but I can't find any
document that specifies what; the "Concepts" manual just says it is possible
for this to happen without explaining what follows.
...
>
> (b) even if there is a practical way to retry using
> other means we'd have to do it for all failures --
> "really failed" and "don't know". FWIW: I don't
> think it's a reasonable approach because it would
> necessitate changing the isolation level of the
> transaction in mid-stream.
If the app really can't tell the difference then yes, you would. But if the
app can tell then, if a tx represents an attempt to acquire some unique
resource it makes sense to retry after a "don't know" but not after a
"really failed" (because you know it will just fail again). Not sure why
this necessitates changing isolation level - why not just retry at the same
isolation level?
...
> (1) LOAD SPIKES DON'T EQUAL COLLISION SPIKES
>
> It is true that real world (especially web) applications
> have usage (load) profiles that are difficult to predict.
> However, well-designed applications should be constructed
> such that UPDATE COLLISIONS are truly exceptional conditions:
> husband and wife accessing account simultaneously from their
> respective places, for example.
>
Isn't this the same as saying, "don't use Serializable if you expect to get
a lot of update collisions (i.e. your tx isn't really very serializable)"?
Which brings us back to the question, what do you use when the nature of the
application is such that collisions are not exceptional? e.g. online users
of a travel site selecting their own seats. When users have unique resources
(as in your example) then sure, load spikes and collision spikes are
independent; but when users are competing for a shared resource the two are
correlated.
Or, in the words of Oracle's 8i Concepts manual,
<quote>
Oracle's serializable isolation is suitable for environments where there is
relatively low chance that two concurrent transactions will modify the same
rows and the relatively long-running transactions are primarily read-only.
It is most suitable for environments with large databases and short
transactions that update only a few rows.
...
Serializable mode is probably not the best choice in an environment with
relatively long transactions that must update the same rows accessed by a
high volume of short update transactions. Because a longer running
transaction is unlikely to be the first to modify a given row, it will
repeatedly need to roll back, wasting work.
</quote>
I don't believe that you can always design away these conditions, however
good a designer you are. I think its somewhat begging the question to just
assert that "well designed" applications can mostly avoid update collisions.
In my experience the real world is not a well designed place! However, I
suspect that we are just going to have to agree to disagree on that point.
> (2) ALLOWING FOR UNUSUAL COLLISION SPIKES IS SPACE
> EFFICIENT
>
> The size of Oracle's interested transaction list -- one
> list per data block -- is variable in size ranging from
> INITRANS to MAXTRANS. ITL is the resource that, when
> depleted, yields the "don't know" result.
>
> I assert that application designers and DBAs can collaborate
> to establish a MAXTRANS values that would make "don't know"
> results virtually impossible -- without wasting space under
> normal (non-spike) conditions.
>
It used to be the case that MAXTRANS was determined by the size of the data
block and the DBA is not supposed to override it - did that change? In any
case, the setting of INITTRANS and MAXTRANS takes away from the data block,
so it has to be done with care. MAXTRANS is also capped at 255 regardless of
block size. Even if you can get a sufficiently high MAXTRANS, "virtually
impossible" <> "impossible", and you still have to decide how you are going
to deal with it when it happens.
Incidentally, the same Concepts manual cited above also advises against
using Serializable in high-collision scenarios because of the costs of
rollback.
===========================================================================
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".
