Having Deadlocks with SQL Server 2k

DeSp

I use Navision 3.70A with SQL Server 2k working with warehouse management.
Two sessions loop in a deadlock frequently until I shut them down manually.
LOCKTABLE(True,False) is used in code

What is the problem?

Thanks in advance

Marije_Brummel

I don't know about this issue, but solving deadlocks in Navision can be a time consuming thing.

Is it solved in 3.70B or 4.00? Have you tested it?

There is also a document abount deadlocks, do you have/want this document?

DeSp

Unfortunetly I don't have much time to test it in 3.70B or 4.0. I need to solve it fast in 3.70A.
And I don't have any documents about solving deadlocks.
Where can I get one?

Marije_Brummel

Send me a mail and I will reply it to you.

DeSp

I've sent it

Marije_Brummel

Me to.

I hope it helps you.

DenSter

Alright, deadlocks 101:
Suppose we have 2 tables, table A and table B. When you have multiple processes going around, like what happens in an ERP system like Navision, you sometimes need to lock tables to preserve the integrity of your transaction. This means that other processes have to wait until the first one is done. This is called "locking", and is often confused with deadlocking because they sometimes take really long.

Deadlocking is a special type of locking, and can mostly be solved by making changes to programming code. This happens when you have say 2 processes that both need to access both tables. Process 1 locks table A first and then table B. Between the time that it locks table A and the time that it locks table B, another process (process 2) has already locked table B.

Normally that would just mean that process 1 needs to wait until process 2 is done with table B. A deadlock occurs though when process 2 locks table A right after it locked table B. Table A, however, was just locked by process 1. Now you have two processes that have to wait until the other is done, but they will never release their locks, because the transaction can't be completed. When this happens, this kind of "crosslocking", you have a deadlock on your hands.

