Oracle 10g上的独占表(读取)锁? [英] Exclusive table (read) lock on Oracle 10g?
问题描述
有没有一种方法可以独占锁定表以在Oracle(10g)中读取?我对Oracle不太熟悉，所以我问DBA，他说不可能在Oracle中锁定表以进行读取吗?
Is there a way to exclusively lock a table for reading in Oracle (10g) ? I am not very familiar with Oracle, so I asked the DBA and he said it's impossible to lock a table for reading in Oracle?
我实际上正在寻找类似SQL Server(TABLOCKX HOLDLOCK)提示的东西.
I am actually looking for something like the SQL Server (TABLOCKX HOLDLOCK) hints.
编辑:
对某些答案的回答:我需要锁定一个表以进行读取的原因是要实现一个可由多个客户端读取的队列，但是2个客户端应该不可能读取同一条记录.所以实际发生的是:
In response to some of the answers: the reason I need to lock a table for reading is to implement a queue that can be read by multiple clients, but it should be impossible for 2 clients to read the same record. So what actually happens is:
- 锁定表
- 阅读队列中的下一项
- 从队列中删除项目
- 删除表锁
也许还有另一种方法(更有效)?
Maybe there's another way of doing this (more efficiently)?
推荐答案
如果您只想阻止任何其他会话修改数据，则可以发出
If you just want to prevent any other session from modifying the data you can issue
LOCK TABLE whatever
/
这会阻止其他会话更新数据，但我们无法阻止其他人读取数据.
This blocks other sessions from updating the data but we cannot block other peple from reading it.
请注意，在Oracle中很少需要这种表锁定，因为Oracle会执行读取一致性策略.这意味着如果我们运行一个查询需要花费十五分钟来运行返回的最后一行，则该查询将与第一行保持一致；换句话说，如果结果集以相反的顺序排序，我们仍然会看到完全相同的行.
Note that in Oracle such table locking is rarely required, because Oracle operates a policy of read consistency. Which means if we run a query that takes fifteen minutes to run the last row returned will be consistent with the first row; in other words, if the result set had been sorted in reverse order we would still see exactly the same rows.
修改
如果要实现队列(实际上不使用Oracle的了解详情.
If you want to implement a queue (without actually using Oracle's built-in Advanced Queueing functionality) then SELECT ... FOR UPDATE is the way to go. This construct allows one session to select and lock one or more rows. Other sessions can update the unlocked rows. However, implementing a genuine queue is quite cumbersome, unless you are using 11g. It is only in the latest version that Oracle have supported the SKIP LOCKED clause. Find out more.
这篇关于Oracle 10g上的独占表(读取)锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
