Transaction (process identifier) ​​was blocked when locking | communication buffer resources with another process and was chosen as a victim of deadlock

Question

I have a Java program that updates a table in MS SQL. This table is also available to web users through a website created in ColdFusion.

I recently got 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")); 

CF request, which is an error:

 <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 causes this error and how to fix it?

Answer 1

A deadlock occurs when 2 processes try to use the same data at the same time - as with an equal data requirement. This is most common when there are many update / insert operations (as you described). The database system "selects" one of the transactions as the "winner".

In some cases, deadlocks can be improved or softened by indexing, but only in the case of a choice - a good indexing strategy can increase the efficiency of the selection and increase the efficiency of row locking. However, in the case where the deadlock comes from the insertion associated with the upgrade, indexing will NOT help. Indeed, aggressive indexing can make matters worse, since indexes need to be updated along with inserts or data updates.

How to solve it largely depends on your system and what you are trying to do. You should either minimize insert / update locks, or provide more or faster resources. Binding inserts together and doses them, more procs or RAM (sometimes not always), clustering, splitting tables and data, fine tuning parallelism - all this can be a viable option. And there is no hard and fast rule.

Answer 2

If the table is never updated (only inserts), you can try to change the selection - without blocking or wrapping, select readuncommited in cftransation.

As Mark said, this is a database error, not ColdFusion, and locks are executed. If there are complex selections and updates, look, add indexes to the description columns.