postgresql – Postgres中有两个DELETE查询死锁怎么办？

发布时间：2020-12-13 16:07:31 所属栏目：百科来源：网络整理

导读：在我们使用Postgres工作的许多事情中,我们将其用作某些类型的远程请求的缓存.我们的架构是： CREATE TABLE IF NOT EXISTS cache ( key VARCHAR(256) PRIMARY KEY,value TEXT NOT NULL,ttl TIMESTAMP DEFAULT NULL);CREATE INDEX IF NOT EXISTS idx_cache_ttl

在我们使用Postgres工作的许多事情中,我们将其用作某些类型的远程请求的缓存.我们的架构是：

CREATE TABLE IF NOT EXISTS cache (
    key VARCHAR(256) PRIMARY KEY,value TEXT NOT NULL,ttl TIMESTAMP DEFAULT NULL
);

CREATE INDEX IF NOT EXISTS idx_cache_ttl ON cache(ttl);

此表没有触发器或外键.更新通常是：

INSERT INTO cache (key,value,ttl)
VALUES ('Ethan is testing8393645','"hi6286166"',sec2ttl(300))
ON CONFLICT (key) DO UPDATE
SET value = '"hi6286166"',ttl = sec2ttl(300);

(其中sec2ttl定义为:)

CREATE OR REPLACE FUNCTION sec2ttl(seconds FLOAT)
RETURNS TIMESTAMP AS $$
BEGIN
    IF seconds IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN now() + (seconds || ' SECOND')::INTERVAL;
END;
$$LANGUAGE plpgsql;

查询缓存是在这样的事务中完成的：

BEGIN;
DELETE FROM cache WHERE ttl IS NOT NULL AND now() > ttl;
SELECT value FROM cache WHERE key = 'Ethan is testing6460437';
COMMIT;

关于这个设计有一些不喜欢的东西 – 缓存中发生的DELETE“读取”,cache.ttl上的索引没有提升,这使得它变得毫无用处,(编辑：ASC是默认的,谢谢wargre！)再加上我们将Postgres用作缓存的事实.但是所有这些都是可以接受的,除了我们已经开始在生产中遇到死锁,这往往看起来像这样：

ERROR: deadlock detected
DETAIL:  Process 12750 waits for ShareLock on transaction 632693475; blocked by process 10080.
Process 10080 waits for ShareLock on transaction 632693479; blocked by process 12750.
HINT:  See server log for query details.
CONTEXT:  while deleting tuple (426,1) in relation "cache"
 [SQL: 'DELETE FROM cache WHERE ttl IS NOT NULL AND now() > ttl;']

更彻底地调查日志表明两个事务都在执行此DELETE操作.

据我所知：

>我的事务处于READ COMMITTED隔离模式.
> ShareLocks被一个事务抓取,表示它想要改变另一个事务已经发生变异(即锁定)的行.
>根据EXPLAIN查询的输出,应按物理顺序的两个DELETE事务抓取ShareLocks.
>死锁表示两个查询以不同的顺序锁定行.

如果一切正确,那么某种同时的事务已经改变了行的物理顺序.我看到UPDATE可以将行移动到更早或更晚的物理位置,但在我的应用程序中,UPDATE总是从DELETE中删除行(因为它们总是扩展行的TTL).如果这些行之前是物理顺序,并且您删除了一行,那么您仍然可以保留物理顺序. DELETE也是如此.我们没有做任何VACUUM或您可能期望重新排序行的任何其他操作.

基于Avoiding PostgreSQL deadlocks when performing bulk update and delete operations,我尝试将DELETE查询更改为：

DELETE FROM cache c
USING (
   SELECT key
   FROM cache
   WHERE ttl IS NOT NULL AND now() > ttl
   ORDER BY ttl ASC
   FOR UPDATE
) del
WHERE del.key = c.key;

但是,我仍然可以在本地获得死锁.那么一般来说,两个DELETE查询怎么会死锁？是因为他们锁定了未定义的订单,如果是这样,我该如何强制执行特定订单？

解决方法

您应该忽略过期的缓存条目,因此您不会依赖于缓存过期的频繁删除操作：

SELECT value
FROM cache
WHERE
  key = 'Ethan is testing6460437'
  and (ttl is null or ttl<now());

还有另一个工作,定期选择键删除跳过已锁定的键,这必须强制一个明确定义的删除行的顺序,或者更好,跳过已锁定的更新行：

with delete_keys as (
  select key from cache
  where
    ttl is not null
    and now()>ttl
  for update skip locked
)
delete from cache
where key in (select key from delete_keys);

如果您无法定期安排此操作,则应每隔1000次运行您的选择查询时随机运行此清理,如下所示：

create or replace function delete_expired_cache()
returns void
language sql
as $$
  with delete_keys as (
    select key from cache
    where
      ttl is not null
      and now()>ttl
    for update skip locked
  )
  delete from cache
  where key in (select key from delete_keys);
$$;

SELECT value
FROM cache
WHERE
  key = 'Ethan is testing6460437'
  and (ttl is null or ttl<now());
select delete_expired_cache() where random()<0.001;

你应该避免写入,因为它们很昂贵.不要经常删除缓存.

此外,你应该使用带有时区类型(或简称timestamptz)的时间戳而不是简单的时间戳 – 特别是如果你不知道为什么 – 时间戳不是最常见的东西 – 责怪SQL标准.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!