I use the following tech stack:
- spring-boot-starter-data-jpa
- HikariCP for connection pooling
- Oracle DB
My actual code looks similar to this.
/// My trigger looks like this
CREATE OR REPLACE TRIGGER FILE_BRI
BEFORE INSERT
ON FILE
FOR EACH ROW
BEGIN
SELECT FILE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
///
public class FILE implements Serializable {
@Id
@SequenceGenerator(
name = "FILE_SEQ",
sequenceName = "FILE_SEQ",
allocationSize = 1)
@GeneratedValue(
strategy = GenerationType.SEQUENCE,
generator = "FILE_SEQ"
)
private long id;
}
public class ServiceA () {
@Transactional(propagation = REQUIRES_NEW, isolation = READ_COMMITTED)
public File insertFile() {
// Below line returns the inserted File object with ID as '58496'
return fileRepository.save(file)
}
@Transactional(propagation = REQUIRES_NEW, isolation = READ_COMMITTED)
public AccessControl insertAccessControl() {
// Below line results in 'SQLIntegrityConstraintViolationException' (full error at the bottom of this post)
return accessControlRepository.save(accessControlFile)
}
}
Public class FileProcessor() {
ServiceA serviceA;
public void someMethod() {
// insert the file and get the inserted record
File insertedFile = serviceA.insertFile(file);
// get the ID from the inserted file and make another insert into another table
serviceA.insertAccessControl(insertedFile.getId()); // inserted file ID is '58496'
}
}
This is my investigation:
When I verified the ID of the inserted record in the table "FILE" is '58497', however repository.save() returned a different value.
When I make the second insert on table "ACCESS_CONTROL_FILE" with FILE_ID as '58496' it results in the error below because the FILE with ID as '58496' does not exist.
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("DB_OWNER"."ACCESS_CONTROL_FILE"."FILE_ID")
I'm puzzled as to why would repository.save() return a different ID(i.e. ID=58496) than what is actually inserted(ID=58497) in the database!
I've investigated all options that I could find on the internet related to 'Propagation and Isolation'.
