Java – a transaction (process ID) deadlocks when locked, communicates with another process to buffer resources and is selected as a deadlock victim

I have a java program for updating tables in MS SQL Web users can also access this table through the web site created in ColdFusion

When I encountered this error recently:

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 wrong CF query 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 the cause of this error? How to solve it?

Solution

A deadlock occurs when two processes attempt to hit the same data at the same time - both have the same declaration on the data This is the most common when a large number of update / insert activities are in progress (as you described) The database system "selects" one of the transactions to become a "winner"

In some cases, deadlocks can be improved or mitigated by indexing, but only when it comes to selection - a good indexing strategy may improve selection performance and make row locking more effective However, if the deadlock comes from an insert that competes with the update, the index will not help In fact, a positive index may reduce the situation because the index must be updated with data insertion or update

How to solve this depends largely on your system and what you want to do You must minimize insert / update locking or provide more or faster resources in some way Bundling inserts together and batching them, more processing or RAM (sometimes – not always), clustering, splitting tables and data, fine-tuning parallelism – are all viable options And there are no hard and fast rules