I have a competition control problem in inserting data into my web application.
Context: I have 3 tables (ex: X, Y and Z) that register hospitalizations of patients (already registered). A patient can not have more than one ACTIVE hospitalization simultaneously.
Currently, my application only checks if there is already any active hospitalization for that patient before starting the transaction that does the INSERTs on the X, Y and Z tables.
However, this check does not work when two or more users try to commit two patients at a time.
Currently the treatment is done like this:
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, checking in 1 occurs to prevent patients being hospitalized more than once at the same time. However, if two (or more) users attempt to commit the same patient at the same time, the check for 1 does not work.
What I thought of solution:
I thought of something that involves locking SELECT to 1, so that it only runs when transaction A is terminated. In this case, when executed, the SELECT would identify that the patient is already registered.
I would like to handle this problem by using LOCK in the database. I use PostgreSQL and, as I studied, I would need to use ACCESS EXCLUSIVE (single SELECT blocking)
How to do this using Hibernate?
Note: I have already analyzed and can not handle constraints in the database