Oracle - How does Oracle manage transaction specific DML statements?

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).

– contactmatt Aug 15 at 18:47 @contactmatt - In the index structure itself. Just like there is a lock flag in each row in a block of the table so that Oracle doesn't need a separate lock manager, there is a similar structure in the index block. – Justin Cave Aug 15 at 18:55 +1.

Well explained. I always had this question to ask someone: "where does this information get written (Index entries, table entries)? In Redo/Undo log?

" – Guru Aug 15 at 18:55 One more question, when I was testing my question with the insert statements, they were both within the same transaction and session. You answered the question by illustrating an example with two different sessions. What about the same session?

– contactmatt Aug 16 at 16:49 1 @contactmatt - It's even easier when you're dealing with multiple statements in the same session since the changes made by prior statements are fully visible within the same transaction. Your second insert statement simply visits the appropriate block in the index (which the transaction already has the lock for), looks at the data already in the index, and throws the exception. The second statement has no idea whether the duplicate row was inserted in the same transaction or in a transaction that was committed months ago.

– Justin Cave Aug 16 at 16:57.

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.

I did a little test and indeed: the second transaction will wait (locked) until you either commit or rollback the first session. – Jeff Aug 15 at 18:49.

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.

Not quite - the second session hangs, pending the outcome of the first transaction. Try it. – DCookie Aug 15 at 18:50 what do you mean by "so it refuses."?

Is the error message you refer to? – Guru Aug 15 at 18:56 I did indeed try (see my comment to another answer) and you are correct: the second session hangs until the first is committed/rollbacked. With refuse I expected a constraint violation.

– Jeff Aug 15 at 19:07.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.