Race Condition in Postgres SQL using Spring data JpaRepository

Question

I am facing a wierd issue in my implementation where I am persisitng data to a PostgresSQL DB using Spring data JpaRepository

In my Entity class I have the below columns:

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private int id;
    
    @Column(name = "field1", nullable = false, length = 16)
    private String field1;

    @Column(name = "field2", nullable = false, length = 16)
    private String field2;

    @Column(name = "field3", nullable = false, length = 16)
    private String field3;

I initially avoided declaring the fields above as composite since there were many fields to be dealt with as composite keys. I thought the java code check would do the trick in all scenarios

So basically, I have to maintain the uniqueness of each row based on field1,field2 and field3. That was the basic requirement for which I had checks in my java code that if any entry exists in the DB for the combination of field1,field2 and field3 then I used to throw java exceptions

No two rows can have these values repeating. All was good until the application was tested under some errorneous business scenarios which would never happen in production but got run by mistake

Whats happening now is that if 2 requests are triggered at the exact same instance with the exact same 3 fields above (through a script) then they both enter into the Database since both get the entry check as false

Would declaring all of them as one composite key resolve the situation?

You can start by adding a unique constraint in postgres to be safe — Cyril G.
– Cyril G., Commented Sep 3, 2020 at 10:57

Cyril G. · Accepted Answer · 2020-09-03 11:01:21Z

1

You should define the unique constraint in your database in addition of JPA constraint.

@Entity
@Table(uniqueConstraints={
    @UniqueConstraint(columnNames = {"field1", "field2", "field3"})
}) 
public class MyEntity {
  ...
}

answered Sep 3, 2020 at 11:01

Cyril G.

2,0252 gold badges7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amol Kshirsagar Over a year ago

Sure. I will try this and get back. Thanks for your help

Amol Kshirsagar Over a year ago

I tried the suggestion above and it works in case of my Integration Tests where I am creating H2 in memory DB which uses the Entity definitions to create tables for the lifespan of the Integration tests. However, post deployment at runtime, the uniqueconstraint does not work in the actual tables. I assume I have to also change the liquibase script which I am using to create the actual tables. Only adding the above to the Java @Entity classes wont work. Please correct me if I am wrong

Collectives™ on Stack Overflow

Race Condition in Postgres SQL using Spring data JpaRepository

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related