如何在 Oracle 上通过 JOIN 使用“FOR UPDATE”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2586014/
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
How can I use "FOR UPDATE" with a JOIN on Oracle?
提问by tangens
The answerto another SO question was to use this SQL query:
另一个 SO 问题的答案是使用此 SQL 查询：
SELECT o.Id, o.attrib1, o.attrib2
FROM table1 o
JOIN (SELECT DISTINCT Id
FROM table1, table2, table3
WHERE ...) T1 ON o.id = T1.Id
Now I wonder how I can use this statement together with the keyword FOR UPDATE. If I simply append it to the query, Oracle will tell me:
现在我想知道如何将这个语句与关键字一起使用FOR UPDATE。如果我只是将它附加到查询中，Oracle 会告诉我：
ORA-02014: cannot select FOR UPDATE from view
ORA-02014: 无法从视图中选择 FOR UPDATE
Do I have to modify the query or is there a trick to do this with Oracle? With MySql the statement works fine.
我是否必须修改查询或者是否有使用 Oracle 执行此操作的技巧？使用 MySql 语句工作正常。
回答by davek
try:
尝试：
select .....
from <choose your table>
where id in (<your join query here>) for UPDATE;
EDIT: that might seem a bit counter-intuitive bearing in mind the question you linked to (which asked how to dispense with an IN), but may still provide benefit if your join returns a restricted set. However, there is no workaround: the oracle exception is pretty self-explanatory; oracle doesn't know which rows to lock becasue of the DISTINCT. You could either leave out the DISTINCTor define everything in a view and then update that, if you wanted to, without the explicit lock: http://www.dba-oracle.com/t_ora_02014_cannot_select_for_update.htm
编辑：考虑到您链接到的问题（询问如何免除IN），这似乎有点违反直觉，但如果您的连接返回受限集，仍然可能提供好处。但是，没有解决方法：oracle 异常是不言自明的；oracle 不知道要锁定哪些行，因为DISTINCT. 您可以省略DISTINCT或定义视图中的所有内容，然后根据需要更新它，而无需显式锁定：http: //www.dba-oracle.com/t_ora_02014_cannot_select_for_update.htm
回答by Alex Poole
It may depend what you want to update. You can do
... FOR UPDATE OF o.attrib1to tell it you're only interested in updating data from the main table, which seems likely to be the case; this means it will only try to lock that table and not worry about the implicit view in the join. (And you can still update multiple columns within that table, naming one still locks the whole row - though it will be clearer if you specify all the columns you want to update in the FOR UPDATE OFclause).
这可能取决于您要更新的内容。您可以
... FOR UPDATE OF o.attrib1告诉它您只对更新主表中的数据感兴趣，这似乎是这种情况；这意味着它只会尝试锁定该表，而不必担心连接中的隐式视图。（并且您仍然可以更新该表中的多个列，命名一个列仍然会锁定整行 - 尽管如果您在FOR UPDATE OF子句中指定要更新的所有列会更清楚）。
Don't know if that will work with MySQL though, which brings us back to Mark Byers point.
不知道这是否适用于 MySQL，这让我们回到 Mark Byers 的观点。
