Safe locking for multiple nodes and updates.
Yes, locking is obvious for a multi nodes environment, and I've been through the locking documentation but I'm left a bit confused, I'm not a database person normally.
I have 128 worker nodes on my network, I want each to look at a data record, update it (which may take some time, lots of calculations) then move on to the next record that isn't being worked on. The database is stored on an SSD connected to a Raspberry, all tables are Innodb, I'm using Python and the Python Connector.
Example >>>
No nodes are being worked on.
n1 checks record 1, it's open, it locks the record and starts working.
n2 checks record 1, it's locked.
n2 checks record 2, it's open, it locks the record and starts working.
n3 checks record 3, it's locked.
n1 finishes.
n1 checks record 2, it's locked. (Or being updated.)
n1 checks etc etc etc.
I can handle the "Or updated" with a simple field. But if I'm doing this, isn't this as good as any locking?
I set all 'updated' flags to 0, all nodes check this field, 1 to say we're working, 2 to say it's done. So each node skips any record with 1 or 2 as the flag and starts work when the field is 0.
However, I do have 128 nodes all hammering the server, is there any danger that 2 nodes could end up working on the same record?
This isn't mission critical to google, but it would mess things up somewhat if I had 2 nodes working on the same record as they're making node related calculations.
All input would be very welcome.
Many thanks.