CODEWITHSUNDEEP
databaseejbcluster-computingappserverejb-timer
Saket Chaudhari
Saket Chaudhari

Reputation: 81

Multi server application reading data from same table

I have an application running on two different server instances. Application has a singleton EJB which has timer set to run after every one minute. The method in EJB reads records from table deletes it and publishes it over the queue (all part of one transaction). Once both servers started it tries to read the record from table simultaneously.

My question is how to allow only one application to read records until the transaction is over.

Upvotes: 1

Views: 508

Answers (2)

Saket Chaudhari
Saket Chaudhari

Reputation: 81

Thanks for the answer!

Here is what I end up doing. I am using jboss application server and I implemented HASingleton feature which allows only one instance of singleton bean to run at a time on one server in cluster environment.

https://docs.jboss.org/author/display/WFLY10/HA+Singleton+Features

Upvotes: 0

user3714601
user3714601

Reputation: 1281

You can use select for update:

select * from table for update

This will block the selected records for the time of transaction, i.e. the same queries issued by other server will wait until transaction is committed.

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

Upvotes: 1

Related Questions