postgresql - concurrency in spring jpa with postgres - Stack Overflow

admin管理员组

文章数量:1023838

I am using jpa in my spring boot application (postgres) and have some entity called car. In the beginning cars are created without assignedUser field in database.

Then trough certain process (post request) users can assign cars to themselves, where they will get the first car that has assignedUser field null and more recent creationTimestamp and status not DELETED. The method finds the first car in these conditions and assigns it to the user that made the request. At this point field assignedUser is populated.

In order to prevent concurrent updates and make sure that if many users try to request cars at the same time (they should all get different cars) I have implemented a optimistic locking mechanism

@Entity
public class Car{

    @Id
    private Long id;
    private String status;
    private String name;
    private double price;
    private String assignedUser;
    private Instant creationTimestamp;

    @Version
    private int version; // Version field for optimistic locking

    // Getters and setters
}

The ideia here is if I get an OptimisticLockException it means the record was updated in between by another process and I will try the assign operation again.

My problem is that at any point in time users can execute a clear operation, where all cars that have assignedUser null are updated to status DELETED. I might have thousands of cars in this scenario so I will update in batches (I cannot use optimistic locking here)

How can I prevent following scenarios when clear operation and assign requests happen at the same time?

1. clear operation from modifing a car that was just assigned to a user
2. assign operation from assigning a car that was just cleared (status = DELETED) ?

I am using jpa in my spring boot application (postgres) and have some entity called car. In the beginning cars are created without assignedUser field in database.

Then trough certain process (post request) users can assign cars to themselves, where they will get the first car that has assignedUser field null and more recent creationTimestamp and status not DELETED. The method finds the first car in these conditions and assigns it to the user that made the request. At this point field assignedUser is populated.

In order to prevent concurrent updates and make sure that if many users try to request cars at the same time (they should all get different cars) I have implemented a optimistic locking mechanism

@Entity
public class Car{

    @Id
    private Long id;
    private String status;
    private String name;
    private double price;
    private String assignedUser;
    private Instant creationTimestamp;

    @Version
    private int version; // Version field for optimistic locking

    // Getters and setters
}

The ideia here is if I get an OptimisticLockException it means the record was updated in between by another process and I will try the assign operation again.

My problem is that at any point in time users can execute a clear operation, where all cars that have assignedUser null are updated to status DELETED. I might have thousands of cars in this scenario so I will update in batches (I cannot use optimistic locking here)

How can I prevent following scenarios when clear operation and assign requests happen at the same time?

1. clear operation from modifing a car that was just assigned to a user
2. assign operation from assigning a car that was just cleared (status = DELETED) ?

本文标签： postgresqlconcurrency in spring jpa with postgresStack Overflow