> Deadlock scans: 289, Deadlocks: 1, Scan interval: 10 Is probably the culprit, 289 deadlock scans, one actual deadlock found....
"Deadlock scans. The number of times that the lock manager walked a chain of locks and owners looking for deadlocks. The lock manager initiates a deadlock scan when a process has been waiting 10 seconds for a lock." "Deadlocks. The number of actual deadlocks found, A deadlock occurs when Process A, wants a lock on Resource 1 which is held in an incompatible mode by Process B and Process B wants a lock on some Resource 2 which is held in an incompatible mode by Process A. "Each owner stands around glowering at the other and neither will do anything to improve the situation, so the lock manager returns a fatal error to one or the other. Deadlocks can also occur with a single resource if both owners start with read locks and request conversions to write locks. However, deadlocks always involve two owners (or two separate transactions) Errors that are returned as "lock conflict" from "no wait" lock requests will not be recorded in the lock table as deadlocks because only one owner is waiting. Errors returned as "deadlock" with a secondary message specifying "update conflicts with concurrent update" are not actual deadlocks either. What has happened in those cases is that one owner has modified (or erased) a record and moved on. Another concurrent owner has attempted to modify (or erase) the same record, noticed that the most recent version is one he can't see waited to find out how the other transaction ends up, and found to his disappointment, that the other transaction succeeded. In that case, our patient transaction can't modify the record because it can't find out what its late contemporary actually did." "Scan interval. The lock manager waits some period of time after a request starts waiting before it starts a deadlock scan. The default interval is 10 seconds, which may be long considering the change in CPU performance since 1983. Deadlock scans should not be done whenever there's a wait because waiting is normal and scans are not free." A deadlock scan would cause your server to hiccup while the lock manager tries to find if there is a real deadlock within the lock manager. You might want to investigate why deadlock scans are being instigated.. With thanks to Ann Harrison :-) Regards Paul Beach
