database Oracle 会在执行 DML 语句时锁定整个表还是仅锁定行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18021447/
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
Will Oracle lock the whole table while performing a DML statement or just the row
提问by Victor
When i try to insert/update something in a db table, will Oracle lock the whole table or only the row being inserted/updated?
当我尝试在 db 表中插入/更新某些内容时，Oracle 会锁定整个表还是仅锁定正在插入/更新的行？
Is this something that can be controlled through external configuration?
这是可以通过外部配置控制的东西吗？
回答by APC
We can issue locks explicitly with the LOCK TABLE command. Find out more
我们可以使用 LOCK TABLE 命令显式地发出锁。 了解更多
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.
否则，插入不会锁定任何其他行。由于 Oracle 的读取隔离模型，该行仅存在于我们的会话中，直到我们提交它，因此其他人无法对它做任何事情。 了解更多。
An update statement only locks the affected rows. Unless we have implemented a pessimistic locking strategy with SELECT ... FOR UPDATE. Find out more.
更新语句只锁定受影响的行。除非我们使用 SELECT ... FOR UPDATE 实现了悲观锁定策略。 了解更多。
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.
此行为已融入 Oracle 内核，不可配置。
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.
Justin 对表级 DDL 锁提出了很好的观点。该锁将导致在表上执行 DDL 的会话等待 DML 会话提交，除非 DDL 类似于 CREATE INDEX，在这种情况下它将立即失败并显示 ORA-00054。
回答by Justin Cave
It depends what you mean by "lock".
这取决于您所说的“锁定”是什么意思。
For 99.9% of what people are likely to care about, Oracle will acquire a row-level lock when a row is modified. The row-level lock still allows readers to read the row (because of multi-version read consistency, writers never block readers and readers never do dirty reads).
对于人们可能关心的 99.9% 的事情，Oracle 会在修改行时获取行级锁。行级锁仍然允许读者读取行（因为多版本读一致性，作者永远不会阻塞读者，读者永远不会做脏读）。
If you poke around v$lock, you'll see that updating a row also takes out a lock on the table. But that lock only prevents another session from doing DDL on the table. Since you'd virtually never want to do DDL on an active table in the first place, that generally isn't something that would actually cause another session to wait for the lock.
如果你四处v$lock看看，你会看到更新一行也会取消对表的锁定。但该锁只会阻止另一个会话在表上执行 DDL。由于您实际上从一开始就不想在活动表上执行 DDL，因此这通常不会真正导致另一个会话等待锁定。
回答by Sumit Sengar
When a regular DML is executed (UPDATE/DELETE/INSERT,MERGE, and SELECT ... FOR UPDATE) oracle obtains 2 locks. Row level Lock (TX) - This obtains a lock on the particular row being touched and any other transaction attempting to modify the same row gets blocked, till the one already owning it finishes. Table Level Lock (TM) - When Row lock (TX) is obtained an additional Table lock is also obtained to prevent any DDL operations to occur while a DML is in progress.
当执行常规 DML 时（UPDATE/DELETE/INSERT、MERGE 和 SELECT ... FOR UPDATE）oracle 获得 2 个锁。行级锁 (TX) - 这会获得对被触摸的特定行的锁，并且任何其他尝试修改同一行的事务都会被阻塞，直到已经拥有它的事务完成。表级锁 (TM) - 当获得行锁 (TX) 时，还会获得一个额外的表锁，以防止在 DML 正在进行时发生任何 DDL 操作。
What matters is though in what mode the Table lock is obtained. A row share lock (RS), also called a subshare table lock (SS), indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. An SS lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.
重要的是在什么模式下获得表锁。行共享锁 (RS)，也称为子共享表锁 (SS)，表示持有表锁的事务已锁定表中的行并打算更新它们。SS 锁是限制最少的表锁模式，可为表提供最高程度的并发性。
A row exclusive lock (RX), also called a subexclusive table lock (SX), indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and SS locks for the same table.
行排他锁 (RX)，也称为子排他表锁 (SX)，表示持有该锁的事务已更新表行或发出 SELECT ... FOR UPDATE。SX 锁允许其他事务在同一表中同时查询、插入、更新、删除或锁定行。因此，SX 锁允许多个事务同时获取同一个表的 SX 和 SS 锁。
A share table lock (S) held by one transaction allows other transactions to query the table (without using SELECT ... FOR UPDATE) but allows updates only if a single transaction holds the share table lock. Multiple transactions may hold a share table lock concurrently, so holding this lock is not sufficient to ensure that a transaction can modify the table.
一个事务持有的共享表锁 (S) 允许其他事务查询表（不使用 SELECT ... FOR UPDATE），但仅当单个事务持有共享表锁时才允许更新。多个事务可能同时持有一个共享表锁，所以持有这个锁不足以保证一个事务可以修改表。
A share row exclusive table lock (SRX), also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR UPDATE) but not to update the table.
共享行排他表锁 (SRX)，也称为共享子排他表锁 (SSX)，比共享表锁限制更多。一次只有一个事务可以获取给定表上的 SSX 锁。事务持有的 SSX 锁允许其他事务查询表（SELECT ... FOR UPDATE 除外）但不允许更新表。
An exclusive table lock (X) is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. Only one transaction can obtain an X lock for a table.
排他表锁 (X) 是限制性最强的表锁模式，允许持有锁的事务对表进行排他写访问。只有一个事务可以获得一张表的 X 锁。
回答by orcl_slave
You should probably read the oracle concepts manual regarding locking. For standard DML operations (insert, update, delete, merge), oracle takes a shared DML (type TM) lock. This allows other DMLs on the table to occur concurrently (it is a share lock.) Rows that are modified by an update or delete DML operation and are not yet committed will have an exclusive row lock (type TX). Another DML operation in another session/transaction can operate on the table, but if it modifies the same row it will block until the holder of the row lock releases it by either committing or rolling back.
您可能应该阅读有关锁定的 oracle 概念手册。对于标准 DML 操作（插入、更新、删除、合并），oracle 采用共享 DML（TM 类型）锁。这允许表上的其他 DML 并发发生（它是共享锁）。由更新或删除 DML 操作修改但尚未提交的行将具有排他行锁（TX 类型）。另一个会话/事务中的另一个 DML 操作可以对表进行操作，但如果它修改同一行，它将阻塞，直到行锁的持有者通过提交或回滚来释放它。
Parallel DML operations and serial insert direct load operations take exclusive table locks.
并行 DML 操作和串行插入直接加载操作采用排他表锁。
