I'd like to ensure that I understand correctly how the concepts of transaction chaining, transaction isolation level and atomicity relate to each other.
Here's my understanding:
- transaction chaining (chained versus unchained transactions) is orthogonal to the four transaction isolation levels defined by the SQL standard. I.e. all eight (4×2) combinations are possible.
- transaction chaining is simply the default setting for each database connection. It is not a constraint and it can easily be overridden. E.g. if transactions are chained by default then a number of statements will only be committed when a
COMMITis encountered. If that is not the desired behavior, simply intersperse more
COMMITstatements. If, on the other hand, transactions are unchained by default then each statement is committed automatically. If that is not the desired behavior simply wrap a block of statements inside
- unchained transactions is the same as auto-commit.
- atomicity of individual statements is always maintained regardless of transaction chaining or isolation level.
- atomicity of groups of statements depends on the transaction chaining mode and the
COMMITstatements used. It is not affected by the transaction isolation level.