Monday, December 14, 2009

Still experiencing "Transaction was deadlocked on lock resources with another process"?

I recently posted and article about the following error:
Exception: System.Data.SqlClient.SqlException: Transaction (Process ID XX) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the transaction.
See http://kiavashshakibaee.blogspot.com/2009_07_01_archive.html
I've recently found that in addition to the original solution, there are occasions when a deadlock cannot be avoided.

In my case, after running a trace log, I found the error to be reported on a sql "Select" statement. Your plugin is probably attempting to read a record while CRM or another plugin has it locked.

The solution to this issue is to enable "Read Committed Snapshot Isolation (RCSI)". Enabling RCSI causes SQL to maintain a snapshot of the database against which read operations are executed, writes and updates are still performed against the actual database, this means that write operations no longer block read operations. (see http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx for more)

To enable RSCI, ensure you have exclusive connection to the CRM Database and run the following SQL script:

ALTER DATABASE (CRM Organization)_MSCRM
SET
READ_COMMITTED_SNAPSHOT ON
go
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
go