Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

If with(updlock) is used inside a nested transaction will the lock be released at the next or the final commit?

asked 17 Mar '15, 04:31

Martin's gravatar image

accept rate: 14%

AFAIK, SQL Anywhere does not really support nested transactions. Even when using BEGIN TRANSACTION/COMMIT TRANSACTION for T-SQL compatibility, this should behave like "nested savepoints". Given that, I would think there is only one real "commit", and until that, the lock will be hold.

Breck has written en detail on that in his great book and has cited it here, too (and I'm free to take a quote from that, too:):

The term "nested transaction" is sometimes used when @@TRANCOUNT rises to 2 or higher. That term is misleading, however, because only the outermost transaction has any meaning as far as database changes and locks are concerned. When @@TRANCOUNT rises to 2 or higher, a COMMIT statement does absolutely nothing except lower the @@TRANCOUNT value. A nested transaction implies that changes made in an inner transaction may be made permanent while changes made in the outer transaction are rolled back, and that simply is not possible in SQL Anywhere; there is no such thing as a nested transaction.

permanent link

answered 17 Mar '15, 05:40

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 17 Mar '15, 04:31

question was seen: 2,852 times

last updated: 17 Mar '15, 05:41