Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

Martin
9.0k130169257
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
40.2k361550822
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×59
×2

question asked: 17 Mar '15, 04:31

question was seen: 2,686 times

last updated: 17 Mar '15, 05:41