更新:下面可能有解决方案
我有一大堆配置文件,其中包含键值对,我正在尝试将它们推入数据库中。许多键和值在配置文件之间重复,因此我正在使用3个表存储数据。一个用于所有唯一的键值,一个用于所有唯一的键值对,以及一个列出每个文件的所有键/值对。
问题:我正在使用多个并发进程(因此连接)将原始数据添加到数据库中。不幸的是,当尝试向键和值表中添加值时,我遇到了很多检测到的死锁。我尝试了几种不同的插入数据方法(如下所示),但总是最终出现“检测到死锁”错误。
TransactionRollbackError: deadlock detected
DETAIL: Process 26755 waits for ShareLock on transaction 689456; blocked by process 26754. Process 26754 waits for ShareLock on transaction 689467; blocked by process 26755.
我想知道到底是什么导致了这些死锁,并可能指引我找到解决问题的方法。从我正在使用的SQL语句(列在下面)来看,我真的看不出为什么会有任何相互依赖性。
感谢您的阅读!
示例配置文件:
example_key this_is_the_value
other_example other_value
third example yet_another_value
表格定义:
CREATE TABLE keys (
id SERIAL PRIMARY KEY,
hash UUID UNIQUE NOT NULL,
key TEXT);
CREATE TABLE values (
id SERIAL PRIMARY KEY,
hash UUID UNIQUE NOT NULL,
key TEXT);
CREATE TABLE keyvalue_pairs (
id SERIAL PRIMARY KEY,
file_id INTEGER REFERENCES filenames,
key_id INTEGER REFERENCES keys,
value_id INTEGER REFERENCES values);
SQL语句:
最初,我尝试使用这个语句来避免任何异常:
WITH s AS (
SELECT id, hash, key FROM keys
WHERE hash = 'hash_value';
), i AS (
INSERT INTO keys (hash, key)
SELECT 'hash_value', 'key_value'
WHERE NOT EXISTS (SELECT 1 FROM s)
returning id, hash, key
)
SELECT id, hash, key FROM i
UNION ALL
SELECT id, hash, key FROM s;
但即使像这样简单的事情也会导致死锁:
INSERT INTO keys (hash, key)
VALUES ('hash_value', 'key_value')
RETURNING id;
- 在这两种情况下,如果插入的哈希值不唯一而引发异常,我使用存储点回滚更改,并使用另一个语句仅选择我想要的id。
- 我使用哈希作为唯一字段,因为有些键和值太长以至于无法索引。
带有存储点的 Python 代码(使用 psycopg2)的完整示例:
key_value = 'this_key'
hash_val = generate_uuid(value)
try:
cursor.execute(
'''
SAVEPOINT duplicate_hash_savepoint;
INSERT INTO keys (hash, key)
VALUES (%s, %s)
RETURNING id;
'''
(hash_val, key_value)
)
result = cursor.fetchone()[0]
cursor.execute('''RELEASE SAVEPOINT duplicate_hash_savepoint''')
return result
except psycopg2.IntegrityError as e:
cursor.execute(
'''
ROLLBACK TO SAVEPOINT duplicate_hash_savepoint;
'''
)
#TODO: Should ensure that values match and this isn't just
#a hash collision
cursor.execute(
'''
SELECT id FROM keys WHERE hash=%s LIMIT 1;
'''
(hash_val,)
)
return cursor.fetchone()[0]
更新: 所以我在另一个stackexchange网站上找到了线索:
具体来说:
UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命令在搜索目标行方面的行为与SELECT相同:它们只会找到作为命令开始时间1提交的目标行。但是,这样的目标行可能已经被另一个并发事务更新(或删除或锁定)了,在找到它时。在这种情况下,应该更新者将等待第一个更新事务提交或回滚(如果它仍在进行中)。如果第一个更新程序回滚,则其效果将被否定,第二个更新程序可以继续更新最初找到的行。如果第一个更新程序提交,则第二个更新程序将忽略该行(如果第一个更新程序删除它),否则它将尝试将其操作应用于该行的更新版本。
虽然我仍然不确定协同依赖关系在哪里,但是处理大量键/值对而不提交可能会导致类似的情况。如果我在添加每个单独的配置文件后提交,死锁就不会发生。