SQL Transactional Locking

Another blog entry about locking in SQL Server. I really hope this is not getting boring.

I just like to get some myths and roumors out of the way, that's all...

Hopefully my older blog entries clarified the LOCKTABLE command resulting in SQL Server locking either records or ranges of records.

For this post to be clear I would like to start writing some code.

This codeunit contains two functions. Bot functions have their own local variable for the Customer table.

Please note that the first function add's a LOCKTABLE before the GET statement. Meaning that the data should be locked for other processes. Function 2 does not contain an explicit locktable.

When we run the first part we see this SQL Statement

SELECT  * FROM "CRONUS International Ltd_$Customer" WITH

  (UPDLOCK, REPEATABLEREAD) WHERE "No_" = '10000'

This means that record 10000 of the customer table will be locked.

SELECT  * FROM "CRONUS International Ltd_$Customer" WITH

  (UPDLOCK, REPEATABLEREAD) WHERE "No_" = '20000'

Now this is a suprise isn't it? We have not explicitly placed a locktable command on the second variable but it seems like NAV is doing it anyway!

Is this a bug? No, it is something we inherrited from the Native database and is bound to the way NAV works.

Maybe if one day the Native database is dropped this will be changed but until that day, it is just something we have to live with.

Question : Does this also happen in diffent objects? Answer: Yes, this is a transaction wide 'setting' that can only be 'avoided' by a commit.

Enjoy...

Comment List
  • Yes, the application must be completely reviewed but maybe they can come up with a LOCKTABLE(TRUE/FALSE) thing and do it in phases right?

  • I am not sure it will disappear immediately when the Native database is dropped because this means that the whole application has to be tested/rewritten again. This because the application was written with the native database logic.

Related
Recommended