Is 'update tokens set tokens = tokens + 1' atomic in InnoDB?

Question

Without using explicit transactions, is:

update tokens set tokens = tokens + 1

guaranteed to be atomic in InnoDB?

Answer 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.