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

Question

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.

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

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."

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.

Answer 1

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

In Session 1, if I lock the table

SQL> lock table foo in exclusive mode;

Table(s) Locked.

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

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.

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 UPDATE that 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 LOCKED qualifier and/or the WAIT qualifier). 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.

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' parameter at 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.

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

• 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.

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.