我们有一个从数据流中读取信息并将该信息更新到数据库中的应用程序。数据是 Google Drive 上发生的变化,这意味着影响相同对象的许多事件可能会非常接近地发生。
将此信息更新插入数据库时,我们遇到了死锁,日志中显示的内容如下。我已经重建并清理了查询以提高可读性:
ERROR: deadlock detected
DETAIL: Process 10586 waits for ShareLock on transaction 166892743; blocked by process 10597.
Process 10597 waits for ShareLock on transaction 166892741; blocked by process 10586.
Process 10586:
INSERT INTO documents
(version, source, source_id, ingestion_date)
VALUES
(0, 'googledrive', 'alpha', '2017-09-21T07:03:51.074Z'),
(0, 'googledrive', 'beta', '2017-09-21T07:03:51.074Z')
(0, 'googledrive', 'gamma', '2017-09-21T07:03:51.074Z'),
(0, 'googledrive', 'delta', '2017-09-21T07:03:51.074Z'),
(0, 'googledrive', 'epsilon', '2017-09-21T07:03:51.074Z'),
(0, 'googledrive', 'zeta', '2017-09-21T07:03:51.074Z')
ON CONFLICT (source, source_id)
DO UPDATE
SET
ingestion_date = EXCLUDED.ingestion_date,
version = documents.version + 1
RETURNING source_id, source, uid
Process 10597:
INSERT INTO documents
(version, source, source_id, ingestion_date)
VALUES
(0, 'googledrive', 'delta', '2017-09-21T07:03:51.167Z'),
(0, 'googledrive', 'gamma', '2017-09-21T07:03:51.167Z')
ON CONFLICT (source, source_id)
DO UPDATE
SET
ingestion_date = EXCLUDED.ingestion_date,
version = documents.version + 1
RETURNING source_id, source, uid
HINT: See server log for query details.
CONTEXT: while locking tuple (3908269,11) in relation "documents"
STATEMENT:
INSERT INTO documents
(version, source, source_id, ingestion_date)
VALUES
(0, 'googledrive', 'alpha', '2017-09-21T07:03:51.074Z'),
(0, 'googledrive', 'beta', '2017-09-21T07:03:51.074Z'),
(0, 'googledrive', 'gamma', '2017-09-21T07:03:51.074Z'),
(0, 'googledrive', 'delta', '2017-09-21T07:03:51.074Z'),
(0, 'googledrive', 'epsilon', '2017-09-21T07:03:51.074Z'),
(0, 'googledrive', 'zeta', '2017-09-21T07:03:51.074Z')
ON CONFLICT (source, source_id)
DO UPDATE
SET
ingestion_date = EXCLUDED.ingestion_date,
version = documents.version + 1
RETURNING source_id, source, uid
架构:
Column | Type | Modifiers
----------------+-----------------------------+-------------------------------------------------------------------
uid | uuid | not null default gen_random_uuid()
date_created | timestamp without time zone | not null default now()
sequence_id | bigint | not null default nextval('documents__sequence_id__seq'::regclass)
version | integer | not null default 0
source | text | not null
source_id | text | not null
ingestion_date | timestamp without time zone | not null
Indexes:
"documents__pkey" PRIMARY KEY, btree (uid)
"documents__sequence_id__unique" UNIQUE CONSTRAINT, btree (sequence_id)
"documents__source__source_id__deleted" UNIQUE, btree (source, source_id)
"documents__ingestion_date__idx" btree (ingestion_date)
"documents__source_id__source__idx" btree (source_id, source)
我怀疑问题类似于“第一个事务按顺序锁定具有 source_id alpha、beta、gamma 的行,同时第二个事务以相反的顺序锁定具有 source_id delta、gamma 的行,并且死锁发生在它们都锁定伽玛和德尔塔”,但是这里的时间非常紧迫!
这个问题的解决方案是什么?按 source_id 对值列表进行排序?