Re^3: 'flock' with multiple users

If you already have a database, why bother with locks?

Oracle offers various methods for making sure that processes don't step on each others feet. Usually, you want to do stuff in a transaction.

For example an easy way could be to UPDATE all rows that you will be processing with your PID, and then select all those that you got:

update top 10 jobs
set processed_by = ?
where processed_by is null
order by job_id
;
select * from jobs where processed_by = ?
[download]

Comment on Re^3: 'flock' with multiple users Select or Download Code

Replies are listed 'Best First'.
Re^4: 'flock' with multiple users by Anonymous Monk on Sep 08, 2017 at 16:24 UTC
`$dbh->do("LOCK TABLES bar READ"); ... get next image $dbh->do("UNLOCK TABLES");` [download] I found this; is it preferable to flock? I have multiple users running the same program.	[reply] [d/l]
Re^5: 'flock' with multiple users by davido (Cardinal) on Sep 08, 2017 at 16:44 UTC
flock is a rather general-purpose solution for advisory locks on files. The locking technique can be used for many purposes, but the locks are advisory, and if you use a file lock to signal that a database is in use, that raises the potential for someone using that database to not be aware of an external file that should be checked first. The lock is kind of far away from the resource it is protecting. By using a database's built-in locks you gain efficiency, and proximity (making it easier to reason about or even know about). A database user outside of your application is more likely to be impeded from doing something destructive in the database if the lock comes from within the database, than if the lock is on a file that is possibly unknown to some consumers of the DB. Dave	[reply]
Re^4: 'flock' with multiple users by TheloniusMonk (Sexton) on Jul 30, 2020 at 13:24 UTC
Funnily enough, I found this thread on supersearch, to make sure I know what 'use' statements I need for flock, precisely because I don't want to use the database. I will keep looking for what I came for after adding my tuppence: The scenario is that I am building an HA postgres cluster with three nodes plus DR nodes. Postgres can be configured to replicate but robust HA needs extra work (in Perl seemed best) to check the status of all nodes in the cluster and if the current node has the wrong role, to either promote it to master or demote it to standby. But you don't want postgres to start at machine bootup in this case because you want to check the correct role first. So obviously flock rather than DB is essential - why lock at all? Because otherwise if the master goes down, all the standbys will try to assume master. So the failover program (guess what, I called it failover.pl) has to lock a file (there's a shared volume used for backup, all the nodes have access to) before running its cycle. Then if it detects no masters and promotes its node to master, the failover running on any other node, will be locked out until the count of masters goes from 0 to 1 and so the possibility of two nodes seeing no master and simultaneously promoting two masters is avoided. But using the database is not feasible in this scenario because failover.pl only does anything when the postgres master node is down.	[reply]
Re^5: 'flock' with multiple users by Corion (Patriarch) on Jul 30, 2020 at 13:28 UTC
Depending on the share mounting mechanism of your file system, this introduces another point where things can break. One of the more interesting problems back in the day were stale NFS mounts where file access did not work but also did not fail and timeouts were set up far too long. Make sure to at least test that you're not introducing another failure mode, or if you're introducing that, that it is well enough understood.	[reply]