Q: How to begin transactions, In Spring boot JPA 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
   }



A: 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.

A: *

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


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


*I think there may be a problem of duplicate ID here.
var list = listOf(User(id))


*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.


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

A: // 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)
}

