How to Handle Lock Information in SQL Server 2008 R2

Nothing can prevent deadlocks in a RDBMS. However, you can greatly reduce the chance of the occurring by following a few simple rules:

1. Make sure your queries are tuned well, using appropriate indexes.
2. If you have to take concurrent locks on multiple tables, all your statements and procedures should access those tables in the same order.
3. Keep your transactions as short as possible.

That will make it a lot less likely for you to encounter deadlocks. However, you cannot completely avoid them, so your code should be resilient and just automatically wait a random time interval (a few milliseconds) and then try again. Only if after three or so retries you still cant get the statement executed log the error.

Now to your question, of how to reproduce the deadlock: There are several types of deadlocks. The simplest one is the deadlock on two resources:

1. connection A takes lock on resource 1
2. connection B takes lock on resource 2
3. connection A tries to take conflicting lock on resource 2 and has to wait
4. connection B tries to take conflicting lock on resource 1 and has to wait

Now both connections wait for each other causing a deadlock. This type of deadlock can be fairly easily reproduced by stepping through the code one statement at a time on two connections. Other deadlocks are not always as easy to reproduce because e.g. they might only occur on memory pressure situations or similar.

The easiest way to get more information about deadlocks is, to set up a deadlock-graph trace. The deadlock graph contains a lot of information that can be helpful understanding the captured deadlock. As your system (hopefully) has deadlock only relatively infrequent, you might have to run the trace for a while. But if you are capturing only that one event, it won't use a lot of resources. Once you captured the deadlock graph look at it as XML. The graphical representation given by the profiler does reveal only a limited amount of information, but it is a good starting point.

Is there a way to prevent the deadlock while maintaining the same queries?

The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID lookup in this case):

As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in different orders. The SELECT query needs to access the index before the table due to the RID lookup, whereas the UPDATE query modifies the table first, then the index.

Eliminating the deadlock requires removing one of the deadlock ingredients. The following are the main options:

1. Avoid the RID lookup by making the nonclustered index covering. This is probably not practical in your case because the SELECT query returns 26 columns.
2. Avoid the RID lookup by creating a clustered index. This would involve creating a clustered index on the column Proposal. This is worth considering, though it appears this column is of type uniqueidentifier, which may or may not be a good choice for a clustered index, depending on broader issues.
3. Avoid taking shared locks when reading by enabling the READ_COMMITTED_SNAPSHOT or SNAPSHOT database options. This would require careful testing, especially with respect to any designed-in blocking behaviours. Trigger code would also require testing to ensure the logic performs correctly.
4. Avoid taking shared locks when reading by using the READ UNCOMMITTED isolation level for the SELECT query. All the usual caveats apply.
5. Avoid concurrent execution of the two queries in question by using an exclusive application lock (see sp_getapplock).
6. Use table lock hints to avoid concurrency. This is a bigger hammer than option 5, as it may affect other queries, not just the two identified in the question.

Can I somehow take an X-Lock on the index in the update transaction before the update to ensure the table and index access are in the same order

You can try this, by wrapping the update in an explicit transaction and performing a SELECT with an XLOCK hint on the nonclustered index value before the update. This relies on you knowing for certain what the current value in the nonclustered index is, getting the execution plan right, and correctly anticipating all the side-effects of taking this extra lock. It also relies on the locking engine not being smart enough to avoid taking the lock if it is judged to be redundant.

In short, while this is feasible in principle, I do not recommend it. It is too easy to miss something, or to outsmart oneself in creative ways. If you really must avoid these deadlocks (rather than just detecting them and retrying), I would encourage you to look instead at the more general solutions listed above.