How to do concurrency control in Hibernate for INSERT in the database

Question

I’m having a competition control problem in entering data in my web application.

Context: I have 3 tables (ex: X, Y and Z) that record hospitalizations of patients (already registered). A patient cannot have more than one ACTIVE hospitalization simultaneously.

Currently, my application only checks if there is already some active hospitalization for that patient before starting the transaction that makes the Inserts in tables X, Y and Z.

However this verification does not work when two or more users try to admit two patients at the same time.

Currently the treatment is done so:

1 - verifica se o paciente está internado (SELECT na tabela X);
Se não:
  inicia transação A;
    2 - INSERT na tabela X;
    3 - INSERT na tabela Y;
    4 - INSERT na tabela Z;
  finaliza transação A;

As I said earlier, check-in 1 occurs to prevent patients being admitted more than once at the same time. However, if two (or more) users try to admit the same patient at the same time, the 1 check-up does not work.

What I thought of solution:

I thought of something that involves blocking SELECT in 1, so that it will only be executed when the A transaction is completed. In this case, when executed, SELECT would identify that the patient is already registered.

I would like to address this problem using LOCK in the database. I use Postgresql and, for what I studied, I would need to use ACCESS EXCLUSIVE (only that blocks SELECT)

How to do this using Hibernate?

Note: I have already analyzed and cannot deal with constraints in the database

Answer 1

As Dherik commented. Having the possibility to create a Constraint I think is the best option.

something like that:

ALTER TABLE pacienteInternado ADD CONSTRAINT CPFUNICO UNIQUE (cpf);

The error that will be returned in case of duplicate registration attempt:

Caused by: java.sql.Sqlintegrityconstraintviolationexception: Duplicate entry 'string-00000000000' for key 'patientInternado.CPFUNICO'

Case the table patient Have any relationship with the patient table, you should leave the patient’s FK single. I am speculating your tables, in case it is necessary to share here for us to see.

Answer 2

Have you ever thought of using the optimistic lock strategy ? It is a feature of Hibernate that creates a column in the database representing the version of that record.

If you are using a Web system (done in Spring for example) you can send this version as an Hidden field in the form. Then when a user fires an action (say hospitalizing the patient) this version is confronted with the version that is persisted in the database. If there is divergence, the transaction is not executed. This is useful to prevent for example that two users try to change a record at the same time.

Imagine the following: Two users open the same patient file in their system, both receive in the data form the version of the record (say 1.0). User A, changes patient information to "inpatient", the database updates this value and raises the version to say 1.1. However user B does not know this and is still with the "old" form on his screen with version value of the record still 1.0. If he has the same patient admitted as well, the field of the version present in his form will differ from the field of the most recent version in the database and the transaction will be canceled. This way you can make the proper error treatment and inform the user that that data was outdated.

This mechanism in Hibernate is reasonably easy to use by just creating a column in the target entity of type int, Integer, long, Long, short, Short or java.sql.Timestamp and note with @Version. Hibernate does version management for you.

I’m going to leave here the reference where I got a very interesting example that might help you to solve this problem.

https://www.baeldung.com/jpa-optimistic-locking

I hope I can help you.