Are procedures atomic in MySQL?

Question

As the title says, are procedures atomic in MySQL? 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.

Anson

Unless you use transactions - No. – Embarrassment

@PM77-1, even if you use transactions, no. – Electromagnetism

If "atomic" means "all or none" then transactions will work. – Embarrassment

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.

Electromagnetism

So am I right in saying that stored procedures don't guarantee even transaction commit level atomicity? – Anson

No, that's not right. If you start a transaction and run your procedure, the changes made within that procedure are part of the transaction, and then you can commit and all those changes will be committed atomically. – Electromagnetism

But stored procedures by themselves don't make any guarantees correct? – Anson

Correct. Stored procedures may run within the scope of a transactions, under control of the calling app. But they do not implicitly start and commit a transaction. – Electromagnetism

Answer 2

By default, a procedure is not atomic while a function is atomic in MySQL. *Atomic means rollback occurs if there is error and we can use transaction in a procedure while we cannot in a function.

For example, you create test table as shown below:

CREATE TABLE test (
  num int
);

Then, you insert the row whose num is 2 as shown below:

INSERT INTO test (num) VALUES (2);

Now, you create my_proc() procedure which updates num to 5, then causes error by SIGNAL statement as shown below:

DELIMITER $$

CREATE PROCEDURE my_proc() 
BEGIN
  UPDATE test SET num = 5;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred';
END$$ 

DELIMITER ;

Then, calling my_proc() gets the error but num is not rollbacked to 2 as shown below:

mysql> CALL my_proc();
ERROR 1644 (45000): An error occurred
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|    5 |
+------+

Actually, you can use a transaction outside my_proc() to rollback num to 2 as shown below. *My answer explains how to use a transaction inside my_proc() to rollback num to 2:

BEGIN;
CALL my_proc();
ROLLBAKC;

Next, you create my_func() function which updates num to 5, then causes error by SIGNAL statement as shown below:

DELIMITER $$

CREATE FUNCTION my_func() RETURNS INT 
DETERMINISTIC
BEGIN
  UPDATE test SET num = 5;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred';
  RETURN NULL;
END$$ 

DELIMITER ;

Then, calling my_func() gets the error, then num is rollbacked to 2 as shown below:

mysql> SELECT my_func();
ERROR 1644 (45000): An error occurred
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|    2 |
+------+

Spender