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.
3
  • is this Sybase or SQL Server? or generic?
    – Hannah Vernon
    Jan 25, 2017 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. Jan 25, 2017 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.
    – Hannah Vernon
    Jan 25, 2017 at 20:02

0

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.