Oracle - Oracle 如何管理事务特定的 DML 语句
想象一下我有一个简单的表：
Table Name: Table1
Columns: Col1 NUMBER (Primary Key)
Col2 NUMBER
如果我在没有提交的情况下将一条记录插入 Table1...
INSERT INTO Table1 (Col1, Col2) Values (100, 1234);
Oracle 如何知道下一条 INSERT 语句违反了 PK 约束，因为尚未向数据库提交任何内容。
INSERT INTO Table1 (Col1, Col2) Values (100, 5678);
Oracle 在何处/如何管理事务，以便在我尚未提交事务时就知道我违反了约束。
Imagine I have this simple table:
Table Name: Table1
Columns: Col1 NUMBER (Primary Key)
Col2 NUMBER
If I insert a record into Table1 with no commit...
INSERT INTO Table1 (Col1, Col2) Values (100, 1234);
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);
Where/how does Oracle manage the transactions so that it knows I'm violating the constraint when I haven't even committed the transaction yet.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
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).
这是因为唯一索引强制执行主键约束。即使对数据块的插入尚未提交，将重复条目添加到索引的尝试也无法成功，即使它是在另一个会话中完成的。
It's because of the unique index that enforces the primary key constraint. Even though the insert into the data block is not yet committed, the attempt to add the duplicate entry into the index cannot succeed, even if it's done in another session.
仅仅因为您尚未完成提交并不意味着第一条记录尚未发送到服务器。 Oracle 已经知道您插入第一条记录的意图。当您插入第二条记录时，Oracle 确信如果不违反约束，这将不可能成功，因此它会拒绝。
如果另一个用户要插入第二条记录，并且第一条记录尚未提交，Oracle 将接受它。如果第二个用户在您之前提交，您的提交将会失败。
Just because you haven't done a commit yet does not mean the first record hasn't been sent to the server. Oracle already knows about you intentions to insert the first record. When you insert the second record Oracle knows for sure there is no way this will ever succeed without a constraint violation so it refuses.
If another user were to insert the second record, Oracle will accept it if the first record has not been committed yet. If the second user commits before you do, your commit will fail.
除非“延迟”特定约束，否则将在语句执行时对其进行检查。如果延期，将在交易结束时进行检查。我假设您没有推迟您的主密钥，这就是您在提交之前就收到违规的原因。
具体如何完成是一个实现细节，不同的数据库系统甚至同一系统的版本之间可能会有所不同。应用程序开发人员可能不应该对此做出太多假设。就 Oracle 而言，出于性能原因，PRIMARY KEY 使用底层索引，而有些系统甚至不需要索引（如果您可以忍受相应的性能损失）。
顺便说一句，可延迟的 Oracle PRIMARY KEY 约束依赖于非唯一索引（与使用唯一索引的不可延迟的 PRIMARY KEY 相比）。
--- 编辑 ---
我刚刚意识到你甚至没有提交第一个插入。我认为贾斯汀的回答很好地解释了锁争用本质上是如何导致其中一个事务停滞的。
Unless a particular constraint is "deferred", it will be checked at the point of the statement execution. If it is deferred, it will be checked at the end of the transaction. I'm assuming you did not defer your PRIMARY KEY and that's why you get a violation even before you commit.
How this is really done is an implementation detail and may vary between different database systems and even versions of the same system. The application developer should probably not make too many assumptions about it. In Oracle's case, PRIMARY KEY uses the underlying index for performance reasons, while there are systems out there that do not even require an index (if you can live with the corresponding performance hit).
BTW, a deferrable Oracle PRIMARY KEY constraint relies on a non-unique index (vs non-deferrable PRIMARY KEY that uses a unique index).
--- EDIT ---
I just realized you didn't even commit the first INSERT. I think Justin's answer explains nicely how what is essentially a lock contention causes one of the transactions to stall.