3

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:

  1. 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.
  2. 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 COMMIT is encountered. If that is not the desired behavior, simply intersperse more COMMIT statements. 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 BEGIN / COMMIT.
  3. unchained transactions is the same as auto-commit.
  4. atomicity of individual statements is always maintained regardless of transaction chaining or isolation level.
  5. atomicity of groups of statements depends on the transaction chaining mode and the BEGIN / COMMIT statements used. It is not affected by the transaction isolation level.
  • is this Sybase or SQL Server? or generic? – Max Vernon Jan 25 '17 at 19:55
  • @MaxVernon I was trying to ask a generic question if possible; if a vendor-independent answer is not possible then Sybase ASE is my immediate use case. – Marcus Junius Brutus Jan 25 '17 at 20:00
  • 1
    Sounds to me you have it correctly defined for Sybase ASE (and incidentally SQL Server) in your question. Perhaps someone smarter will correct me. – Max Vernon Jan 25 '17 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.