Now, there has always been something that bothered me with this application and that is the mismatch in data types that seems to occur. Well, there is a configuration setting in ColdFusion, String Format, that makes all strings to be treated as NVARCHAR. Well, what happens if your primary key is varchar(36) and you send a nvarchar value? One, you don't get to use the index on the table. Now this doesn't mean you get a table scan either. In our case, SQL Server chose to scan a NC index (idx_DateInitialPublished for example) on the OperationsSIGACTDraftReport table. It is a small index with only a datetime value and the ReportKey. Once it found the ReportKey it needed it would then do a bookmark lookup to the clustered index.

Well, multiply it to most of our tables.

Today, the engineer changed this setting so that we were sending varchar values. The automated test suite has been running for over an hour and no deadlocks. Java is running happily. And we have very few scans on the indexes on the two tables we were getting deadlocks.

Moral: Use proper data types. And if anyone else runs into bizarre deadlock issues like this, check your data types from the application to the database.

FYI, when this application was first being developed in Iraq, they had no real database developer.