Multi server application reading data from same table

Question

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.

Answer 1

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

Answer 2

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