Locking an SQL Table and waiting for lock to be released - Java

Question

I want to execute a oracle stored procedure which creates table level lock , checks whether a record(with specific criteria) exists or not, and creates the record if it doesn't exist.

I want to use it in my microservice architecture. Since multiple threads could be simultaneously accessing the same data, i want to implement a mechanism where the db call waits for the lock to be released.

How can i achieve this ?

EDIT 1 : Explaining my goal in detail :-

Step 1 : Microservice Instance 1 locks the table, executes procedure/transaction

Step 2 : inside procedure/transaction , check if record exists, if yes return true and release lock, if not create record and release lock.

Step 3 : While step 2 is being performed, another Microservice Instance B tries to access the table, but since it is locked, instance B will wait till Instance A releases the lock.

step 4: After instance A releases the lock, instance B proceeds with its step 2.

There could be 5-7 instances.

Answer 1

等待大神答复