我有两张桌子:
create table name
(
id bigserial primary key,
name text not null
);
create table name_score
(
id bigserial primary key,
name text not null,
score bigint default 1 not null
);
以及表 name_score 上的唯一索引:
create unique index name_score_name_uindex
on name_score(name);
以及触发函数:
create or replace function add_name_func() returns trigger as
$name_count$
DECLARE
new_name text;
begin
new_name := NEW.name;
insert into name_score(name)
values (new_name)
on conflict (name) do update set score=(select score from name_score where name = new_name for update) + 1;
return null;
end;
$name_count$ language plpgsql;
create trigger name_score_trigger
after insert
on name
for each row
execute procedure add_name_func();
现在,当我第一次使用多线程插入 3 行时,我得到:
ERROR: null value in column "score" of relation "name_score" violates not-null constraint
Detail: Failing row contains (273, "test", null).
Where: SQL statement "insert into name_score(name)
values (new_name)
on conflict (name) do update set score=(select score from name_score where name = new_name for update) + 1"
PL/pgSQL function add_name_func() line 7 at SQL statement
数据库中成功插入了一行,分数为 1
此后,此功能有时会插入错误的分数。
我已经尝试过锁定表并且它可以工作,但我对此感到困惑。
我想知道有没有更好的方法来解决这个问题?
非常感谢您阅读我的问题。
问题在于您在分支中使用的子查询
UPDATE
。如果您写只要您使用默认
READ COMMITTED
事务隔离级别。 所采取的行锁UPDATE
将序列化更新,并且第二个操作将看到第一个操作的结果,这样就不会出现竞争条件。