How to solve Index/Key related Deadlock

Question

I have diagnosed a deadlock problem in my sql server using the deadlock graph in SQL Server 2008.

The problem has to do with my indexes. I have two queries: a long running report with alot of joins and subqueries that pulls data according to two different dates on a base table, and a quick update query, which updates those same dates on that base table. I have two indexes, and the report wants a shared KEY lock on both of them, while the update query wants an exclusive KEY lock on both of them, and somehow each query only manages to get one of the keys, so neither can proceed.

What can I do to fix this?

Here's all the details on my situation:

My base table looks like this:

CREATE TABLE job_tb{
    job_id int IDENTITY(1,1),
    createDate datetime NULL,
    upDate datetime NULL,
    dataField1 nchar(1),
    dataField2 nchar(2),
    --etc...
}

My indices look like this:

CREATE NONCLUSTERED INDEX idx_createDate ON job_tb(
  createDate DESC
)
INCLUDE(dataField1, dataField2)

CREATE NONCLUSTERED INDEX idx_upDate ON job_tb(
  upDate DESC
)
INCLUDE(dataField1, dataField2)

Lastly, my update looks like this:

BEGIN TRANSACTION;
    UPDATE job_tb
    SET
        dataField1 = @data
        upDate = @upDate
    WHERE
        job_id = @job_id
COMMIT TRANSACTION;

And the report is counting all kinds of statistics by date, so I won't include that here. I intentionally designed idx_createDate and idx_upDate to "cover" or include dataField1, since it's heavily used in that report.

I believe the report grabs a shared lock on one of the indexes, then hits a subquery and requests a lock on the second index. Meanwhile the update query wants an exclusive lock on both indices in order to update both the upDate and the included dataField1.

What do you guys think?

EDIT: Here's the XML deadlock graph, as requested:

<deadlock-list>  <deadlock>
<victim-list>
    <victimProcess id="processcf65288"/>
</victim-list>
<process-list>
    <process id="processcf65288" taskpriority="0" logused="0" waitresource="KEY: 6:72057597970874368 (eee1799e706c)" waittime="122" ownerId="421742704" transactionname="SELECT" lasttranstarted="2012-08-03T05:37:21.257" XDES="0x8611e8800" lockMode="S" schedulerid="50" kpid="8560" status="suspended" spid="70" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2012-08-03T05:37:21.257" lastbatchcompleted="2012-08-03T05:37:21.257" clientapp="Internet Information Services" hostname="xxx" hostpid="11964" loginname="xxx" isolationlevel="read committed (2)" xactid="421742704" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
            <frame procname="" line="28" stmtstart="1276" stmtend="4826" sqlhandle="0x03000600311ac36c65a31701a1a000000100000000000000">
            </frame>
            <frame procname="" line="1" sqlhandle="0x01000600f61bee3600932ae3090000000000000000000000">
            </frame>
        </executionStack>
        <inputbuf> exec MonthlyReport @id = 41
        </inputbuf>
    </process>
    <process id="processd2b6bc8" taskpriority="0" logused="1908" waitresource="KEY: 6:72057597970939904 (8e8117a49479)" waittime="2242" ownerId="421742551" transactionname="user_transaction" lasttranstarted="2012-08-03T05:37:20.447" XDES="0x7e84ad0a0" lockMode="X" schedulerid="63" kpid="12700" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-08-03T05:37:20.443" lastbatchcompleted="2012-08-03T05:37:20.443" clientapp="Internet Information Services" hostname="xxx" hostpid="11964" loginname="xxx" isolationlevel="read committed (2)" xactid="421742551" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
            <frame procname="" line="47" stmtstart="2342" stmtend="2640" sqlhandle="0x03000600e7dd9c717cbbb900ec9f00000100000000000000">
            </frame>
            <frame procname="" line="1" sqlhandle="0x01000600311d7a152032f9be040000000000000000000000">
            </frame>
        </executionStack>
        <inputbuf> exec UpdateJob @dataField1 = &apos;C&apos;, @upDate = &apos;8/3/2012 5:37:20 AM&apos;, @job_id = 1542687
        </inputbuf>
    </process>
</process-list>
<resource-list>
    <keylock hobtid="72057597970874368" dbid="6" objectname="" indexname="" id="lock612859900" mode="X" associatedObjectId="72057597970874368">
        <owner-list>
            <owner id="processd2b6bc8" mode="X"/>
        </owner-list>
        <waiter-list>
            <waiter id="processcf65288" mode="S" requestType="wait"/>
        </waiter-list>
    </keylock>
    <keylock hobtid="72057597970939904" dbid="6" objectname="" indexname="" id="lock612a15300" mode="S" associatedObjectId="72057597970939904">
        <owner-list>
            <owner id="processcf65288" mode="S"/>
        </owner-list>
        <waiter-list>
            <waiter id="processd2b6bc8" mode="X" requestType="wait"/>
        </waiter-list>
    </keylock>
</resource-list>  
</deadlock> /deadlock-list> 

Answer 1

Based on the Q/A discussion in comments and after analyzing the deadlock graph, this is the case where the report query isn't fully covered by the present two indexes. The report will start looking over non clustered indexes first. It doesn't find all the needed information. So it does a key lookup on primary table to get the remaining data. But updates work the exact opposite way. Updates will first lock the primary table, and update it's data and then go for all the indexes and update them. Hence deadlock.

One way to fix this is to cover the whole report query by index. But this has implications that updates will become slow.

Other solution is to break down the report query into two and use temporary table variable to collect the data from indexes and then do key lookup. Note report query should not be run under serializable transaction mode. Otherwise the transaction won't release the read locks which it just read.

Hope I'm clear. Let me know if you have any doubts.