Oracle 10g 上的独占表（读）锁？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1487596/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exclusive table (read) lock on Oracle 10g?
提问by Philippe Leybaert
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?
有没有办法在 Oracle (10g) 中以独占方式锁定表以供读取？我对Oracle 不是很熟悉，所以我问了DBA，他说在Oracle 中不可能锁定表进行读取？
I am actually looking for something like the SQL Server (TABLOCKX HOLDLOCK) hints.
我实际上正在寻找类似 SQL Server (TABLOCKX HOLDLOCK) 提示的东西。
EDIT:
编辑：
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:
回应一些回答：我需要锁表进行读取的原因是为了实现一个可以被多个客户端读取的队列，但是2个客户端读取同一条记录应该是不可能的。所以实际发生的是：
- Lock table
- Read next item in queue
- Remove item from the queue
- Remove table lock
- 锁表
- 读取队列中的下一项
- 从队列中删除项目
- 移除表锁
Maybe there's another way of doing this (more efficiently)?
也许有另一种方法可以做到这一点（更有效）？
回答by APC
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.
这会阻止其他会话更新数据，但我们不能阻止其他人读取它。
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 中很少需要此类表锁定，因为 Oracle 运行读取一致性策略。这意味着如果我们运行一个需要 15 分钟来运行的查询，返回的最后一行将与第一行保持一致；换句话说，如果结果集以相反的顺序排序，我们仍然会看到完全相同的行。
edit
编辑
If you want to implement a queue (without actually using Oracle's built-in Advanced Queueing functionality) then SELECT ... FOR UPDATEis 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 LOCKEDclause. Find out more.
如果您想实现一个队列（而不实际使用 Oracle 的内置高级队列功能），那么SELECT ... FOR UPDATE就是要走的路。此构造允许一个会话选择并锁定一行或多行。其他会话可以更新未锁定的行。但是，除非您使用 11g，否则实现真正的队列非常麻烦。Oracle 仅在最新版本中支持该SKIP LOCKED子句。 了解更多。
回答by Gary Myers
1. Lock table
2. Read next item in queue
3. Remove item from the queue
4. Remove table lock
Under this model a lot of sessions are going to be doing nothing but waiting for the lock, which seems a waste. Advanced Queuing would be a better solution.
在这种模型下，很多会话除了等待锁定之外什么都不做，这似乎是一种浪费。高级排队将是一个更好的解决方案。
If you want a 'roll-your-own' solution, you can look into SKIP LOCKED. It wasn't documented until 11g, but it is present in 10g. In this algorithm you would do
如果您想要一个“自己动手”的解决方案，您可以查看SKIP LOCKED。它直到 11g 才被记录下来，但它存在于 10g 中。在这个算法中，你会做
1. SELECT item FROM queue WHERE ... FOR UPDATE SKIP LOCKED
2. Process item
3. Delete the item from the queue
4. COMMIT
That would allow multiple processes to consume items off the queue.
这将允许多个进程消耗队列外的项目。
回答by Hank Gay
The TABLOCKXand HOLDLOCKhints you mentioned appear to be used for writes, not reads (based on http://www.tek-tips.com/faqs.cfm?fid=3141). If that's what you're after, would a SELECT FOR UPDATEfit your need?
您提到的TABLOCKX和HOLDLOCK提示似乎用于写入，而不是读取（基于http://www.tek-tips.com/faqs.cfm?fid=3141）。如果这就是您所追求的，是否SELECT FOR UPDATE适合您的需求？
UPDATE: Based on your update, SELECT FOR UPDATEshould work, assuming all clients use it.
更新：根据您的更新SELECT FOR UPDATE，假设所有客户端都使用它，应该可以工作。
UPDATE 2: You may not be in a position to do anything about it right now, but this sort of problem is actually an ideal fit for something other than a relational database, such as AMQP.
更新 2：您现在可能无法对此做任何事情，但这种问题实际上非常适合关系数据库以外的其他内容，例如AMQP。
回答by Tony Andrews
If you mean, lock a table so that no other session can read from the table, then no, you can't. Why would you want to do that anyway?
如果您的意思是锁定一个表，以便其他会话无法从该表中读取，那么不，您不能。你为什么要这样做？
