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

user_13548254 0
Viewed 484

I want to execute a function which locks on a table / row ? , 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 to handle concurrent requests trying to modify the same data.

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( i.e concurrent request), 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 concurrent requests .

spring-transactions transactions spring sql
1 Answers

Sorry if I am missing something about the goal you want to achieve, but instead of trying performing this kind of distributed locking mechanism, maybe an alternative solution will be using some type of message broker to which the different microservices will publish messages, with a single consumer that will handle them taking into account the possible duplicates. It is true that with certain restrictions, but services like Kafka or RabbitMQ, for example, will guarantee message ordering.

You can make the publishers of this first write queue consumers of another one in which the aforementioned single consumer will publish the results of the operation performed against the backend, if your microservices need this feedback.

I think this setup will highly decouple your different services without the risk of using the suggested locks.

user_139424480
Related