如何在给定时间后自动解锁 Oracle 中的表？

How can I automatically unlock tables in Oracle after a given time?

提问by Paradoxyde

I have a bit of code that connects to an Oracle database, locks a table, does some things to it and unlock it.
I would like to know that if my program was to freeze while a table was locked, the lock would release automatically.

我有一些代码可以连接到 Oracle 数据库、锁定表、对其执行某些操作并解锁它。
我想知道如果我的程序在表被锁定时冻结，锁定会自动释放。

Is there any way to set up Oracle, to do this automatically?

有没有办法设置 Oracle，自动执行此操作？

For example, I'm thinking of something that would say "If user x has maintained a lock on table y for over z seconds, rollback the transaction and release the table."

例如，我在想这样的事情：“如果用户 x 对表 y 保持锁定超过 z 秒，则回滚事务并释放表。”

If it isn't possible, is there anything else I can do to achieve the same results? Is this even an actual problem or am I just being paranoid?

如果不可能，我还能做些什么来达到相同的结果？这甚至是一个实际问题还是我只是偏执？

Thanks in advance.

提前致谢。

回答by Justin Cave

First off, locking a table will not prevent another session from issuing SELECTstatements against the data.

首先，锁定表不会阻止另一个会话SELECT针对数据发出语句。

In Session 1, if I lock the table

在会话 1 中，如果我锁定表

SQL> lock table foo in exclusive mode;

Table(s) Locked.

I can then start Session 2 and query the data all I'd like

然后我可以开始会话 2 并查询所有我想要的数据

SQL> select * from foo;

      COL1
----------
         1
         1

In Oracle, writers do not block readers so you can never prevent another session from querying the data in a table.

在 Oracle 中，写入器不会阻止读取器，因此您永远无法阻止另一个会话查询表中的数据。

It sounds like what you are trying to implement is pessimistic locking. In that case, rather than locking the table, you do a SELECT FOR UPDATEthat locks the particular entry you intend to process. As long as all the other sessions also attempt to do a SELECT FOR UPDATE(depending on the Oracle version, potentially adding the SKIP LOCKEDqualifier and/or the WAITqualifier). That locks the specific row you're processing and lets the second session either select a different row or time out or find there are no rows to process depending on the specifics of the implementation. That does not involve locking the table.

听起来您要实现的是悲观锁定。在这种情况下，不是锁定表，而是锁定SELECT FOR UPDATE要处理的特定条目。只要所有其他会话也尝试执行SELECT FOR UPDATE（取决于 Oracle 版本，可能会添加SKIP LOCKED限定符和/或WAIT限定符）。这会锁定您正在处理的特定行，并让第二个会话选择不同的行或超时，或者根据实现的细节发现没有要处理的行。这不涉及锁定表。

The only way for a lock to be released is for the session that acquired it to release it (generally by ending the transaction) or for the session that acquired it to be terminated. If the client application is still running but not doing anything to release the lock or terminate the session, the lock will be held indefinitely. A DBA would need to explicitly kill the session, letting the transaction roll back and releasing the lock to get the system moving again. If the client application stops running or, at least, stops responding (I'm still not clear exactly what failure scenario you're discussing), it is possible that enabling dead-connection detection (DCD) via the 'SQLNET.EXPIRE_TIME' parameterat the database level would cause the database to determine that the client is non-responsive and to automatically kill the session, rolling back the transaction and releasing the lock.

释放锁的唯一方法是获取它的会话释放它（通常通过结束事务）或终止获取它的会话。如果客户端应用程序仍在运行但没有执行任何操作来释放锁定或终止会话，则锁定将被无限期持有。DBA 需要明确终止会话，让事务回滚并释放锁以使系统再次运行。如果客户端应用程序停止运行，或者至少停止响应（我仍然不清楚您正在讨论的确切故障场景），则可能通过“SQLNET.EXPIRE_TIME”参数启用死连接检测（DCD）在数据库级别会导致数据库确定客户端没有响应并自动终止会话，回滚事务并释放锁。

If there are multiple sessions processing data, however, it is generally much preferable to use some form of optimistic locking. Otherwise, you're designing a system that will inevitably need the DBA to urgently find and kill sessions in order to get the business users working again and that will require more and more intervention the busier it gets. That's not something that DBAs relish doing and not something business users enjoy complaining about. A simple optimistic locking scheme would so something like

但是，如果有多个会话处理数据，通常最好使用某种形式的乐观锁定。否则，您正在设计的系统将不可避免地需要 DBA 紧急查找和终止会话，以便让业务用户再次工作，并且随着它变得越来越忙，这将需要越来越多的干预。这不是 DBA 喜欢做的事情，也不是业务用户喜欢抱怨的事情。一个简单的乐观锁定方案就像

• Select a key to process and some sort of date indicating the last time the row was updated.
• Update a status column to "processing" so that other sessions do not try to process that same row.
• Process the entry in your application
• When you're done processing, update the data using the key and the time you selected in the first step. If you update 1 row, you know no other session has modified the data in question since you selected it. If you update 0 rows, you know that some other session has modified the data since you selected it.

• 选择要处理的键和指示该行上次更新时间的某种日期。
• 将状态列更新为“正在处理”，以便其他会话不会尝试处理同一行。
• 处理申请中的条目
• 完成处理后，使用您在第一步中选择的密钥和时间更新数据。如果您更新 1 行，您就知道自您选择它以来没有其他会话修改过相关数据。如果您更新 0 行，您就知道自您选择数据以来，某个其他会话已经修改了数据。

With this sort of architecture, it's relatively easy to query the database to see what rows are being processed and to, for example, have a job that sets the status column back to "unprocessed" after some period of time if the client hasn't finished. It's realatively easy for other sessions to pick a different row to process. And it's relatively safe if, for example, the application freezes for a couple hours and then recovers since it just finds once it's done processing that some other session already re-processed the row.

使用这种架构，查询数据库以查看正在处理的行以及例如，如果客户端没有在一段时间后将状态列设置回“未处理”，则相对容易完成的。其他会话选择不同的行进行处理非常容易。例如，如果应用程序冻结几个小时然后恢复，那么它是相对安全的，因为它在完成处理后才发现某个其他会话已经重新处理了该行。