如何为 PostgreSQL 编写自己的全局锁定/解锁函数
我有 postgresql (在 perlu 中)函数 getTravelTime(integer, timestamp),它尝试选择指定 ID 和时间戳的数据。如果没有数据或者数据旧,它会从外部服务器下载它们(下载时间~300ms)。
多个进程使用该数据库和该函数。当两个进程找不到数据并下载它们并尝试插入到travel_time表时会出现错误(id和时间戳对必须是唯一的)。我想到了锁。锁定整个表将阻止所有进程并只允许一个进程继续进行。我只需要锁定 id 和时间戳。 pg_advisory_lock 似乎只锁定“当前会话”。但我的进程使用自己的会话。
我尝试编写自己的锁定/解锁函数。我做对了吗?我使用主动等待,如何省略这个?也许有一种方法可以使用 pg_advisory_lock() 作为全局锁?
我的代码:
CREATE TABLE travel_time_locks (
id_key integer NOT NULL,
time_key timestamp without time zone NOT NULL,
UNIQUE (id_key, time_key)
);
------------
-- Function: mylock(integer, timestamp)
DROP FUNCTION IF EXISTS mylock(integer, timestamp) CASCADE;
-- Usage: SELECT mylock(1, '2010-03-28T19:45');
-- function tries to do a global lock similar to pg_advisory_lock(key, key)
CREATE OR REPLACE FUNCTION mylock(id_input integer, time_input timestamp)
RETURNS void AS
$BODY$
DECLARE
rows int;
BEGIN
LOOP
BEGIN
-- active waiting here !!!! :(
INSERT INTO travel_time_locks (id_key, time_key) VALUES (id_input, time_input);
EXCEPTION WHEN unique_violation THEN
CONTINUE;
END;
EXIT;
END LOOP;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE
COST 1;
------------
-- Function: myunlock(integer, timestamp)
DROP FUNCTION IF EXISTS myunlock(integer, timestamp) CASCADE;
-- Usage: SELECT myunlock(1, '2010-03-28T19:45');
-- function tries to do a global unlock similar to pg_advisory_unlock(key, key)
CREATE OR REPLACE FUNCTION myunlock(id_input integer, time_input timestamp)
RETURNS integer AS
$BODY$
DECLARE
BEGIN
DELETE FROM ONLY travel_time_locks WHERE id_key=id_input AND time_key=time_input;
RETURN 1;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE
COST 1;
I have postgresql (in perlu) function getTravelTime(integer, timestamp), which tries to select data for specified ID and timestamp. If there are no data or if data is old, it downloads them from external server (downloading time ~300ms).
Multiple process use this database and this function. There is an error when two process do not find data and download them and try to do an insert to travel_time table (id and timestamp pair have to be unique). I thought about locks. Locking whole table would block all processes and allow only one to proceed. I need to lock only on id and timestamp. pg_advisory_lock seems to lock only in "current session". But my processes uses their own sessions.
I tried to write my own lock/unlock functions. Am I doing it right? I use active waiting, how can I omit this? Maybe there is a way to use pg_advisory_lock() as global lock?
My code:
CREATE TABLE travel_time_locks (
id_key integer NOT NULL,
time_key timestamp without time zone NOT NULL,
UNIQUE (id_key, time_key)
);
------------
-- Function: mylock(integer, timestamp)
DROP FUNCTION IF EXISTS mylock(integer, timestamp) CASCADE;
-- Usage: SELECT mylock(1, '2010-03-28T19:45');
-- function tries to do a global lock similar to pg_advisory_lock(key, key)
CREATE OR REPLACE FUNCTION mylock(id_input integer, time_input timestamp)
RETURNS void AS
$BODY$
DECLARE
rows int;
BEGIN
LOOP
BEGIN
-- active waiting here !!!! :(
INSERT INTO travel_time_locks (id_key, time_key) VALUES (id_input, time_input);
EXCEPTION WHEN unique_violation THEN
CONTINUE;
END;
EXIT;
END LOOP;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE
COST 1;
------------
-- Function: myunlock(integer, timestamp)
DROP FUNCTION IF EXISTS myunlock(integer, timestamp) CASCADE;
-- Usage: SELECT myunlock(1, '2010-03-28T19:45');
-- function tries to do a global unlock similar to pg_advisory_unlock(key, key)
CREATE OR REPLACE FUNCTION myunlock(id_input integer, time_input timestamp)
RETURNS integer AS
$BODY$
DECLARE
BEGIN
DELETE FROM ONLY travel_time_locks WHERE id_key=id_input AND time_key=time_input;
RETURN 1;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE
COST 1;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
使用 pg_advisory_lock()。这正是您所需要的。
Use
pg_advisory_lock()
. It is exactly it what you need.经过进一步测试,我发现“当前会话”意味着 PostgreSQL 后端的整个实例,而不仅仅是单个连接。
我的解决方案是:
在我的
(+解锁)。getTravelTime
函数中执行pg_advisory_lock(id, extract(epoch from my_time)::int);After further testing I found that "current session" means whole instance of PostgreSQL backend, not just a single connection.
My solution is :
PERFORM pg_advisory_lock(id, extract(epoch from my_time)::int );
(+ unlock) inside mygetTravelTime
function.