Are MySql stored procedures atomic?

Question

As the title says, are stored procedures in MySQL atomic? i.e. would something like

for (..)
  <check_if_row_has_flag>
for (..)
  <update_row>

work atomically?

Interestingly, I couldn't find much about this on Google except one forum thread from 2009.

Answer 1

No, stored procedures are not atomic.

The pseudocode you show above has a race condition. The first loop, checking if a row has a flag, would return an answer, but unless you do a locking read, another concurrent session could change the flag immediately after your procedure reads the row.

This is the effect of optimistic locking. Rows are not locked until you issue a statement to lock them. So even within a transaction, you don't have atomic locking.

The atomicity that MySQL supports is for transaction commit. Transactions are atomic in that all changes made during the transaction succeed, or else all are rolled back. Other sessions cannot see your transaction in a partially-complete state.

---

Re the comments below:

You can call a procedure within a transaction from your app:

START TRANSACTION;
CALL MyProcedure();
COMMIT;

You can even start and commit a transaction (or multiple transactions serially), explicitly in the body of a procedure:

CREATE PROCEDURE MyProcedure()
BEGIN
    START TRANSACTION;
    ...UPDATE, INSERT, DELETE, blah blah...
    COMMIT;
END 

But the procedure itself does not implicitly start or commit a transaction.