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.
Oh, and could you also elaborate on the "Filthy" VALIDATE command :-) ?
Great entry ! Thanks a lot for sharing.
If I understand you correctly, you're saying that the LOCKTABLE command should never be used on INSERTs?
That sounds reasonable.. :-)
Can you explain a bit about the GET after the INSERT?
Yes, I know.
This is realy an extremely high level blog entry but I wanted to do it anyway because of the continuous roumors and myths that are out there.
Unfortunately not everyone reads this stuff.
Well, that keeps us busy. ;)
LOL. that's the kind of comments you want :-).
Nice blog, Mark! Really informative.
Yes, NAV2009 classic client has colors.
how do you get the colors in the C/AL editor? is that a new feature in NAV 2009?