I've a little of code that connects for an Oracle database, locks a table, does several things into it and unlock it.
I must realize that if my program ended up being to freeze while a table was locked, the lock would release instantly.
Can there be in whatever way to setup Oracle, to get this done instantly?
For instance, I am considering something which would say "If user x has maintained a lock on table y for more than z seconds, rollback the transaction and release the table."
Whether it is not possible, can there be other things I'm able to do to offer the same results? Is even a real problem or shall we be held just being paranoid?
Thanks ahead of time.
To begin with, securing a table won't prevent another session from giving
SELECT claims from the data.
In Session 1, basically lock the table
SQL> lock table foo in exclusive mode; Table(s) Locked.
I'm able to then start Session 2 and query the information all I would like
SQL> select * from foo; COL1 ---------- 1 1
In Oracle, authors don't block visitors so that you can never prevent another session from querying the information inside a table.
It may sound like what you're attempting to implement is pessimistic securing. For the reason that situation, instead of securing the table, you perform a
SELECT FOR UPDATE that locks the specific entry you want to process. As lengthy as the rest of the periods also make an effort to perform a
SELECT FOR UPDATE (with respect to the Oracle version, potentially adding the
SKIP LOCKED qualifier and/or even the
WAIT qualifier). That locks the particular row you are processing and allows the 2nd session either choose another row or break or find you will find no rows to process with respect to the more knowledge about the implementation. That doesn't involve securing the table.
The only method for any lock to become launched is perfect for the session that acquired it to produce it (generally by ending the transaction) or the session that acquired so that it is ended. When the client application continues to be running although not doing almost anything to release the lock or terminate the session, the lock is going to be held indefinitely. A DBA will have to clearly get rid of the session, letting the transaction roll back and delivering the lock to obtain the system moving again. When the client application stops running or, a minimum of, stops reacting (I am still not obvious precisely what failure scenario you are talking about), it's possible that enabling dead-connection recognition (DCD) through the 'SQLNET.EXPIRE_TIME' parameter in the database level would make the database to find out the client is non-responsive and also to instantly get rid of the session, moving back the transaction and delivering the lock.
If you will find multiple periods processing data, however, it's generally much more suitable to make use of some type of positive securing. Otherwise, you are creating a method which will inevitably require the DBA to urgently find and kill periods to be able to obtain the business customers working again which will need increasingly more intervention the more busy it will get. That isn't something which DBAs relish doing and never something business customers enjoy worrying about. An easy positive securing plan would so something similar to
- Choose a vital to process plus some kind of date showing the final time the row was up-to-date.
- Update a standing column to "processing" to ensure that other periods don't try to procedure that same row.
- Process the entry inside your application
- When you are done processing, update the information while using key and also the time you selected in the initial step. Should you update 1 row, you realize not one other session has modified the information under consideration because you selected it. Should you update rows, you will know another session has modified the information because you selected it.
With this particular kind of architecture, it's relatively simple to question the database to determine what rows are now being processed and also to, for instance, work that sets the status column to "natural" after a little time period when the client has not finished. It's realatively simple for other periods to choose another row to process. And it is relatively safe if, for instance, the applying freezes a couple of hrs after which rebounds because it just finds once it's done processing that another session already re-processed the row.