Amit,

Here is my freshly updated code to show what is going on with this deadlock. (For any keeping track, this is now my most recent deadlock shredding code.)

-- see http://msdn.microsoft.com/en-us/library/ms188246.aspx

-- (MS BOL Analyzing Deadlocks with SQL Server Profiler)

-- see http://msdn.microsoft.com/en-us/library/ms175519.aspx

-- (MS BOL Lock Modes)

-- Shred XML Deadlock Graphs, showing in tabular format as much information as possible.

-- Insert the XML Deadlock Graph into the @deadlock table.

-- Author: Wayne Sheffield

-- Version: 2

-- Modification History:

-- 10/10/2010 - Added individual items in the Execution Stack node.

-- - Converted from using an XML variable to a table variable with an XML variable

-- - to allow seeing multiple deadlocks simultaneously.

declare @deadlock table (DeadlockID INT IDENTITY PRIMARY KEY CLUSTERED, DeadlockGraph XML);

INSERT INTO @deadlock VALUES ('');

-- insert the deadlock XML in the above line! Duplicate as necessary for additional graphs.

WITH CTE AS

(

SELECT DeadlockID,

DeadlockGraph

FROM @deadlock

)

, Process AS

(

-- get the data from the process node

SELECT CTE.DeadlockID,

--[DeadlockTime]=

CTE.[DeadlockGraph],

[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = Deadlock.Process.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,

[LockMode] = Deadlock.Process.value('@lockMode', 'varchar(3)'),

[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),

[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),

[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),

[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),

[BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime'),

[BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime'),

[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)'),

[ProcessID] = Deadlock.Process.value('@id','varchar(50)'),

[SPID] = Deadlock.Process.value('@spid','int'), -- server process id

[SBID] = Deadlock.Process.value('@sbid','int'), -- server batch id

[ECID] = Deadlock.Process.value('@ecid','int'), -- thread id of given SPID (0 is always the parent)

[IsolationLevel] = Deadlock.Process.value('@isolationlevel','varchar(200)'),

[WaitResource] = Deadlock.Process.value('@waitresource','varchar(200)'),

[LogUsed] = Deadlock.Process.value('@logused','int')

FROM CTE

CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)

)

, ExecutionStack AS

(

-- get the data from the executionStack node

SELECT CTE.DeadlockID,

ProcessID = Execution.Stack.value('../../@id','varchar(50)'),

Code = Execution.Stack.value('.', 'varchar(1000)'),

ProcName = Execution.Stack.value('@procname', 'sysname'),

Line = Execution.Stack.value('@line','int'),

RN = row_number() OVER (PARTITION BY CTE.DeadlockID, Execution.Stack.value('../../@id','varchar(50)') ORDER BY (SELECT 1))

FROM CTE

CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/process-list/process/executionStack/frame') as Execution(Stack)

)

--SELECT * FROM ExecutionStack

, PageLock AS

(

-- get the data from the pagelock node

SELECT DeadlockID,

ObjectName = PageLock.Process.value('../../@objectname', 'varchar(200)'),

ProcessID = PageLock.Process.value('@id', 'varchar(200)'),

LockType = 'Page',

LockMode = PageLock.Process.value('../../@mode','varchar(3)')

FROM CTE

CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/pagelock/owner-list/owner') AS PageLock(Process)

)

,KeyLock AS

(

-- get the data from the keylock node

SELECT DeadlockID,

ObjectName = KeyLock.Process.value('../../@objectname', 'varchar(200)') + '.' +

KeyLock.Process.value('../../@indexname', 'varchar(200)'), -- get the index name also

ProcessID = KeyLock.Process.value('@id', 'varchar(200)'),

LockType = 'Key',

LockMode = KeyLock.Process.value('../../@mode','varchar(3)')

FROM CTE

CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/keylock/owner-list/owner') AS KeyLock(Process)

)

, RidLock AS

(

-- get the data from the ridlock node

SELECT DeadlockID,

ObjectName = RIDLock.Process.value('../../@objectname', 'varchar(200)'),

ProcessID = RIDLock.Process.value('@id', 'varchar(200)'),

LockType = 'RID',

LockMode = RIDLock.Process.value('../../@mode','varchar(3)')

FROM CTE

CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/ridlock/owner-list/owner') AS RIDLock(Process)

)

, ObjectLock AS

(

-- get the data from the objectlock node

SELECT DeadlockID,

ObjectName = ObjectLock.Process.value('../../@objectname', 'varchar(200)'),

ProcessID = ObjectLock.Process.value('@id', 'varchar(200)'),

LockType = 'Object',

LockMode = ObjectLock.Process.value('../../@mode','varchar(3)')

FROM CTE

CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/objectlock/owner-list/owner') AS ObjectLock(Process)

)

-- combine all the data together, and display in

SELECT Process.[DeadlockID],

Process.[SPID],

Process.[SBID],

Process.[ECID],

Process.[DeadlockGraph], -- include the graph - can click and open in separate window

Process.[ProcessID],

Process.[Victim],

[LockedType] = coalesce(PageLock.LockType, KeyLock.LockType, RIDLock.LockType, ObjectLock.LockType),

[LockMode] = coalesce(PageLock.LockMode, KeyLock.LockMode, RIDLock.LockMode, ObjectLock.LockMode),

[LockedObject] = coalesce(PageLock.ObjectName, KeyLock.ObjectName, RIDLock.ObjectName, ObjectLock.ObjectName),

[Procedure #] = es.RN,

es.[ProcName],

es.[Line],

es.[Code],

Process.[ClientApp],

Process.[HostName],

Process.[LoginName],

Process.[TransactionTime],

Process.BatchStarted,

Process.BatchCompleted,

Process.[InputBuffer],

Process.[IsolationLevel],

Process.WaitResource,

Process.LogUsed

FROM Process

JOIN ExecutionStack es

ON es.ProcessID = Process.ProcessID

AND es.DeadlockID = Process.DeadlockID

LEFT JOIN PageLock

ON PageLock.ProcessID = Process.ProcessID

AND PageLock.DeadlockID = Process.DeadlockID

LEFT JOIN KeyLock

ON KeyLock.ProcessID = Process.ProcessID

AND KeyLock.DeadlockID = Process.DeadlockID

LEFT JOIN RIDLock

ON RIDLock.ProcessID = Process.ProcessID

AND RIDLock.DeadlockID = Process.DeadlockID

LEFT JOIN ObjectLock

ON ObjectLock.ProcessID = Process.ProcessID

AND ObjectLock.DeadlockID = Process.DeadlockID

ORDER BY Process.DeadlockID,

Process.victim DESC, -- show the victim first

Process.ProcessID,

Process.ECID, -- show in thread order by SPID

es.RN; -- execution stack order

In running this against the two deadlocks, I first notice that they are related. When the first deadlock was forcibly ended (SQL choose a victim), the "winning" process was then immediately involved in a deadlock with a third process.

The process that was "winner" in both deadlocks was inserting a record into the TsiCbs.dbo.tsi_t_objects table. The first deadlock was when inserting the row into the table, the second deadlock was when updating the PK on that table (presumably the same record). It also appears that there is an insert trigger on the table - hence why there are 4 subsequent statements in the execution stack after the update for this process.

The "victim" of the deadlocks were each running a select statement. They both appear to be from a view (I'm assuming that the "v_" in the following object names indicates a view:

1. tsi_v_secured (which is also being joined to a function: tsi_f_get_object_permissions)

2. x_v_users

Since neither of these views indicate the object that is the source of the deadlock (TsiCbs.dbo.tsi_t_objects), it looks like they are involved in a JOIN condition to this table.

So, IMO, the things that you need to look at are:

1. The views - look at the actual execution plan (NOT the ESTIMATED plan), and ensure that they are as optimized as they can be (for the queries that I see, there should be NO scan operators).

2. The function - again, ensure that it is as optimized as it can be. Note that joining to a function may result in the overall query now being reduced to scans, and can be even worse than using a cursor.

3. The trigger - again, ensure that it is optimized.

As Craig as stated, I see nothing in the procedure that you are running that is causing the issue. So, IMO, it will be at least one of the above issues.