SQL statement to specify the cluster master
I have clustered applications for which one of the nodes needs to be assigned as a master. Cluster nodes are tracked in a table with columns nodeID , isMaster , lastTimestamp .
Each node in the cluster will try to become master every X seconds. node can only become master if
- no other main nodes
- lastTimestamp for the current master node is older than 2 * X
When one of the above conditions is met
- the current master node isMaster should be cleared
- a new master node isMaster must be installed
- new master node lastTimestamp should be set to timestamp.
What is a single (portable) SQL statement to achieve the above without the ability for two or more nodes to become a master?
source to share
Isn't this coordination usually handled by the DBMS itself and not by the applications running in the DBMS? I can also envision ways to do this in a DBMS with which I am familiar, but not knowing more about your system (apparently it uses shared disks, so all nodes see the same data, presumably blocking protocols that prevent concurrent access to the data, it's a custom process on the main node that updates the lastTimestamp periodically) it would be very difficult to help. And as Jamie Love pointed out, the DBMS should allow multiple processes to coordinate access to the corresponding records - the master corresponding record is the current master record.
[Edited: I may have read too much.
A single UPDATE statement must perform differential updates on two rows of the table and must fail if only one of the two updates is possible. That is, he must both change the current master to non-master and change his own record to be the master. One problem is how the DBMS enforces the "only one row can be master" constraint. Suppose the work and approval in general fails if a problem arises - as it should. Why do people so often omit the table name even when they provide the column names? Well, the table name hereafter is ClusterControl. Each node needs to know its own NodeID in some way; I used {MyNodeID} to indicate where this appears in SQL.
You will need a separate heart rate update:
UPDATE ClusterControl
SET lastTimestamp = CURRENT_TIMESTAMP
WHERE NodeID = {MyNodeID};
The "grab master status" update could 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 "grab top status" update theory (SET clause):
- the lastTimestamp field for the new master is set to the current timestamp, but the old master is not changed.
- the isMaster field is changed to "Y" for the new master and "N" for the old master.
WHERE clause theory:
- Change the entry for the current node only if it is not the current master, or the entry for the current master node if 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) limitation to ensure that only one row has the "Y" flag, this should fail when required when the master is updated.
Uncharted SQL!
]
source to share