select for update doesn't wait on an uncommitted insert?
912732Jan 23 2012 — edited Jan 24 2012Hi folks. This is my first post here, I tried to search for similar problems, but haven't found any. I will greatly appreciate any help You can provide. Here's the deal: imagine two tables: table A and table B, both with primary key made up of a single, auto-incremented long integer field. Additionally, table B references A with a foreign key. In our application (we're talking Java + Hibernate + Oracle 10g if I'm not wrong here) we have observed the following behavior:
One transaction comes in and saves a row in A, let's say with primary key equal to 1. This transaction then moves on to do other stuff. Among other things, the transaction (handled by Spring on a transaction-per-http-request basis) it sends an asynchronous request to a JMS queue containing the said primary key 1. An asynchronous response comes in before the commit of that transaction, and in another thread inserts a line into B with foreign key equal to 1. To our suprise, the second transaction waits for the commit of the first one. I was quite surprised not to see it explode with some awful hibernate stacktrace.
We were expecting similar behavior in another scenario, that was subject to a recent defect. Namely - first transaction writes a line into A, and before it gets committed, another transaction in another thread comes in to do a select ... for update on the line, which has been inserted, but not yet committed. However, here, the select returns no results and our code goes on to create the same line again in the second transaction. The insert in the second transaction waits for the first to commit, but only to explode saying that uniqueness constraint has been violated. Could someone please explain to me what are we doing wrong?
We have tried several different possibilities using not even our java code, but a tool code toad for oracle. It seems that if we have an insert waiting for a commit, another insert of an identical line will wait, but fail with uniqueness constraint violation after the commit of the first transaction. However, a delete statement will wait also, but execute gently after the commit of the 1st transaction. And select for update will not. Why? It is my understanding of the oracle row and table locking, that an insert statement should automatically obtain a row-exclusive lock on the inserted row. So the select for update, which is trying to lock the same row, should wait, the same way as a delete or update, no? Please help, any tips on how to solve this situation will be greatly appreciated.