Limit concurrent execution of an application process using database

Question

I was asked to implement an "access policy" to limit the amount of concurrent executions of a certaing process within an application (NOT a web application) which has direct connection to the database.

The application is running in several machines, and if more than a user tries to call the process, only one execution should be allowed at a given time, and the other must return an error message (NOT wait for the first execution to end).

Although I'm using Java/Postgres, is sort of a general question.

Given that I have the same application running in several machines, the simplest solution I can think of is implementing some sort of "database flag".

Something like checking whether the process is currently active:

SELECT Active FROM Process

If it's active, return a 'concurrent access policy error'. If not, activate it:

UPDATE Process SET Active = 'Y'

Once the execution is finished, simply update the active flag:

UPDATE Process SET Active = 'N'

However, I've encountered a major issue:

• If I don't use a DB transaction in order to change the active flag, and the application is killed, the the active flag will remain with the Y value forever.
• If I use a DB transaction, the first point is solved. However, the change of the active flag in a host (from N to Y) will only be visible after the commit, so the other hosts will never read active with Y value and therefore execute anyway.

Any ideas?

Answer 1

Don't bother with an active flag, instead simply lock a row based on the user ID. Keep that row locked in a dedicated transaction/connection. When the other user tries to lock the row (using SELECT ... FOR UPDATE) you'll get an error, and you can report it.

If the process holding the transaction fails, the lock is freed. If it quits, the lock is freed. If the DB is rebooted, the lock is freed.

Win all around.

Answer 2

Instead of having only a simple Y/N flag, put the timestamp at which active as been set, and have your client application set it regularly (say every minute, or every five minute). Then if a client crashes, other clients will have to wait just over that time limit, and then assume that client is dead and take over. This is just some kind of "heartbeat" mechanism to check the client that started the process is still alive.

A simpler solution would be to configure the database to only accept one connection at the time?