在Snowflake中的单例模式实现?

Question

我们需要实现一些单例模式来确保一个存储过程不能同时运行多次。

由于我无法看到这个功能，我考虑通过一个"锁"来实现它。表格我们在一个"批次"中。环境，等待几秒钟是没有问题的。

SHARED.LOCK(LOCK_NAME           STRING NOT NULL PRIMARY KEY
,SESSION_ID          STRING NOT NULL
,ACQUIRED_AT         TIMESTAMP_NTZ
)

• LOCK_NAME强制为大写并用作主键
• SESSION_ID是当前会话
• ACQUIRED_AT只是有用的信息

然后创建一个存储过程来"获取";锁$LOCK_NAME试图用自己的会话id更新锁记录，只要它没有被"锁定";已经

UPDATE SHARED.LOCK
SET LOAD_ID = $LOAD_ID
,SESSION_ID = CURRENT_SESSION()
,ACQUIRED_AT = CURRENT_TIMESTAMP()
WHERE LOCK_NAME = $LOCK_NAME
AND SESSION_ID IS NULL;

为了避免雪花乐观锁定的副作用，我将确保这个存储过程不是作为显式事务的一部分调用的。

然后检查我是否成功"获取"了";这把锁

SELECT 1
FROM SHARED.LOCK
WHERE LOCK_NAME = $LOCK_NAME
AND LOAD_ID = $SESSION_ID;

如果我得到一条记录，那么我就有了锁。

否则，我可以等待X秒，稍后再试一次，最多重试一定次数。

一旦我完成了，我可以用一个简单的Update语句

释放锁

UPDATE SHARED.LOCK
SET SESSION_ID = NULL
,ACQUIRED_AT = NULL
WHERE LOCK_NAME = $LOCK_NAME
AND SESSION_ID = $SESSION_ID;

当然，我们还必须对锁在一定时间内未被释放或被一个不再活跃的会话锁定等问题做一些处理。

我认为这应该可以…但是也许在Snowflake中有一种更简单的方法来实现单例?

有更好的主意吗?

Answer 1

根据需要，如果存储过程要按计划运行，可以使用内置重叠保护的TASK:

CREATE OR REPLACE TASK my_task
WAREHOUSE = compute_wh
SCHEDULE = '1 minute'
ALLOW_OVERLAPPING_EXECUTION = FALSE
AS  
CALL procedure_call();

CREATE TASK - ALLOW_OVERLAPPING_EXECUTION:

ALLOW_OVERLAPPING_EXECUTION = TRUE | FALSE

指定是否允许任务树的多个实例并发运行

FALSE确保只有一个实例

在一个特定的任务树中被允许同时运行。

演示:

---

CREATE TABLE log(id INT NOT NULL IDENTITY(1,1), d TIMESTAMP);
CREATE OR REPLACE procedure insert_log()
returns string
language javascript
execute as owner
as
$$
snowflake.execute ({sqlText:  "INSERT INTO log (d) SELECT CURRENT_TIMESTAMP()"});
snowflake.execute ({sqlText:  "CALL SYSTEM$WAIT(2, 'MINUTES')"});
return "Succeeded.";   
$$
;
ALTER TASK my_task RESUME;
SELECT * FROM log;