Types of Deadlock
exclusive lock(X), shared lock(S), update lock (U), intent lock (I), schema lock (SCH) and bulk update lock (BU)
select * from sys.dm_tran_locks
The first time a user sees the following message, the result of an unhandled deadlock error in SQL Server, it can come as quite a shock.
Msg 1205, Level 13, State 56, Line 10
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Two or more sessions were waiting to acquire a lock on a resource already locked by another session in the same locking chain. The result is a 'circular chain' where no session can complete, and so SQL Server is forced to intervene to clear up the mess. It automatically chooses one of the sessions as the 'victim', kills it and rolls back its transaction. It means that the other sessions may proceed, but that is little comfort to the hapless victim, and if that victim happens to be a session running a critical business process, then it may soon be of little comfort to the DBA.
No comments:
Post a Comment