Parent Key Not Found - Spring Transaction Management

Question

I am working with your typical three layered application that has a presentation layer, a business layer, and a DAO layer. It is a Java web application that uses Spring MVC, Spring Security, and Spring's transaction management from the business layer down. It is communicating with an Oracle 10g database.

I have a business layer method called createDepartment. Inside this method I make two calls to the DAO layer to create the department and create a position for that department. Each time I do this, I get a parent key not found exception when an attempt is made to create the position. If I move createPosition up to the presentation layer (outside of the transaction management), it works.

I can duplicate this in Oracle's SQL Developer. If I create a department, I can't see that department until I close the connection and get a new one, so I can see why the position cannot be created. What makes this different than most SQL inserts is that the application is using Oracle's label security feature and all departments have a label assigned to them. So when a department is created, a new label is created.

How can I make the department visible in the connection that it is using to create it?

Answer 1

It sounds to me like the DAO call to create the "position" is actually operating on a different connection and/or transaction to the DAO call that creates the department. This would be consistent with the observations:

• When the department is created inside your transaction, and then the attempt is made to create the position in a different transaction, the transaction that created the department has yet to be committed, and so it fails.
• When the department is created outside of the DAO transaction, it gets committed, and then the position gets created OK.

Make sure that you only have one transaction boundary in your DAO, and make sure the call to create the position is not inadvertantly creating a new transaction or connection. The Spring debyug-level logging is very useful for tracking this down, it's very verbose about every connection get/release and tx start/end boundary.