Database Record Lock

Question

I have a server application and a database. Several server instances can be started simultaneously, but all the data comes from the same database (on some servers it is postgresql, in other cases ms sql server).

My application is running a process that can take several hours. I need to make sure that this process is performed only one at a time. If one server is processing, no other server instance can be processed until the first is completed.

A process depends on a single table (let's call it "ProcessTable"). What I do, before any server starts the clock process, I set the boolean flag in the ProcessTable, which indicates that this record is “locked” and is being processed (not all records in this table are processed / locked, so I need mark each entry that is necessary for the process). Therefore, when the next server instance arrives while the previous instance is still being processed, it sees the logical flags and throws an exception.

The problem is that two server instances can be activated almost simultaneously, and when both check the ProcessTable, no flags can be set, but both servers are actually in the process of “setting” flags, but since the transaction has not yet been completed for any process , no process will see a lock being executed by another process. This is due to the fact that the locking mechanism itself can take several seconds, therefore there is such a window of possibilities in which two servers can be processed simultaneously.

It looks like I need one entry in my "Settings" table, which should store the logical flag "LockInProgress". Therefore, before even the server can lock the necessary entries in ProcessTable, you must first make sure that it has full rights to lock by checking the "LockInProgress" column in the "Settings" table.

So my question is: how can I prevent the two servers from both LockInProgress columns from changing in the settings table at the same time ... or am I wrong about this?

Note that I need to support both postgresql and ms sql servers, as some servers use one database and some servers use another.

Thanks in advance...

Answer 1

How about first committing a record to a record and then updating the record to show "locked." This will avoid the second instance in order to get the lock successfully, and thus updating the record failed.

The point is to fix and update as one atomic step.

Answer 2

Make a stored procedure that issues a lock and run it under "serializable" isolation. This ensures that one and only one process can access the resource at any given time.

Please note that this means that the second process trying to get into the lock will be blocked until the first process releases it. Also, if you need to get multiple locks this way, make sure the design of the process ensures that locks are received and released in the same order. This will avoid deadlock situations where two processes are storing resources, waiting for each other to release locks.

If you cannot handle your other processes, blocking this may be easier to implement and more reliable than trying to implement the semantics of “test and set”.

Answer 3

I thought about it, and I think this is the easiest way to do things; I simply execute the command as follows:

update settings set settingsValue = '333' where settingsKey = 'ProcessLock' and settingsValue = '0' 

'333' will be the unique value that each server process receives (based on date / time, server name, + random value, etc.).

If no other process has locked the table, then the value of settingsValue will be = 0, and this statement will adjust settingsValue.

If another process has already locked the table, then this statement becomes inoperative, and nothing will change.

Then I immediately transfer the transaction.

Finally, I request a table for the settingsValue parameter, and if this is the correct value, then our lock succeeded, and we continue, otherwise an exception is raised, etc. When we finish with the lock, we reset the value back to 0.

Since I am using the transaction mode SERIALIZATION, I do not see this causing any problems ... please correct me if I am wrong.