In my java application, I'm running a Transaction I where an insert is done on table A.
Transaction I also calls a method which uses a separate connection to have a separate Transaction II in order to create a record in table B. (This table is used to keep track of internal ID generation and it is important, that whatever happens to Transaction I, that Transaction II is commited).
Now, because of things, table B actually had a reference to the PK of table A (which is actually the problem).
However since the record in A is just being created in the "outer" Transaction I, it is not yet commited, and the value to insert as reference in B (pointing to A) thus does not exist in Transaction II. (Or in other words, I would suspect that this value is not visible for Transaction II since it isn't commited)
Now in this situation I'd expect to get a FK constraint violation error, but I am getting a
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction.
Testing the same scenario on a postgresql db raises the expected:
org.postgresql.util.PSQLException: ERROR: insert or update on table "B" violates foreign key constraint
I suspect that the mysql database knows about the insert of the first transaction and actually tries to wait in the second transaction for the first to finish to actually be able to insert the record of the second transaction?
Of course that can't ever happen since the first transaction also waits for the second to complete. So the "inner" transaction aborts with the timeout.
Is there a reason the two db systems behave differently, or do I miss something?
Update: I think this has to do with the different default isolation levels of mysql (REPEATABLE_READ) and postgresql (READ_COMMITED)
You cannot have two separate connections and expect it to work. You should write a code where you can pass the current connection object to the method so that the connection is re used in that method which will not complain about fk.