What you need to do is figure out which processes are accessing the same tables at the same time, and make sure that those processes lock those tables in the same order (i.e. make both processes lock table A first and then table

Marije_Brummel

Yes, this is what the document by Microsoft says also. Along with some tips and pointers how to solve/analyse the problem.

DeSp

Thanks a lot

"Tuning Navision for better performance" is the doc by Microsoft? Do you mean it in your last reply, Mark?

DenSter, and how can I figure this out?
I have to use SQL Profiler or Navision Session, Client monitors and Activity log?

Marije_Brummel

I think it is from Microsoft, reading the disclaimer,

I got it from another mibuso user (fl).

So I am just passing it trough...

DenSter

I would start by running the session monitor, to identify the clients that experience the deadlocks and to identify the processes that run. Then you analyze the processes themselves and debug where it errors out, to see if you can discover the problem.

Then if that doesn't solve it, you run the client monitor from those clients to see what else causes the deadlocks. It's not an exact science, it's common sense and following the path of the code. Wow that sounds like a Jedi mantra .

"follow the path of the code Luke" :-k

DeSp

I've tried to use Session Monitor (SQL Server).fob but it looks like it has an error in Table 150014 Session (SQL): "TableData 150014 doesn't exist".
How can I solve it?

DeSp

It was my mistake with Table 150014 and I've solved it.

But I have another question: is there any parameter for SQL Server 2k to set the time period which SQL Server waits before automatically resolving the deadlock by killing one of the locking processes? Is it possible to set this parameter manually?

DenSter

I don't have one running here so I can't check, but I think it's a timer on the form. Open the form in the designer and check the timer property and the OnTimer trigger.

Marije_Brummel

DenSter wrote:

I don't have one running here so I can't check, but I think it's a timer on the form. Open the form in the designer and check the timer property and the OnTimer trigger.

I think I am lost here :?

I am trying to follow this thread, just out of curiousity?

Could you explain to me please what the OnTimer trigger has to do with the deadlock message?

DenSter

DeSp is having deadlocking issues, and to investigate he has the session monitor objects in his database. The session monitor basically takes snapshots of the sessions and writes each session's activity into a table. Then he was asking how to manipulate the time interval of the session monitor, and I believe that this can be done by changing the timer setting on the session monitor form. OnTimer has nothing to do with deadlocking, but it's where the session monitor's code is as far as I remember.

As I said though, I don't have it running, so I don't know for sure, but that's where I would start looking.

Marije_Brummel

Ok, thank you, I get it know. :!:

I thought you were refering to the parameter to get the deadlock message earlier, so I got a bit lost. :?

I know deadlocks can be a real pain in the ***, so I am just curious to how it ends.

bbrown

Are we dealing with a true deadlock here, or just a process that is holding a lock for an extended period of time? Is the sysem displaying deadock messages?

Deadlock definition: A deadlock occurs when there is a cyclic dependency between two or more threads for some set of resources


Deadlocking is often confused with normal blocking. When one transaction has a lock on a resource and another transaction needs the same resource it must wait for the first resource to release the lock. SQL transactions do not timeout (unless LOCK_TIMEOUT is set). In this case, the second transaction is blocked, not deadlocked.

When a deadlock does occur, SQL will choose to terminate a transaction that will end the deadlock. SQL will typically choose the transaction that is least costly to rollback. The SET DEADLOCK_PRIORITY command is used in Transact-sql to assign transactions a higher or lower deadlock priority.

The query wait option can be set in the server configuration, but this only effects queries that are waiting for memory resources.

To help track down deadlock issues, turn on traceflag 1024 in SQL. This will place detailed information about the deadloacks in the SQL error log. This information may help you isolate the issue.

Hope this information is helpful. Keep us updated on your issue.

DeSp

Thanks for assistance, everyone

I've tracked the deadlocks with the Navision Client Monitor (Multi-User) tool from the Client monitor.fob. It shows that it is definitely an SQL Server deadlock with an error message. It occurs when I simultaneously run the same routine at mutiple Navision clients. It looks like I have three problem tables causing the deadlocks. As I run the same routine at each client, the locking order of these tables must be the same, but it causing the deadlock somehow. Maybe I should set the TransactionType explicitly via the CURRENTTRANSACTIONTYPE(TransactionType) function? Default value is UpdateNoLocks, it causes current transaction to run as SERIAZABLE and UPDLOCK from the point of LOCKTABLE statement or write operations took place. As I get it, SERIALIZABLE isolation level is the highest for SQL Server providing higher consistency but lower concurrency.

The LOCK_TIMEOUT and DEADLOCK_PRIORITY parameters can be set only for a current connection and I can't set them from C/AL code.

DeSp

I have not solved the problem yet. Any suggestions will be appreciated.

DeSp

So it's it again.

I run the same warehouse registration operation on two Navision 3.70 clients simultaneously. Sometimes it leads to a hang - both clients don't prîcess any messages and have white screens. In SQL Enterprise Manager -> Process Info window I see such an info:

client1: spid=56, status=sleeping, open transactions=0, command=awaiting command, wait time=0, wait type=not waiting, wait resource=..., blocked by=0, blocking=1

client2: spid=55, status=sleeping, open transactions=1, command=execute, wait time=0, wait type=miscellaneous, wait resource=..., blocked by 56, blocking=0

This info is regularly refreshed but it is the same all the time while the clients are hanging.

After an hour of hanging I kill the process 56 manually via the SQL EM "kill process" method. The client2 proceeds with transaction and finishes it succesfully. The client1 either can get an error message: "Internal error 1247 in unit 19..." (What does it mean?) and then terminates, or can get a server connection lost error message and then, after we reconnect to server, we can gather the log information via the Form 150020 Client monitor for both clients and 150024 Client monitor (Multi-User) (referred by the "Performance troubleshooting guide.pdf"). Log investigation leads us to an error in the "SQL Error" field: "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()) , occurred at the hang time displayed in the "Time" field. What does this error mean?

Also I run the SQL Profiler to track this issue. Profiler's trace investigation leads us to an "Lock:Deadlock Chain" event: "Mode=RangeS-U, IndexId=1, Table= Warehouse Activity Line". We also get a "Lock:Deadlock" and "Exception" event for process 56: "Error=1205, Severity=13, State=50" (process 56 was chosen as a deadlock victim).
So, at the hang time it is a deadlock and process 56 must be stopped, its transaction rollbacked, client1 get a deadlock error message, process 55 lock necessary recources and finish its transaction - so no one hangs. But it is not! The clients are still hanging!
How is it possible? If it is a deadlock then SQL Server must resolve it correctly! But it is not!

I guess, process 56's transaction is not rollbacked somehow and it still lock recources, so process 55 can't proceed. How is it possible?

Any help or suggestions on this issue willl be very much appreciated.
Thanks in advance.

ara3n

DeSp wrote:

It was my mistake with Table 150014 and I've solved it.

But I have another question: is there any parameter for SQL Server 2k to set the time period which SQL Server waits before automatically resolving the deadlock by killing one of the locking processes? Is it possible to set this parameter manually?

What was the problem with this error 150014 does not exists and how did you solve it?

johanna

ara3n wrote:

DeSp wrote:

It was my mistake with Table 150014 and I've solved it.

But I have another question: is there any parameter for SQL Server 2k to set the time period which SQL Server waits before automatically resolving the deadlock by killing one of the locking processes? Is it possible to set this parameter manually?

What was the problem with this error 150014 does not exists and how did you solve it?

I've experienced this problem too and I've solved it by execute "Session Monitor (SQL
Server).sql" file in SQL Server Management Studio as mentioned in documentation "Microsoft Navision SQL Resource Kit"..

But, then when I run form 150014, I got new warning "Session (SQL) Connection ID '51' already exists" and I don't know how to solve it..

FDickschat

In CU "Session Monitor Mgt. (SQL Srv)", Fu RefreshUserActivity do the following modification:

SELECTLATESTVERSION;
IF Session.FIND('-') THEN
  REPEAT
    SessionTmp := Session;
    //- fdi01
    //SessionTmp.INSERT;
    IF NOT SessionTmp.INSERT THEN
      SessionTmp.MODIFY;
    //+ fdi01
  UNTIL Session.NEXT = 0;

johanna

wooow... i have tried your solution, FDickschat and ... it works !! =D>
thank you very much for your help