从Oracle跟踪文件中查找死锁中涉及的所有语句? [英] Finding all statements involved in a deadlock from an Oracle trace file?

问题描述

据我了解，涉及行锁定的死锁的典型情况需要四个SQL语句.二合一事务更新A行和B行，然后另外两个事务在独立事务中更新相同行，并且需要相同的锁，但是顺序相反.

As I understand it, the typical case of a deadlock involving row-locking requires four SQL statements. Two in one transaction to update row A and row B, and then a further two in a separate transaction to update the same rows, and require the same locks, but in the reverse order.

事务1在事务2可以请求之前在行A上获得了锁，事务2在事务1可以获取它之前在行B上获得了锁，而任何一个都无法获得其余的必需锁.一个或任何一个事务必须回滚，以便另一个可以完成.

Transaction 1 gets the lock on row A before transaction 2 can request it, transaction 2 gets the lock on row B before transaction 1 can get it, and neither can get the remaining required locks. One or either transaction has to be rolled back, so the other can complete.

当我在死锁后查看Oracle跟踪文件时，它似乎只突出显示了两个查询.这些似乎是每笔交易中的最后一笔.

When I review an Oracle trace file after a deadlock, it only seems to highlight two queries. These seem to be the last one out of each transaction.

如何识别每个事务中涉及的其他语句，或者Oracle跟踪文件中缺少该语句?

How can I identify the other statements involved in each transaction, or is this missing in an Oracle trace file?

如果需要，我可以包含特定跟踪文件的相关位.

I can include relevant bits of the specific trace file if required.

推荐答案

您是正确的，在典型的行级死锁中，您将让会话1执行sql_a来锁定行1.然后会话2将执行sql_b这将锁定第2行.然后，会话1将执行sql_c尝试锁定第2行，但是会话2尚未提交，因此会话1开始等待.最后，会话2出现，并发出sql_d，试图锁定第1行，但是由于会话1持有该锁，因此它开始等待.三秒钟后，检测到死锁，并且其中一个会话将捕获ORA-00060并写入跟踪文件.

You're correct, in a typical row-level deadlock, you'll have session 1 execute sql_a that will lock row 1. Then session 2 will execute sql_b that will lock row 2. Then session 1 will execute sql_c to attempt to lock row 2, but session 2 has not committed, and so session 1 starts waiting. Finally, session 2 comes along, and it issues sql_d, attempting to lock row 1, but, since session 1 holds that lock, it starts waiting. Three seconds later, the deadlock is detected, and one of the sessions will catch ORA-00060 and the trace file is written.

在这种情况下，跟踪文件将包含sql_c和sql_d，但不包含sql_a或sql_b.

In this scenario, the trace file will contain sql_c and sql_d, but not sql_a or sql_b.

问题在于，信息实际上在任何地方都不可用.考虑您执行一个DML，如果不存在DML，它将启动一个事务，生成一堆撤消和重做，然后进行更改.但是，一旦发生这种情况，会话将不再与该SQL语句关联.确实没有干净的方法可以返回并找到该信息.

The problem is that information just really isn't available anywhere. Consider that you execute a DML, it starts a transaction if one doesn't exist, generates a bunch of undo and redo, and the change is made. But, once that happens, the session is no longer associated with that SQL statement. There's really no clean way to go back and find that information.

sql_c和sql_d是发生死锁时与这些会话相关联的语句，因此，很明显，Oracle可以识别它们并将其包含在跟踪文件中.

sql_c and sql_d, on the other hand, are the statements that were associated with those sessions when the deadlock occurred, so, clearly, Oracle can identify them, and include that in the trace file.

因此，您是对的，有关sql_a和sql_b的信息不在跟踪中，并且它确实不容易获得.

So, you're correct, the information about sql_a and sql_b is not in the trace, and it's really not readily available.

希望有帮助.

这篇关于从Oracle跟踪文件中查找死锁中涉及的所有语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！