从 Oracle 跟踪文件中查找涉及死锁的所有语句？
据我了解，涉及行锁定的死锁的典型情况需要四个 SQL 语句。两个事务在一个事务中更新 A 行和 B 行，然后另外两个事务在一个单独的事务中更新相同的行，并且需要相同的锁，但顺序相反。
事务 1 在事务 2 可以请求之前获取了 A 行的锁，事务 2 在事务 1 之前获取了 B 行的锁，并且事务 2 都无法获取剩余所需的锁。必须回滚一个或其中一个事务，以便另一个事务可以完成。
当我在死锁后查看 Oracle 跟踪文件时，它似乎只突出显示两个查询。这些似乎是每笔交易的最后一笔。
如何识别每个事务中涉及的其他语句，或者 Oracle 跟踪文件中是否缺少此语句？
如果需要，我可以包含特定跟踪文件的相关位。
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.
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.
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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
你是对的，在典型的行级死锁中，您将让会话 1 执行 sql_a 来锁定行 1。然后会话 2 将执行 sql_b 来锁定行 2。然后会话 1 将执行 sql_c 来尝试锁定行2，但会话 2 尚未提交，因此会话 1 开始等待。最后，会话 2 出现，它发出 sql_d，尝试锁定行 1，但是，由于会话 1 持有该锁，因此它开始等待。三秒后，检测到死锁，其中一个会话将捕获 ORA-00060 并写入跟踪文件。
在这种情况下，跟踪文件将包含 sql_c 和 sql_d，但不包含 sql_a 或 sql_b。
问题是信息确实无法在任何地方获得。假设您执行一个 DML，如果事务不存在，它会启动一个事务，生成一堆撤消和重做，然后进行更改。但是，一旦发生这种情况，会话就不再与该 SQL 语句关联。确实没有干净的方法可以返回并查找该信息。
另一方面，sql_c 和 sql_d 是发生死锁时与这些会话关联的语句，因此，很明显，Oracle 可以识别它们，并将其包含在跟踪文件中。
所以，你是对的，有关 sql_a 和 sql_b 的信息不在跟踪中，而且它确实不容易获得。
希望有帮助。
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.
In this scenario, the trace file will contain sql_c and sql_d, but not sql_a or sql_b.
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 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.
So, you're correct, the information about sql_a and sql_b is not in the trace, and it's really not readily available.
Hope that helps.