How to begin transactions, In Spring boot JPA

Question

I have a composite unique constraint in the database and i wish to violate it inside the transaction but it will be upheld after i call commit.

It's pretty basic database transaction, but i am not aware how to achieve it using JPA.

I have already tried @org.springframework.transaction.annotation.Transactional on my @org.springframework.stereotype.Service class and also at the method level.

but the moment i call userRepository.save(userEntity) it throws

java.sql.SQLIntegrityConstraintViolationException: (conn=380) Duplicate entry ...'

controller

    @PostMapping("/save")
    @Transactional
    fun createUser(
        @RequestHeader("token", required = true) subject: String,
    ): ResponseEntity<*> {
        val id = extractId(subject)
        return userService.save(id)
    }

service

    @Transactional
    fun save(id: String): ResponseEntity<*> {
     val validatedUser =  getValidatedUser(id)
     return ResponseEntity<User>(validatedUser, HttpStatus.CREATED)
    }

   @Transactional
   fun getValidatedUser(id: String): User? {
      var list = listOf(User(id))
      val newUser = //some logic to generate user
      userRepository.saveAll(list.plus(newUser)) // <--- where error happens
   }

Answer 1

You are looking for DEFERRED constraints.

Simplified example (postgresql) that mimics your actions:

create table users (
  id int primary key,
  name varchar(255) unique
);
start transaction;
insert into users(id, name) values(1, 'max');
insert into users(id, name) values(2, 'max'); -- fails here, despite the transaction
insert into users(id, name) values(3, 'paul');
commit;

using deferrable and set constraints deferred:

create table users (
  id int primary key,
  name varchar(255) unique deferrable
);
start transaction;
set constraints all deferred;
insert into users(id, name) values(1, 'max');
insert into users(id, name) values(2, 'max'); -- no fail here
insert into users(id, name) values(3, 'paul');
commit; -- fails here ("deferred")

using deferrable initially deferred you can skip set constraints deferred:

create table users (
  id int primary key,
  name varchar(255) unique deferrable initially deferred
);
start transaction;
-- no 'set constraints deferred' necessary
insert into users(id, name) values(1, 'max');
insert into users(id, name) values(2, 'max'); -- no fail here
insert into users(id, name) values(3, 'paul');
update users set name = 'bob' where id = 2; -- fixes constraint violation
commit; -- succeeds

So, the easiest solution would be to add deferrable initially deferred to your constraint definition.

Answer 2

1. Your database has composite unique constraint. So you can't insert the same data.

2. This error means that the program inserted the same data at runtime.This is not allowed.

3. I think there may be a problem of duplicate ID here.

   var list = listOf(User(id))

4. Because of 3, two user objects with the same ID are created in the next step of the code.So there was an error inserting into database.

5. You can output user object at the console and find out if there is the same userId object.

Answer 3

// RollbackFor=Exception.class needs to be specified
@Transactional(rollbackFor = Exception.class)
fun save(id: String): ResponseEntity<*> {
 val validatedUser =  getValidatedUser(id)
 return ResponseEntity<User>(validatedUser, HttpStatus.CREATED)
}