Sorry if this is a dumb question, but are SQL transactions in MySQL atomic?
I'm using InnoDB tables in MySQL5.

Clearly, transactions have the property that either all updates occur or
none do. By atomic, I mean are other queries guaranteed to either see all
changes from the transaction or none of them?

An obvious example of the potential problem if they are not atomic (with
regards to visibility):

Transaction {
Query1 - update credits_table with +$20
Query2 - update debits_table with -$20
}

Separate process running periodically:
Query3 - compute total credits and debits

Could query3 ever see the total of credits and debits as being out of
balance?

If the transaction doesn't guarantee that, is there any other way to force
atomic visibility?

Doug

Hi Douglas,

Douglas Pearson wrote:
> Sorry if this is a dumb question, but are SQL transactions in MySQL atomic?
> I'm using InnoDB tables in MySQL5.
>
> Clearly, transactions have the property that either all updates occur or
> none do. By atomic, I mean are other queries guaranteed to either see all
> changes from the transaction or none of them?

You're actually asking about isolation -- the I in ACID. yes, MySQL
transactions are ACID. And MySQL and InnoDB implement all four levels
of transaction isolation described by the SQL standard. Most RDBMSs
don't, so MySQL is actually a good bit more complicated to use correctly
with respect to transactions and isolation levels.

> An obvious example of the potential problem if they are not atomic (with
> regards to visibility):
>
> Transaction {
> Query1 - update credits_table with +$20
> Query2 - update debits_table with -$20
> }
>
> Separate process running periodically:
> Query3 - compute total credits and debits
>
> Could query3 ever see the total of credits and debits as being out of
> balance?

Yes, if you choose the wrong isolation level.

> If the transaction doesn't guarantee that, is there any other way to force
> atomic visibility?

Choosing the right isolation level will do what you need. It will not
guarantee no one can ever see inconsistent data, so if you're trying to
prevent people from being able to, you can't. If you're trying to
design applications so they WON'T, you can do that.

You should read the section on InnoDB Transaction Model and Locking,
especially this section (but read the whole thing, it is very complex):
http://dev.mysql.com/doc/refman/5.0/...isolation.html

Baron