Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using table locking in my Oracle database. I have a small program with a function that locks my table, does some things and unlocks it again. If many instances are run and requests are made simultaneously, the table locks will be queue, like updates or other table modifications would be.

In a positive spirit of bug prevention, I'm concerned that if the Database crashes or suddenly becomes unavailable for any reason, while the table is locked, the program instances in queue would be stuck there, waiting for an answer that will never come.

Obviously, getting my program frozen is undesirable. Is there any way a request can remove itself from the queue after a set time? It there maybe a better way to do this?

If this proves to be a problem, I do know that I can use the NO WAIT parameter in my table lock, and then repeat requests for a certain time before giving up, but that seems quite inefficient and I lose the benefit of the database queue.

What do you think?

share|improve this question

migrated from stackoverflow.com Jan 17 '12 at 19:10

This question came from our site for professional and enthusiast programmers.

    
The answer depends on the database, and the language/driver you're using on the client side, but most provides one mean or another to set a timeout on the statements you execute. –  nos Jan 17 '12 at 14:46
2  
whats wrong with Oracle's default concurrency/consistency model? Rolling your own locking via table locks is probably not a good idea. See here: docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm –  tbone Jan 17 '12 at 14:53
1  
Is "mall program" an implementation of a shopping cart or just a bad piece of code? –  APC Jan 17 '12 at 15:07
    
It's a typo, "small program". –  Paradoxyde Jan 17 '12 at 15:20

1 Answer 1

up vote 3 down vote accepted

From Oracle 11gR1 you can use the WAIT instruction in DDL:

lock table t in exclusive mode wait 10;

This will wait 10 seconds before giving up.

The WAIT instruction has been available for a long time (9i) for DML:

SELECT * FROM t FOR UPDATE WAIT 10;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.