Distributed Locking and Error Tracking for Python with Postgres

with MachineLock("lock-name"):
<do something>
class CantLock(Exception):
pass
class MachineLock(object):
LOCK_DIRECTORY = "/srv/lock/" # adjust to taste
def __init__(self, file_name):
self.file_path = LOCK_DIRECTORY + file_name + ".lock"
def __enter__(self):
try:
self.fd = os.open(self.file_path,
os.O_CREAT | os.O_EXCL | os.O_RDWR)
except Exception as e:
raise CantLock(e)
def __exit__(self, a_type, value, traceback1):
os.close(self.fd)
os.unlink(self.file_path)
create table locks (
id UUID PRIMARY KEY DEFAULT pgcrypto.gen_random_uuid(),
named TEXT UNIQUE NOT NULL,
pid INTEGER NOT NULL,
ins TIMESTAMPTZ NOT NULL DEFAULT now()
);
create or replace function "acquire"(x text) returns void
volatile
security definer
as $dbvis$
DECLARE
lock_id UUID;
BEGIN
delete from locks
where tracking in (
select l.tracking
from locks as l
left join pg_stat_activity as p on p.pid = l.pid
where p.pid is null
);
insert into locks(named, pid)
values (x, pg_backend_pid()) returning tracking into lock_id;
return lock_id;END;
$dbvis$ LANGUAGE plpgsql
CREATE TABLE released (
id UUID PRIMARY KEY,
named TEXT NOT NULL,
locked TIMESTAMPTZ,
released TIMESTAMPTZ DEFAULT now(),
problem TEXT,
details TEXT
);
CREATE OR REPLACE FUNCTION "acquit"(lock_id UUID, error_type TEXT DEFAULT NULL::TEXT, error_details TEXT DEFAULT NULL::TEXT) RETURNS VOID VOLATILE SECURITY DEFINER as $dbvis$
BEGIN
INSERT INTO released (id, named, locked, problem, details)
SELECT id, named, ins, error_type, error_details
FROM locks WHERE id = lock_id
ON CONFLICT DO NOTHING;
DELETE FROM LOCKS WHERE id = lock_id;END; $dbvis$ LANGUAGE plpgsql
...
INSERT INTO released (id, locked, named, problem)
SELECT l.id, ins, l.named, 'disconnected'
FROM locks as l
LEFT JOIN pg_stat_activity as p on p.pid = l.pid
WHERE l.pid is NULL
ON CONFLICT DO NOTHING;
...

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store