oracle oracle中有哪些不同类型的锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15936323/
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
What are different types of locks in oracle
提问by Ravi
Please anyone explain locking mode in Oracle i.e. Share, Exclusive and Update lock. I found many theories on this and according to that
请任何人解释 Oracle 中的锁定模式，即共享、独占和更新锁定。我发现了很多关于这个的理论，根据那个
Share lock : Nobody can change data,Read only purpose
共享锁：没有人可以更改数据，只读目的
Exclusive lock : Only one user/connection are allow to change the data.
独占锁：只允许一个用户/连接更改数据。
Update lock : Rows are locked till user made commit/rollback.
更新锁定：行被锁定，直到用户提交/回滚。
Then, I tried shared to check how it works
然后，我尝试共享以检查它是如何工作的
SQL> lock table emp in share mode;
Table(s) Locked.
SQL> update emp set sal=sal+10;
14 rows updated.
Then, I found that, user can change data after share lock. Then, what makes it different from exclusive lock and update lock.
然后，我发现，用户可以在共享锁定后更改数据。那么，它与排他锁和更新锁有何不同。
Another question, how Update lock and exclusive lock are different with each other, even they seems almost equivalent.
另一个问题，更新锁和排他锁是如何不同的，甚至它们看起来几乎是等效的。
回答by hagrawal
Posting explanation for future visitors, and it also gives the answer.
为未来的访问者发布解释，它也给出了答案。
Shared lock
共享锁
- Before I begin let me first say that there are 5 types of table locks - row shared, row exclusive, shared, shared row exclusive and exclusive. And shared lock is one among these. Also, please note that there are row locks, which are different than table locks. Follow the link I have provided in end to read about all this.
- A shared lock is acquired on the table specified in following statement –
LOCK TABLE table IN SHARE MODE; - This lock prevents other transactions from getting “row exclusive” (this lock is used by INSERT, UPDATE and DELETE statement), “shared row exclusive” and “exclusive” table locks, otherwise everything is permitted.
- So, this means that a shared lock will block other transactions from executing INSERT, UPDATE and DELETE statements on that table but will allow other transactions to update the rows using “SELECT … FOR UPDATE” statement because for this statement a “row shared” lock is required, and it is permitted when a “shared” lock is required.
- 在开始之前让我先说一下表锁有 5 种类型 - 行共享、行独占、共享、共享行独占和独占。共享锁就是其中之一。另外，请注意有不同于表锁的行锁。按照我最后提供的链接阅读所有这些内容。
- 在以下语句中指定的表上获取共享锁 –
LOCK TABLE table IN SHARE MODE; - 这个锁防止其他事务获得“行独占”（这个锁被 INSERT、UPDATE 和 DELETE 语句使用）、“共享行独占”和“独占”表锁，否则一切都被允许。
- 因此，这意味着共享锁将阻止其他事务在该表上执行 INSERT、UPDATE 和 DELETE 语句，但将允许其他事务使用“SELECT ... FOR UPDATE”语句更新行，因为对于此语句，“行共享”锁是必需的，并且在需要“共享”锁时是允许的。
Below table is a good summary of locks and what's permitted.
下表很好地总结了锁和允许的锁。
Since many users will follow this question so I decided to go one more step further and put my learning notes, I hope folks will be benefited from it:由于很多用户会关注这个问题，所以我决定更进一步，把我的学习笔记，我希望人们能从中受益：
Source of this information and also excellent reading about Oracle locks此信息的来源以及有关 Oracle 锁的优秀读物。
回答by David Aldridge
It's very well explained in the documentation: http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502
它在文档中有很好的解释：http: //docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502
In your example you locked the table in shared mode. This does not prevent other sessions locking the same object in shared mode, but it does prevent them from locking it in exclusive mode so you could not drop the table (which requires an exclusive lock) while it is being updated (which has a shared lock).
在您的示例中，您以共享模式锁定了表。这不会阻止其他会话以共享模式锁定同一对象，但会阻止它们以独占模式锁定它，因此您无法在更新表（需要独占锁）时删除该表（具有共享锁） ）。
