Atomicity of stored functions

Question

Unlike stored procedures, which can explicitly start & commit transactions, stored functions can't contain "Statements that perform explicit or implicit commits or rollbacks". Documentation is pretty clear about that, it's getting a little vague, when it comes to explain, why it is prohibited, by merely stating that SQL "standard" doesn't require it & leaves it up to the vendor. I wonder however, does atomicity of commands such as SELECT or DO, affect stored function, when it's called within their context? In such case, commands contained within stored function, are treated as one transaction or each one is as separate transaction (like stored procedure, without START TRANSACTION)? The first one seems to be true, it would even explain why it's forbidden to commit transaction within function body, also in case if something goes awry, then every change can be rolled-back automatically by SELECT or DO, this way maintaining "all or nothing" approach, but still it's more implied, than official straight-forward answer, documentation should cover this matter but it doesn't.

[It's purely theoretical matter]

The function is always called in the query. Each query is single transaction. So ALL function calls are performed in this single transaction. Also - a query is minimal transaction granularity, so there cannot exist sub-transactions in it, and this is a reason why transactions are not allowed in the function. — Akina, May 05 '23 at 05:55

score 0 · Answer 1 · answered May 05 '23 at 14:46

There are 3 ways to do a transaction:

Explicit. Necessary when there are multiple statements:
```
 BEGIN
 ...
 COMMIT
```
AUTOCOMMIT=ON. In this case a single statement implicitly has a BEGIN and COMMIT:
```
 UPDATE ...
```
AUTOCOMMIT=OFF. I avoid this because it requires eventually performing a COMMIT.
```
 UPDATE ...
 UPDATE ...
 COMMIT
```

As you point out, a Stored Procedure, but not Function, can can encapsulate any of these patterns.

can't contain "Statements that perform explicit or implicit commits or rollbacks".

I think they are referring simply to DDL statements (CREATE, DROP, ...) which, until MySQL 8.0, do implicitly terminate any pending transaction, as in cases 1 and 3 above. Suggest you file a documentation bug report about the confusion of that statement

SQL "standard"

MySQL half-heartedly follows any standard. MariaDB does a much more serious job of trying to follow the standard.

Since a Stored Function is called from a statement

UPDATE ... SET x = fcn1(...) WHERE y = fcn2(...)

it makes sense to think of those calls as short hand for subqueries in similar locations. So, in this example, I would expect the implicit BEGIN and COMMIT to be at least around the UPDATE.

I don't think you are allowed to include DDL statements inside a Stored Function, so the issue of "forbidden to commit transaction within function body" does not arise for implicit commits.

Regardless of whether the vendor follows the standard, you will need to follow the vendor's rules. (I don't find them too onerous.)

Atomicity of stored functions

1 Answers1