Isn't this sort of coordination normally handled by the DBMS itself, rather than by applications running on the DBMS? I too can envisage ways to do it in the DBMS I'm familiar with, but without knowing more about your system (it presumably is using shared disks, so all the nodes see the same data; there are presumably locking protocols that prevent concurrent access to the data; is it a user process on the master node that periodically updates the lastTimestamp), it is going to be hard to help much. And, as Jamie Love pointed out, the DBMS should allow multiple processes to coordinate access to the relevant records - the main relevant record being the current master record.
[Edited: Maybe I was reading too much into it.
The single UPDATE statement has to do differential updates on two rows of the table, and must fail if only one of the two updates is possible. That is, it must both change the current master to be non-master and also change its own record so it is the master. One issue is how does the DBMS enforce the 'only one row may be the master' constraint. Let's assume that works and the statement as a whole will fail if there's an issue - as it should. Why do people so often omit the table name, even when they provide the column names? Oh well, the table name is hereinafter ClusterControl. Each node must know its own NodeID somehow; I've used {MyNodeID} to indicate where that appears in the SQL.
You need a separate heartbeat update:
UPDATE ClusterControl
SET lastTimestamp = CURRENT_TIMESTAMP
WHERE NodeID = {MyNodeID};
The "seize master status" update might be:
UPDATE ClusterControl
SET lastTimestamp = (CASE
WHEN NodeID = {MyNodeID} THEN CURRENT_TIMESTAMP
ELSE lastTimestamp END),
isMaster = (CASE
WHEN NodeID = {MyNodeId} THEN 'Y'
ELSE 'N' END)
WHERE (NodeID = {MyNodeID} AND isMaster = 'N') OR
(NodeID != {MyNodeID} AND
lastTimestamp < CURRENT_TIMESTAMP - INTERVAL '120' SECOND AND
isMaster = 'Y'
);
The theory behind the 'seize master status' update is (SET clause):
- the lastTimestamp field for the new master is set to the current timestamp, but the old master is unchanged.
- the isMaster field is changed to 'Y' for the new master and to 'N' for the old master.
The theory behind the WHERE clause is:
- Only change the record for the current node if it is not the current master or the record for the current master node when this node is not the current node and the timestamp is more than 120 seconds ("2 * X" in the question) old.
Since there is a (possibly mythical) constraint in place to ensure that only one row has the 'Y' flag, this should fail as required when the master is up to date.
Untested SQL!
]