Even if two processes compete on only one resource, they still can embrace in a deadlock. The following scripts reproduce such a scenario. In one tab, run this:
CREATE TABLE dbo.Test ( i INT ) ;
GO
INSERT INTO dbo.Test
( i )
VALUES ( 1 ) ;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
BEGIN TRAN
SELECT i
FROM dbo.Test ;
--UPDATE dbo.Test SET i=2 ;
After this script has completed, we have an outstanding transaction holding a shared lock. In another tab, let us have that another connection have a shared lock on the same resource:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
BEGIN TRAN
SELECT i
FROM dbo.Test ;
--UPDATE dbo.Test SET i=2 ;
This script completes and renders a result set, just like the first script did. Now let us highlight and execute the commented update commands in both tabs. To perform an update, each connection needs an exclusive lock. Neither connection can acquire that exclusive lock, because the other one is holding a shared lock. Although both connections are competing on only one resource, they have embraced in a conversion deadlock:
Msg 1205, Level 13, State 56, Line 1
Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.