Question:
What does Transaction (Process ID nn) was deadlocked on lock resources with another process and has been chosen as the deadlock victim… mean?
Answer:
Most simply, it means that the user‘s work was not saved. If the user saves again, it should be fine.
Underneath the scenes, it means that two users were doing some work and that work required the same internal resources, but in different order. For example, Joe and Beth both need X and Y. Joe has X and Beth has Y—so now neither can complete their work. Much like the north- and south- going Zax in Dr. Suess‘s famous story, neither can finish their trip unless one “steps aside” and lets the other continue. Having read the story and learned the lesson, SQL makes the decision about who will step aside. Then to make it sound impressive, SQL calls that person the “deadlock victim.”
If the message is very, very intermittent—once a month or less—then the issue may be related to other activity on the SQL server system, such as backups, disk defragmentation, etc. Schedule these activities for off periods.
Lack of system resources do contribute to this problem. Slow disks or a large disk queue, for example, contribute to long transaction lifetime and increase the probability of a deadlock scenario.
If there is a specific use case that always causes this symptom, contact Spitfire support immediately. sfPMS uses “snapshot isolation,” so deadlocks during normal system usage should be exceptionally rare.
See below for troubleshooting information.
Additional Comments:
Step 1: Enable SQL Deadlock trace – DBCC TraceOn(1222,-1). You can query the current state of tracing using DBCC TraceStatus(1222). If tracing and troubleshooting is an ongoing effort, the command only needs to be repeated once per SQL server startup. Consider adding -T1222 to service startup parameters (no space before dash, capital T);Spitfire uses standard procedures to analyze the deadlock trace – see
- Detecting and Ending Deadlocks at http://msdn.microsoft.com/en-us/library/ms178104.aspx
- (Retired) http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx for some basics.
- Trace Flag Doc at http://msdn.microsoft.com/en-us/library/ms188396.aspx