Searching potential deadlocks with Client Monitor

dolec

Hi,

I am trying to find out the potential deadlocks using Client Monitor and helper objects from Tools CD and I don't understand it much.

I have already defined the Locking Order Rules:
357 Document Dimension, 37 Sales Line
5107 Sales Header Archive, 37 Sales Line

and also i have found some locking rule violations /in the Transaction (Locking Rules) form/

Then I looked at the form Transaction Locking Order
(from the Transaction (Locking Rules) form, chose some line where the field Locking Rule Violations is checked, click Transaction->Locking order)

The Locking order was realy different from my defined rules, BUT if I look at the form Client Monitor (Transaction->Client Monitor (Locking Operations Only)), there are operations which lock tables in the RIGHT order (

Why the Transaction Locking order form shows this order when the client monitor has recorded locking operations in some different order?

thanx a lot for your suggestions

--Transaction Locking Order--
Transaction No.	Table ID	Table Name
1	37	Sales Line
1	36	Sales Header
1	89043	Way Bill Line
1	357	Document Dimension
1	309	No. Series Line


--Client Monitor--
Entry No.	Transaction No.	Table Name	Locking	Function Name	Search Method
1008	1	Document Dimension	Yes	LOCKTABLE
1009	1	Sales Line	                Yes	LOCKTABLE
1010	1	Sales Line	                Yes	FIND/NEXT	-
.
.
.

kine

You are using MS SQL?

If yes, locking is made by Insert, modify or get and FIND command, not through LOCKTABLE... !

And if you have problems with deadlocks on MS SQL, try optimize indexes and after that to try check the code...

dolec

Hi Kine,

maybe I know you personaly but I am not sure. Haven't you studied Technical University of Brno - FIT? I think I heard your name at some time.

OK, so I'll try to explain you our situation. We have a customer with about 55 licensed sessions and he uses MS SQL option. Sometimes more than 2 users (i tried 5 users) decide to post something (e.g. Sales Order) and the process finishes with the error message 'Your activity was locked (deadlocked) by some other user who had changed the Document Dimension table' (or something like this, i have translated it from czech). On some clients Navision stops and waits for unlock.

Now I am trying to localize the points that could be source of that problems. So I read the Performance Troubleshooting Guide and I am learning how to use Client Monitor + helper objects from Tools CD.

Accoording to your advice, think i followed all the locking operations (including find/next, insert, modify, delete,...) maybe I had some wrong data, doesn' matter.

But maybe I could ask you, how did you solved the Document Dimension vs. Sales Line deadlock? I noticed you wrote some contribution to this forum (3.7 Deadlock). In the t37 table there is the code
.
.
DocDim.LOCKTABLE;
SalesLine.LOCKTABLE;
.
.
and I think the locking order is allways the same

so why deadlocks occur?

kine

Yes, I am from BUT (VUT) - but I graduated before the FIT (2001) :-) I was only on FEI...

LOCKTABLE say nothing about when the lock will be created... it only change isolation level...

if you have

DocDim.LOCKTABLE;
SalesLine.LOCKTABLE; 

but in the code

  ...
  SalesLine.MODIFY;
  ...
  DocDim.MODIFY;¨
  ...

The locking order will be SalesLine and than DocDim...

Many of the locks and waits are because indexes and SIFT tables must be updated.

I had some meeting with the author of the Troubleshooting Guide and maker of the extended client monitor objects and this is short abstract:

Problem area (by priority):

1) HW:
a) RAM (MS SQL need memory - for example 20GB DB, 70 users - 8GB recomanded) - do not forget apropriate switches for OS and MS SQL to use more than 2GB RAM and check maximum usable memory for the combination of MS SQL version and OS
b) HDD - do not use RAID 5, RAID 10 is OK. Minimum is 3 HDD (one for TempDB, OS, MS SQL OS, swap, second for DB and third for Transaction logs)
c) CPU
d) Network (possible slow connection can slow all others)
2) Re-design SIFT using in Navision (disable MaintainSIFTIndex for work tables - for example SalesLine, on other big tables change the SIFT levels to maintain to have only needed levels and only once for combination of filters you are using)

3) Re-design keys in Navision (disable MaintainSQLIndex for all secondary keys and create new keys starting with fields which are used for filtering and have high selectivity (are not booleans, options but are for example document no., customer no. etc.)

4) Disable Automatic statistics on database and create own maintanance job for that

5) Disable Auto grow of DB files and set up some tool (script or something) to send warning if there is low of free memory to be able resize ALL db files to same size (auto grow is changing size only for one file)

6) etc.

After that, try to check wrong designed code which are calling SELECT statement in the loop instead of FETCH (wrong cache using).

Alex_Chow

Wouldn't you put redesigning of SIFT and indexes before inspecting the hardware?

DenSter

You always inspect hardware first, to make sure there are no obvious bottlenecks. You can redesign indexes and eliminate SIFT as much as you want, but put it on bad hardware and it will perform very badly.

jlandeen

I have one solution option and one question here.

Solution Option:
I've run into this document dimension problem when running with SQL server and a solution that I've seen implemented is simply to comment out the DocDim.LOCKTABLE call in CDU80. As your solution runs on SQL server - the SQL database should be managing all of the locks and in the event of a collision on updates it should throw an error and rollback the transaction. In the case of a collision you are no worse off then now - but if there isn't a collision on updating document dimension then every thing flows through fine.

I definately agree that you should take a look at the Hardware & Environment configuration as suggested here...but this solution is a quick workaround that can help make things more functional for the users while you check out the hardware.

Question (for Kine):
You note that Raid5 is to be avoided and Raid10 is ok. Would you consider Raid10 just ok or the recommended option? I know Raid5 performs poorly with a transactional system and normally I've always recommended clients go with Raid10...are there any alternatives that you would recommend?

kine

We are using nothing than Raid 1 (2 discs) or 10 (4 and more discs). I never used any other RAID version.