Tip #7 – SQL Range Locks

This Blog Article was brought forward from my old blog

UPDATE 2014 : This no longer applies to the latest versions of Dynamics NAV (2013 and up)

In my previous tip (http://dynamicsuser.net/blogs/mark_brummel/archive/2009/06/13/tip-6-find-locked-records.aspx) I showed you how to find which exact records are locked and ‘proved’ row locking with the ‘LOCKTABLE’ command.

But even then you can eperience another fact. Sometimes SQL ‘locks’ records that are not yet created. This can be very irritating, especialy when for example several users are creating sales orders in batches.

What causes this to happen?

The answer can be found in the serveral locking types SQL server knows. The easiest one is the record lock. This lock is what you get when you ‘GET’ a single record when fitering on the unique value or ‘primairy key’ in Navision language.

To check this we will write the following code

.

(Don’t you love the colors )

When we run the codeunit, a Sales Line is inserted. Please notice you need the .GET to actualy insert the records because of the buffered inserts introduced in SP1 of 5.0

When we open a second client on the same SQL Server database and open the Sales line table we see the record

This is because SQL Server does not know the Version Principle that the Classic or ‘Native’ database knew. We can actualy see a record that is not yet committed into the database. COOL.

Now let’s check the locks in SQL Server. We do this using the sp_lock command.

I will not go into details about lock types but you can read some here. http://msdn.microsoft.com/en-us/library/ms191272.aspx

All that is interesting now is that we can insert a neighbouring record using a second client.

To do this we copy the codeunit and change the numbers.

After executing we see this in a ‘Third’ client.

And this in the SQL Management Studio

So lesson learned: We can create two neigbouring records in the sales line with two transactions from two clients. No blocking, no deadlocks, nothing.

Perfect. So how come I have (dead)(b)locks in my database.

Let’s release both locks by hitting Ja. (Dutch for Yes )

And write a new codeunit.

This reads all saleslines from the ‘Mark’ document without a cursor.

Now when we issue sp_lock we get this

Note that we get ‘RangeS-U’ types of locking.

Now let us try to create line number 30000.

When running this codeunit we get this error

THIS IS WHY YOU GET BLOCKS AND DEADLOCKS.

Navision is full of this kind of statements.

So how do I avoid this.

First lesson is to avoid the validate statement when populating records. This is a very nasty habbit of a lot of developers. Have a look at standard NAV code. How many validates are there in Codeunit 80? And 12?

Another trick can be to read into a temp table first and ‘get’ the records one by one.

Good luck with this one.

Send me an email when you want the objects.