NASSQL.exe deadlock problem

jwilder

We have switched from C/SIDE to SQL 2005 a week ago and most things are going well. One problem we are running into is that one of our NAS's keeps on getting deadlock errors and eventually the NAS can't restart (fatal error)

We have 2 NAS process's: one post's invoices and the other charges credit cards. The one that charges credit cards is the one that gets the deadlock error. It typically gets about 20 deadlock errors in a 1/2 hour time period while the other nas is posting invoices. I have worked through the locking sequence and I am quite confident there can be no deadlock.

I also ran the same credit card process from a navision client and received no deadlock errors.

Has anyone experienced deadlocking with nassql.exe that shuts it down?

Waldo

Very hard to solve deadlock problems over a forum.

I don't think the other NAS process (posting invoices) is involved in your problem.

You should check which tables it updates during the credit card charging. Then search for other processes (probably manual processes) that are updating these tables as well.

Are there also users getting deadlocks?

Marije_Brummel

You can watch the deadlocks in the profiler. Only issue is that the profiler gives funny HOBT id's instead of SQL or NAV table numbers.

I've asked Hynek to make me something to see which Navision processes were involved but that is part of the SQL Peform tools now.

You can try to run the processes on the 2 clients for 1 day and see what happens.

Deadlocks usualy happen because of slow posting routines. You can try to speed up your posting routines by archiving data or cleaning up unnessesairy indexes and SIFT levels.

Another very simple solution is to serialise the transactions. This way you simulate the Native behaviour. It is very simple. You create a new table with a Code field. If the first NAS starts to post it writes 'NASA' in the table. If the transaction is finished it clears the field. Now if the Secons NAS posts it writes 'NASB' in the field but only if it is empty, otherwise it has to wait for NASA to finish. On the other hand. If both nasses cannot work simultanously you might be able to squeeze both routines in 1 NAS.

Good luck.

colin_leversuch-roberts

the easiset way to capture deadlock information is to enable trace flags, the deadlock info is written to the sql eror log.
the usual trace flags are 1222 and 1204 , ( 1222 is a sql 2005 flag ).
They were convinced NAS was deadlocked where I am, but we found this was not the case - but that's another story.
you can enable a trace flag using dbcc traceon
e.g. dbcc traceon(1222,1204,-1)

Wim_Mulder

Hey,

as you have switched from C/SIDE to SQL 2005, you have to look at the way you are locking tables.
The order in which the SQL-Server locks a table is different to C/Side
Using locktable with SQL-Server means that the table will be locked later the with C/Side. The table ist locked the first time the table is used (FIND)ot at the time of LOCKTABLE()
You will find plenty of information on the TOOLS CD 4.0 in the documention: Implementation\Performance Troubleshooting Guide\w1w1PerfTGuide.pdf
Chapter: 3.4 Locking in Navision - A comparison of Navision Database Server and SQL Server O:)

Marije_Brummel

LOCKTABLE does not lock the table on SQL. It only ads UPDLOCK to the SELECT statement.

The only way to lock a table in SQL is via LOCKESCALATION but this has nothing to do with C/AL

jlandeen

I have recently worked through some rather serious deadlock and performance issues and I completely agree with Mark - you should make sure to check for any slow posting routines. Simply adding keys or modifying SIFT levels to optimized slow searches can make a huge difference.

Also make sure that if you have any processes that lock tables that commits are called at a reasonable point so that any locked records are commited and UNLOCKED for other processes.