java 事务（进程 ID）因锁定而死锁 | 与另一个进程的通信缓冲区资源并已被选为死锁受害者
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10915667/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Transaction (Process ID) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim
提问by Mike
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
我有一个 Java 程序，用于更新 MS SQL 中的表。Web 用户也可以通过 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 :
出错的 CF 查询是：
<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?
是什么导致了这个错误，我该如何解决？
回答by Mark A Kruger
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".
当 2 个进程试图同时访问相同的数据时，就会发生死锁 - 两者都对数据具有相同的要求。当正在进行大量更新/插入活动时（如您所描述的），这是最常见的。数据库系统“选择”交易之一作为“赢家”。
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.
如何解决它在很大程度上取决于您的系统以及您尝试执行的操作。您必须最小化插入/更新锁定或以某种方式提供更多或更快的资源。将插入捆绑在一起并对它们进行批处理、更多的 procs 或 RAM（有时 - 并非总是如此）、集群、拆分表和数据、微调并行性 - 这些都可能是可行的选择。而且没有硬性规定。
回答by Paul
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.
如果从未更新表（只有插入），那么您可能想尝试将选择更改为 - 在 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.
正如马克所说，这是一个数据库错误，而不是 ColdFusion 并且锁正在占用。如果有复杂的选择和更新，请向子句列添加索引。
