db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Atomic check for row existence and insert if doesn't exist
Date Wed, 14 Feb 2007 13:15:39 GMT

>>>>>>>>>>>> Ace Jayz wrote (2007-02-12 09:50:22):
> Ace Jayz wrote:
> > 
> > 
> > If REPEATABLE READ is in effect, then no other transaction would be able
> > to delete the row between the insert and the select because this would
> > result in
> > different results for the select statement if it were repeated.  For
> > example, the first execution of the select might return a row (if it
> > existed) but a second execution
> > of the same select statement might return 0 results if a delete was
> > allowed to sneak in before the select was executed.  Is this correct?
> > 
> > Thanks,
> > Ace.
> > 
> I'm going to correct myself here and say that SERIALIZABLE would probably be
> the right choice in this case, since REPEATABLE READ won't prevent phantoms. 
> I was trying to avoid SERIALIZABLE if possible to increase concurrency, but
> I don't think I have a choice in this case.  Is there any way to avoid
> SERIALIZABLE in this case or is using it in a short transaction with my
> requirements acceptable?

SERIALIZABLE is the right choice in your case. The transaction you
describe would be pretty short, so I guess it should not be a
performnace problem either. However, that will of course depend on
your application and one would need a detailed description of the
application and the anticipated load to figure that out.
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

View raw message