11

Without using explicit transactions, is:

update tokens set tokens = tokens + 1

guaranteed to be atomic in InnoDB?

Pacerier
  • 86,231
  • 106
  • 366
  • 634
Limbeh
  • 221
  • 1
  • 7

2 Answers2

1

My understanding is that in autocommit mode (i.e. "without explicit transactions") a single statement is a single transaction. As a transaction is per definition atomic, so your single statement is atomic as well.

However, when it comes to evaluating constraints, this is not done on the statement (i.e. transaction) level, but row by row while the statement is processed.

If you have a unique constraint (index) on the tokens column, that update will most probably fail because of that. The same is true for foreign keys referencing the same table.

  • 1
    http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html Has an example where it does "select for update" before issuing the update statement. Does that imply that this form of update is not atomic? I'm using the default isolation level. thx – Limbeh Dec 08 '11 at 23:25
  • @Limbeh: in the example the UPDATE is atomic (there is a single row in `child_codes`), the SELECT ... FOR UPDATE (taking an exclusive lock/write lock) is done to serialize the SELECT/UPDATE combo with respect to other sessions (i.e: to make the whole SELECT/UPDATE atomic and consistent). – ninjalj Dec 09 '11 at 01:17
  • Thanks. The example also says that select for update requires autocommit to be off, or start transaction. Question: if I already use start transaction, would I even need to bother with select for update? I thought transactions are atomic already. – Limbeh Dec 11 '11 at 11:33
  • 1
    Sorry one more question, would this be atomic as well if i constrain it to single row: "update table a=a+b+1,b=0" – Limbeh Dec 11 '11 at 11:44
  • Yes that would be atomic as well –  Dec 11 '11 at 12:03
  • @Limbeh: about select for update inside of a transaction: yes, you need that, unless you are at SERIALIZABLE isolation level (which in MySQL takes a shared lock on SELECT). Transactions are only atomic with respect to other transactions if they both are at SERIALIZABLE isolation. Normally, transactions are only atomic with respect to storage. – ninjalj Dec 12 '11 at 19:06
  • @a_horse_with_no_name, Is the atomicity of the statement in the question still guaranteed if we are using `MyISAM`? – Pacerier Dec 18 '14 at 07:32
  • @Pacerier: no it does not. MyISAM does not even have atomic statements, let alone read consistency on statement level. Not even InnoDB has statement level read consistency for DML statements: `update foo set a=b, b=a` will also not work with InnoDB –  Dec 18 '14 at 07:35
  • @a_horse_with_no_name, Hmm, the other thead at http://stackoverflow.com/q/7734272/632951 seems to suggest that MyISAM single statements are atomic. – Pacerier Dec 19 '14 at 02:44
  • @Pacerier: single statements are atomic in their result, but DML statements don't operate in a read consistent manner. Try the update with swapping column values. –  Dec 19 '14 at 06:56
0

I think it isn't. Don't have my MySQL isolation levels/cheat sheet available, but I think it would be atomic on a per-row level (UPDATE would use range-locks IIRC), but not on a per-table level.

Now, a more interesting question than whether UPDATE is atomic, is when the non-atomicity of UPDATE is observable. The answer is that single statements in READ UNCOMMITTED isolation level can observe the non-atomicity of UPDATE, and a series of related statements in a single transaction in READ COMMITTED isolation level can observe different updates. REPEATABLE READ and SERIALIZABLE see UPDATEs as though they were atomic and consistent.

Imagine two sessions. Session A has isolation level SERIALIZABLE and does: UPDATE tokens SET tokens = tokens + 1 in a table with 1000 rows.

  • Suppose Session B has isolation level READ UNCOMMITTED and does SELECT sum(tokens) FROM tokens → This select can see a partial update (i.e: some rows updated and other rows not updated).
  • Suppose Session B has isolation level READ COMMITTED and does SELECT sum(tokens) FROM tokens → This select cannot see a partial update, so it sees the UPDATE as though it was atomic.
  • Suppose Session B has isolation level READ COMMITTED and does SELECT sum(tokens) FROM tokens WHERE id BETWEEN 1 AND 100; SELECT sum(tokens) FROM tokens WHERE id BETWEEN 501 AND 600 and then some program logic adds those two values → These SELECTs can see different MVCC snapshots → different updates,
  • Suppose Session B has isolation level REPEATABLE READ and does SELECT sum(tokens) FROM tokens WHERE id BETWEEN 1 AND 100; SELECT sum(tokens) FROM tokens WHERE id BETWEEN 501 AND 600 and then some program logic adds those two values → These SELECTs cannot see different MVCC snapshots → they see the same update,
ninjalj
  • 42,493
  • 9
  • 106
  • 148
  • Anyway, the locking done by UPDATE is documented in MySQL docs. – ninjalj Dec 06 '11 at 22:30
  • 1
    If it wasn't atomic, then InnoDB's "transaction" aren't transactions at all. A single statement should (actually: has to) work atomically - and it has to see a consistent state of the table while it's running. –  Dec 06 '11 at 22:52
  • @a_horse_with_no_name: so would that mean that InnoDB has to set an IX (Intention Exclusive) lock on the whole table, then set either a table lock or index/gap/next-key locks for each affected row, and finally update the rows even in < REPEATABLE_READ isolation levels? That's not my interpretation of READ COMMITED in http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html – ninjalj Dec 06 '11 at 23:47
  • Say, you have two READ UNCOMMITED transactions, one does the UPDATE, another does a SELECT to count the total number of tokens. I think you could get an intermediate value. – ninjalj Dec 06 '11 at 23:51
  • READ **UN** COMMITTED simply turns off all transaction handling –  Dec 07 '11 at 08:04
  • @a_horse_with_no_name: by that reasoning, only SERIALIZABLE has transaction handling. – ninjalj Dec 07 '11 at 20:58