问题为什么我们会收到错误“TT6002锁请求被拒绝因死锁而被拒绝”而我们的SQL主要是对唯一行进行插入和更新解决方案回答1. 当未提交的值被更新时优化器会在执行阶段移除该旧值而不是等待提交时。在这种情况下用户先插入后再更新。如果更新涉及在并发事务中将索引列更新为相同值情况也会更糟。2. 执行时从索引中删除需要某种下一锁来保护已删除位置以备需要回滚时使用。3. 我们有两种机制可以防止随机行的下一轮锁定具体如下a.对于非唯一索引我们使用通用锁来阻止所有可序列化的扫描b。对于唯一索引我们插入一个带有锁4的假行。在这种情况下它是非唯一索引但由于某些并发FK验证需要可序列化扫描无法放置3.a。结果我们被迫用下一个锁配合X锁而X锁是死锁的根源。以下应用端的变更有助于处理或避免死锁1. 应用程序在发现死锁时应重试事务这实际上是 Timesten 的标准和推荐做法。2. 通过将插入更新合并为一个事务避免更新未提交的值。3. 避免同时更新索引列到与其已存在的值相同。这在11.2.2中被注意到但在11.2.1和7.0中也可能出现。这种死锁在某些情况下是由于上述3级内部导致的死锁情况因此我们正在修复该漏洞因为遇到该问题的用户不会做出应用端的修改。这个问题在11.2.2.4.6版本中修复了15941309、16738960和16739014的漏洞。在11.2.2.6.0版本中修复了作为16805039的bug。这个问题在11.2.2.5.x版本中没有修复因为11.2.2.6.0会和11.2.2.5.1差不多同时发布。SummaryQuestion: Why do we get error TT6002: Lock Request Denied Because Of Deadlock when our SQL is mostly inserts and updates on unique rows?SolutionAnswer:1. When an uncommitted value is updated, optimizer will remove the old value from the index during execution phase instead of waiting until commit time. In this case, user did an insert followed by an update. Situation can also be worse if the update involves updating the indexed column to the same value in concurrent transactions.2. Delete from the index at execution will require some kind of next lock to protect the deleted position in case a rollback is needed.3. We have two mechanisms to prevent next locks on random row as follows:a. For non-unique index, we use a general lock to prevent all serializable scanb. For unique index, we insert a dummy row with a lock4. In this case, it was a non-unique index but 3.a could not be placed because some concurrent FK verification need serializable scan. As a result, we were forced to use next lock with X lock and X lock is a source of deadlock.The following application side changes could help handle or avoid the deadlock:1. Application should retry the transaction when it catches that a deadlock occurs, this is actually standard and recommended practice for Timesten.2. Avoid updating of uncommitted value by combining insertupdate into one transaction.3. Avoid doing concurrent updates of indexed column to the same value as it already is.This was noticed in 11.2.2, but can occur in 11.2.1 and 7.0. This deadlock in some cases is the result of internally caused deadlock situation described above as 3, so for that reason we are fixing the bug since the particular users that encountered it would not make application side changes. This was fixed in 11.2.2.4.6 in bugs 15941309, 16738960, and 16739014. It is fixed in 11.2.2.6.0 as bug 16805039. This was not fixed in the 11.2.2.5.x release since 11.2.2.6.0 will come out around the same time as 11.2.2.5.1.