Transaction (Process ID) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim

Question

I have a java program that updates a table in MS SQL. This table is also accessed by web users through a website created in ColdFusion

Recently I have been getting this error when the line:

sql_stmt.executeUpdate("update random_selection "
    + "set forecasted = 1 where "
    + " randnum = " + ora_rs.getString("RANDNUM")
    + " and quarter = " + quarter
    + " and ozip3 = " + ora_rs.getString("OZIP3"));

The CF query that is erroring is :

<cfquery name="submit_forecast" datasource="ttmsdropper" username="#request.db_username#" password="#request.db_password#">
    INSERT INTO forecast_entry
    VALUES (<cfqueryparam value="#currentRecord[8]#">)
</cfquery>

What is causing this error and how can I fix it?

Answer 1

A deadlock occurs when 2 processes try to hit the same data at the same time - both with an equal claim on the data. It's most common when there is a lot of update/insert activity going on (as you describe). The DB system "chooses" one of the transactions to be the "winner".

In some cases deadlocks might be improved or mitigated by indexing but only when selects are involved - a good indexing strategy might improve select performance and make row locking more efficient. However, in the case where the deadlock is coming from an insert contending with an update, indexing will NOT help. Indeed aggressive indexing could degrade the situation since the indexes have to be updated along with the data inserts or updates.

How to solve it greatly depends on your system and what you are trying to do. You have to either minimize the insert/update locking or provide more or faster resources somehow. Bundling inserts together and batching them, more procs or RAM (sometimes - not always), clustering, splitting tables and data, fine tuning parallelism - these could all be viable options. And there's no hard and fast rule.

Answer 2

If there are never update to the table ( only inserts ) then you may want to try changing selects to - with no lock or wrapping select in cftransation readuncommited.

As Mark said this is a DB error not ColdFusion and locks are occuing. If there are complicated selects and updates look add adding indexes to the clause columns.