Oracle-Oracle如何管理特定于事务的DML语句 [英] Oracle - How does Oracle manage transaction specific DML statements

问题描述

想象一下我有这个简单的表:

Imagine I have this simple table:

Table Name: Table1
Columns:    Col1 NUMBER (Primary Key)
            Col2 NUMBER

如果我在没有提交的情况下将记录插入到Table1中...

If I insert a record into Table1 with no commit...

INSERT INTO Table1 (Col1, Col2) Values (100, 1234);

Oracle怎么知道下一条INSERT语句违反了PK约束，因为尚未将任何内容提交给数据库.

How does Oracle know that this next INSERT statement violates the PK constraint, since nothing has yet been committed to the database yet.

INSERT INTO Table1 (Col1, Col2) Values (100, 5678);

Oracle在哪里/如何管理事务，以便在我什至尚未提交事务时就知道我违反了约束.

推荐答案

Oracle创建一个索引来强制执行主键约束(默认情况下为唯一索引).当会话A插入第一行时，索引结构将更新，但不会提交更改.当会话B尝试插入第二行时，索引维护操作会注意到索引中已经存在带有该特定键的未决条目.会话B无法获取保护共享索引结构的闩锁，因此它将阻塞，直到会话A的事务完成为止.届时，会话B要么能够获取锁存器并对其进行修改(因为A回滚了)，要么它将注意到另一个条目已经提交，并且将抛出唯一的约束冲突(因为A提交了) ).

Oracle creates an index to enforce the primary key constraint (a unique index by default). When Session A inserts the first row, the index structure is updated but the change is not committed. When Session B tries to insert the second row, the index maintenance operation notes that there is already a pending entry in the index with that particular key. Session B cannot acquire the latch that protects the shared index structure so it will block until Session A's transaction completes. At that point, Session B will either be able to acquire the latch and make its own modification to the index (because A rolled back) or it will note that the other entry has been committed and will throw a unique constraint violation (because A committed).

这篇关于Oracle-Oracle如何管理特定于事务的DML语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！