Exclusive table (read) lock on Oracle 10g?
Is there a way to lock a table for reading in Oracle (10g)? I'm not very familiar with Oracle, so I asked the DBA and he said that it is not possible to lock a table for reading in Oracle?
I'm really looking for something like SQL Server hints (TABLOCKX HOLDLOCK).
EDIT
In response to some of the answers: The reason I need to lock the table for reading is to implement a queue that can be read by multiple clients, but 2 clients must be able to read the same record. So what is actually going on:
- Locking table
- Read the next item in the queue
- Remove item from queue
- Remove table lock
Maybe there is another way to do this (more efficiently)?
source to share
If you just want to prevent some other session from changing the data you can give
LOCK TABLE whatever
/
This blocks other sessions from updating data, but we cannot block reading other files.
Note that in Oracle, such table locks are rarely required because Oracle uses a read consistency policy. This means that if we run a query that takes fifteen minutes to run the last returned row, it will match the first row; in other words, if the result set were sorted in reverse order, we would see exactly the same rows.
change
If you want to implement a queue (without using Oracle built-in extended queue functions) then SELECT ... FOR UPDATE
is the way to go.This construct allows a single session to fetch and block one or more rows. Other sessions can update unlocked rows. However, the implementation of a genuine queue is quite cumbersome unless you are using 11g. Only the latest version of Oracle supports the offer SKIP LOCKED
. Find out more .
source to share
1. Lock table
2. Read next item in queue
3. Remove item from the queue
4. Remove table lock
Under this model, many sessions will do nothing but wait for a lock, which seems like a waste. An advanced queue would be the best solution.
If you want to use your own "roll-your-own" solution you can look at SKIP LOCKED . It was not documented until 11g, but it is present in 10g. In this algorithm, you would do
1. SELECT item FROM queue WHERE ... FOR UPDATE SKIP LOCKED
2. Process item
3. Delete the item from the queue
4. COMMIT
This will allow multiple processes to consume items from the queue.
source to share
The hints you provided TABLOCKX
and HOLDLOCK
appears to be used for writing and not reading (based on http://www.tek-tips.com/faqs.cfm?fid=3141 ) If this is what you need it would be SELECT FOR UPDATE
meet your need?
UPDATE: Based on your update, SELECT FOR UPDATE
should work, assuming all clients are using it.
UPDATE 2: You can't do anything right now, but this kind of problem is actually ideal for anything other than a relational database like AMQP .
source to share