在什么情况下将导致LockAcquisitionException和SQLCODE = -911，SQLSTATE = 40001，SQLERRMC = 68 [英] In what condition will cause LockAcquisitionException and SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68
问题描述
据我所知，当一个线程试图更新被另一个线程锁定的记录行时，会发生 LockAcquisitionException .(如果我错了，请纠正我)
所以我尝试模拟如下:
我使用 dbVisualizer 锁定一行记录，然后使用应用程序对同一条记录运行更新查询.最后，我只是用原因代码68导致全局事务超时，而不是 LockAcquisitionException .
因此，我在想我的理解是错误的. LockAcquisitionException 不会通过这种方式发生.可以建议或给出一些简单的示例来创建 LockAcquisitionException 吗?
由于锁定超时，您将获得LockAcquisitionException(SQLCODE = -911 SQLERRMC = 68).
比较dbViz和休眠状态的操作可能无济于事，因为它们可能在jdbc级别使用不同的类/方法和设置，这可能会影响异常详细信息.重要的是，在Db2级别上，无论他们报告锁定超时的异常名称如何，都经历过SQLCODE = -911和SQLERRMC = 68.
根据许多因素，您可以在诸如UPDATE或DELETE或INSERT或SELECT之类的语句(以及其他包括DDL和命令)上获得锁定超时.
所有锁定超时都有一个共同点:一个事务等待太长时间并回滚，因为另一个事务没有足够快地提交.
锁定超时诊断和避免锁定超时是不同的主题.
可以根据选择的设计，包括混合使用这些，在数据库级别，连接级别或语句级别设置等待锁的时间长度.您还可以通过调整数据库参数(例如CUR_COMMIT，LOCK_TIMEOUT)以及在语句级别或连接级别调整隔离级别来调整Db2的锁定行为.
明智的做法是在考虑避免方法之前确保准确的诊断.
在运行Db2-LUW v10.5.0.9时，请仔细研究此页面以及所有相关链接:
在很多情况下都可能导致锁定超时，因此最好准确地了解哪种与您的情况有关.
避免锁冲突是配置和事务设计的问题，因此这是一个更大的话题.该配置可以在Db2级别上，也可以在应用程序层上，也可以在两者上都进行.
有时，错误会导致锁定超时，例如，当应用程序服务器线程的数据库连接处于挂起状态且尚未提交且未被应用程序正确清除时.
您应该诊断锁定超时中的参与者.在Db2-LUW上执行锁冲突诊断的方法有多种，因此请选择一种适合您的方法.
一个仍然可以在V10.5.0.9上运行的简单诊断工具是使用Db2注册变量DB2_CAPTURE_LOCKTIMEOUT = ON，尽管该方法已被弃用.您可以即时设置此变量(并取消设置)，而无需任何服务中断.因此，如果您的可重现方案导致SQLCODE = -911 SQLERRMC = 68(锁定超时)，则可以打开此变量，重复测试，然后关闭该变量.如果打开了变量，并且发生了锁定超时，Db2将编写一个新的文本文件，其中包含有关锁定情况下参与者的信息，其中显示的详细信息可帮助您了解正在发生的事情，并可以让您在解决问题时考虑解决问题的方法.有足够的事实.您不想永久设置此变量，因为如果锁定超时，它可能会影响性能并填充Db2诊断文件系统.因此，您必须要小心.在此页面的知识中心中了解有关此变量的信息:https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.regvars.doc/doc/r0005657.html 您可以通过仔细研究这些文件的内容来诊断锁定超时，尽管当然也有必要了解这些细节.这是常规的DBA活动.
另一种方法是将db2pdcfg -catch与自定义db2cos脚本一起使用，以确定在Db2抛出-911之后执行的操作.这需要脚本技巧，它使您可以准确地确定-911之后要收集的诊断信息以及将这些诊断信息存储在何处.
另一种涉及更多工作但可能带来更多收益的方法是使用 As my understanding, So I try to simulate as follow: I lock a row of record using Thus, I am thinking that my understanding is wrong. You will get LockAcquisitionException (SQLCODE=-911 SQLERRMC=68) as a result of a lock timeout. It may be unhelpful to compare the actions of dbViz with hibernate because they may use different classes/methods and settings at jdbc level which can influence the exception details. What matters is that at Db2 level both experienced SQLCODE=-911 with SQLERRMC=68 regardless of the exception-name they report for the lock-timeout. You can get a lock-timeout on statements like UPDATE or DELETE or INSERT or SELECT (and others including DDL and commands), depending on many factors. All lock-timeouts have one thing in common: one transaction waited too long and got rolled-back because another transaction did not commit quickly enough. Lock-timeout diagnosis and Lock-Timeout avoidance are different topics. The length of time to wait for a lock can be set at database level, connection level, or statement level according to what design is chosen, including mixing these. You can also adjust how Db2 behaves for locking by adjusting database parameters like CUR_COMMIT, LOCK_TIMEOUT and by adjusting isolation-level at statement-level or connection-level. It's wise to ensure accurate diagnosis before thinking about avoidance. As you are running Db2-LUW v10.5.0.9, consider careful study of this page and all related links: There are many situations that can lead to a lock timeout, so it's better to know exactly which situation is relevant for your case(s). Avoiding lock-conflicts is a matter of both configuration and transaction design so that is a bigger topic. The configuration can be at Db2 level or at application layer or both. Sometimes bugs cause lock-timeouts, for example when app-server threads have a database-connection that is hung and has not committed and is not being cleaned up correctly by the application. You should diagnose the participants in the lock timeout. There are different ways to do lock-conflict diagnosis on Db2-LUW so choose the one that works for you. One simple diagnosis tool that still works on V10.5.0.9 is to use the Db2-registry variable DB2_CAPTURE_LOCKTIMEOUT=ON, event though the method is deprecated. You can set this variable (and unset it) on the fly without needing any service-outage. So if you have a recreatable scenario that results in SQLCODE=-911 SQLERRMC=68 (lock timeout), you can switch on this variable, repeat the test, then switch off the variable. If the variable is switched on, and a lock timeout happens, Db2 will write a new text file containing information about the participants in the locking situation showing details that help you understand what is happening and that lets you consider ways to resolve the issue when you have enough facts. You don't want to keep this variable permanently set because it can impact perormance and fill up the Db2 diagnostics file-system if you get a lot of lock-timeouts. So you have to be careful. Read about this variable in the Knowledge Center at this page:
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.regvars.doc/doc/r0005657.html
You diagnose the lock-timeout by careful study of the contents of these files, although of course it's necessary to understand the details also. This is a regular DBA activity. Another method is to use db2pdcfg -catch with a custom db2cos script, to decide what to do after Db2 throws the -911. This needs scripting skills and it lets you decide exactly what diagnostics to collect after the -911 and where to store those diagnostics. Another method which involves much more work but potentially pays more dividends is to use an 这篇关于在什么情况下将导致LockAcquisitionException和SQLCODE = -911，SQLSTATE = 40001，SQLERRMC = 68的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！事件监视器进行锁定.该文档位于:LockAcquisitionException will happen when a thread is trying to update a row of record that is locking by another thread. ( Please do correct me if I am wrong )dbVisualizer, then I using application to run a update query on the same record as well. At the end, I am just hitting global transaction time out instead of LockAcquisitionException with reason code 68.LockAcquisitionException is not happen by this way. Can kindly advise or give some simple example to create the LockAcquisitionException ?event monitor for locking. The documentation is at:
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0054074.html
Be sure to study the "Related concepts" and "Related tasks" pages also.
