Oracle在执行DML语句或仅执行行时会锁定整个表吗? [英] Will Oracle lock the whole table while performing a DML statement or just the row

问题描述

当我尝试在db表中插入/更新某些内容时，Oracle将锁定整个表还是仅锁定要插入/更新的行?

When i try to insert/update something in a db table, will Oracle lock the whole table or only the row being inserted/updated?

这可以通过外部配置控制吗?

Is this something that can be controlled through external configuration?

推荐答案

我们可以使用LOCK TABLE命令显式地发出锁. 了解更多信息

We can issue locks explicitly with the LOCK TABLE command. Find out more

否则，插入不会锁定任何其他行.由于Oracle的读取隔离模型，该行仅存在于我们的会话中，直到我们提交它为止，因此其他任何人都无法对其进行任何操作. 了解详情.

Otherwise, an insert does not lock any other rows. Because of Oracle's read isolation model that row only exists in our session until we commit it, so nobody else can do anything with it. Find out more.

更新语句仅锁定受影响的行.除非我们用SELECT ... FOR UPDATE实现了悲观的锁定策略. 了解更多信息.

An update statement only locks the affected rows. Unless we have implemented a pessimistic locking strategy with SELECT ... FOR UPDATE. Find out more.

最后，在Oracle中，编写者不会阻止读者.因此，即使锁定的行也可以被其他会话读取，但无法更改. 查找更多.

Finally, in Oracle writers do not block readers. So even locked rows can be read by other sessions, they just can't be changed. Find out more.

此行为已植入Oracle内核，并且不可配置.

This behaviour is baked into the Oracle kernel, and is not configurable.

Justin很好地介绍了表级DDL锁.该锁将导致在表上执行DDL的会话等待直到DML会话提交，除非DDL类似于CREATE INDEX，在这种情况下，它将立即因ORA-00054而失败.

Justin makes a good point about the table-level DDL lock. That lock will cause a session executing DDL on the table to wait until the DML session commits, unless the DDL is something like CREATE INDEX in which case it will fail immediately with ORA-00054.

这篇关于Oracle在执行DML语句或仅执行行时会锁定整个表吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！