Strange Deadlock issue

Many thanks for your reply.

The execution plan for Session-52 is:
Execution Tree
--
Assert(WHERE:(If ([Expr1007]<>'Job' AND [Expr1007]<>'CO') then 0 else If ((((([Expr1011]<>'Replace' AND [Expr1011]<>'Reject') AND [Expr1011]<>'Request') AND [Expr1011]<>'Plan') AND [Expr1011]<>'Cancel') AND [Expr1011]<>'Approve') then 1 else If (NOT([Pass1022]) AND ([Expr1021] IS NULL)) then 2 else NULL))
|--Nested Loops(Left Semi Join, WHERE:([@3] IS NULL), DEFINE:([Expr1021] = [PROBE VALUE]))
|--Clustered Index Update(OBJECT:([alexqiu].[dbo].[resources_allocate].[PK__resources_alloca__58B39691]), SET:([resources_allocate].[ToDate]=RaiseIfNull([Expr1008]), [resources_allocate].[ResId]=RaiseIfNull([@3]), [resources_allocate].[RequestUserId]=[@20], [resources_allocate].[RequestTS]=[Expr1012], [resources_allocate].[RepToDate]=[Expr1009], [resources_allocate].[RepManday]=[@6], [resources_allocate].[RepFromDate]=[Expr1005], [resources_allocate].[ObjId]=RaiseIfNull([@7]), [resources_allocate].[Manday]=RaiseIfNull([@5]), [resources_allocate].[ApprToDate]=[Expr1017], [resources_allocate].[ApprFromDate]=[Expr1018], [resources_allocate].[ApprAllocation]=[Expr1016], [resources_allocate].[FrozenToDate]=[Expr1014], [resources_allocate].[FrozenFromDate]=[Expr1015], [resources_allocate].[FrozenAllocation]=[Expr1013], [resources_allocate].[FromDate]=RaiseIfNull([Expr1004]), [resources_allocate].[Allocation]=RaiseIfNull([Expr1006]), [resources_allocate].[Description]=[Expr1010], [resources_allocate].[ObjType]=RaiseIfNull([Expr1007]), [resources_allocate].[Status]=RaiseIfNull([Expr1011])), DEFINE:([Expr1004]=Convert([@1]), [Expr1005]=Convert([@2]), [Expr1006]=Convert([@4]), [Expr1007]=Convert([@8]), [Expr1008]=Convert([@9]), [Expr1009]=Convert([@10]), [Expr1010]=Convert([@11]), [Expr1011]=Convert([@12]), [Expr1012]=Convert([@13]), [Expr1013]=Convert([@14]), [Expr1014]=Convert([@15]), [Expr1015]=Convert([@16]), [Expr1016]=Convert([@17]), [Expr1017]=Convert([@18]), [Expr1018]=Convert([@19])), WHERE:([resources_allocate].[AllocationId]=Convert([@21])))
|--Clustered Index Seek(OBJECT:([alexqiu].[dbo].[resources].[PK__resources__5BC50D66]), SEEK:([resources].[ResId]=[@3]) ORDERED FORWARD)

The other indexes in this table are:
CREATE INDEX resouces_allocate_idx1 ON resources_allocate(ResId, FromDate, ToDate);

CREATE INDEX resouces_allocate_idx2 ON resources_allocate(ObjType, ObjId);

I am going to redo the performance test and use DBCC on the deadlock page.
Thanks again.

Regards,
AlexQiu

Hi,
Maybe I get the answer.
The S lock is the result of lock escalation.
When many IS locks on a page, SQLServer escalates the lock to S lock to reduce the locks overhead. That's why I get S lock in a select statement, right?

So the deadlock is generated somehow like this process:
Two records, says: A, B
1. System put IS locks on A, B because of a select statement.
2. At the same time, A get an IX lock by the update statement. (IS, IX are compatible)
3. Then system decides to escalate the IS locks to S locks because of the overhead. And upgrade the IS lock on B to S lock.
4. Anohter update statement comes, try to gain an IX lock on B but failed. (IX and S are not compatible)
5. System tries to upgrade the IS lock on A to S lock but failed. (Waiting for the update statement to release the IX lock)

If it is true, how can I prevent this kind of deadlock?
Your suggestion is to use Rowlock hint to resolve it. But the situation is we are now using an ORMapping tools to deal with DB (Hiberxxxx), the rowlock implementation on sqlserver is not yet implemented, so I just want to see whether there is another work around.

Sorry for ocupy your time, many thanks again for your reply.

Regards,
AlexQiu

Thanks for your reply.

You are right, SQLServer choose Page S lock for my select statement.
I can't reproduce it in the Query window. (Only IS lock on page)

The "rowlock implementation on SQL Server is not yet implemented" means Hiberxxxx not yet implements the 'rowlock hint' on SQLServer thru its Hql & API.

So the solution becomes writing native sql like "Select ... with ROWLOCK where...". (It resolves the deadlock issue, I have tested. Thanks.)

But this makes our application db dependent, it seems not so good. I just want to see if there is any other solution.

Yes, there are multiple updates in the app. But I don't know about the nested loop join. (The cluster index update is the PK). The target table is a base table.

Here is the whole table definition. Hope it helps.
==========================================================
CREATE TABLE resources_allocate (
Allocation tinyint NOT NULL,
AllocationId id PRIMARY KEY,
Description string3,
FromDate ts,
FrozenAllocation tinyint,
FrozenFromDate ts NULL,
FrozenToDate ts NULL,
ApprAllocation tinyint,
ApprFromDate ts NULL,
ApprToDate ts NULL,
Manday int NOT NULL,
ObjId id,
ObjType type CHECK (ObjType IN ('CO', 'Job')),
RepFromDate ts NULL,
RepManday int,
RepToDate ts NULL,
RequestTS ts NULL,
RequestUserId id NULL,
ResId id REFERENCES resources ON DELETE CASCADE,
Status type CHECK (Status IN ('Approve','Cancel', 'Plan', 'Request', 'Reject', 'Replace')),
ToDate ts
);

CREATE INDEX resouces_allocate_idx1 ON resources_allocate(ResId, FromDate, ToDate);

CREATE INDEX resouces_allocate_idx2 ON resources_allocate(ObjType, ObjId);
============================================================

Thanks again.

Regards,
AlexQiu