On transaction atomicity

gistfile1.txt

If someone interesting - answer to the question:

> Isn't mysql ensuring atomicty within tx?

It solely depends on the Isolation level transactions are executed against. 
If we use SERIALIZABLE in database which guarantees real _serializabiltiy_ 
for SERIALIZABLE and not something else (like an Oracle) that transactions 
are atomic, yes. Or you can think of them as of atomic because their execution 
is equivalent to some serial logically correct history.

But as far as it is almost 100% probability that we don't use SER, we can
expect RC or SI. And UI at worst.

So now when we do read-write cycle transaction we want really be sure that 
our update don't get lost. Mainly because we do that like a CAS operation. 
And isolation level does not guarantee neither consistency neither atomicity.
This is lol, but mainly due to the fact that the atomicity is not that the
execution history is equivalent to some sequential history in which transactions
are points on a straight line, but that either all of the updates are applied,
or none. So this is different atomicity. It's more about phisycal integrity of
tx it self. But we still can see different side effects of concurrently running
transactions.

So, how we can fix? What we want to be sure is that our update dont get lost:

```
x = *K
            x' = *K
*K = y
commit
            *K = y'
            commit

use x invalid. and totally fine under READ COMMITTED.
```

we want it to be either

```
mutex.Lock()
x = *K
*K = y
mutex.Unlock()
use x
```

or

```
do {
x = *K
y = x
y.version ++
} while (CompareAndSwap(K.version, y.version-1, y))
use x
```

So, now, how we can express it in SQL:

```
BEGIN TRANSACTION
SELECT X FOR UPDATE
UPDATE X
COMMIT
```

or

```
BEGIN TRANSACTION
SELECT X
rows = UPDATE X WHERE X.version = Y
COMMIT
if rows == 0 -> retry
```

or

```
BEGIN TRANSACTION
SELECT X
rows = UPDATE X SET X.blah = X.blah +1 WHERE X.blah = CONST
COMMIT
if rows == 0 -> retry
```

or

```
BEGIN TRANSACTION
UPDATE X SET X.blah = X.blah +1 // <--- sets exclusive lock
SELECT X
COMMIT
use X.blah-1 as it was atomic yes. but we reserved previous, not current value.
```