Re: I thought single UPDATE statements were atomic

[Mark Rages]

On Fri, Jun 06, 2003 at 01:07:43PM +0200, Jon Haugsand wrote:
> * Mark Rages
> > According to the docs, single update statements are atomic.
> 
> I would not say that your problem is with atomicity, but with
> consistency.  It is expected that a transaction oriented database
> should follow the ACID properties, and with them, any consistency
> properties should not be evaluated until the transaction is
> committed.  Appearantly MySQL is not able to do so.  (Which is perhaps
> a trade off between speed and functionality.)
> 
> Why don't you just drop the unique criterium and do the consistency
> check by youself?
> 

I'm using InnoDB tables, which are reputed to be ACID compliant.
Same problem.

I guess I will follow your suggestion and just drop the UNIQUE
constraint since it appears to be evaluated after each row is
UPDATEd and not after the UPDATE completes.

I'll check out how Postgres and Firebird handle this.  
(Anyone want to try it on Oracle for me?)

Regards,
Mark
markrages@stripped