Can Atomic Counter in Trigger Cause ‘Could Not Serialize Access’ Error?

postgresqlpostgresql-9.4

I have the following code:

create table users (id serial primary key, comments_counter integer default 0);
create table comments (id serial primary key, user_id integer references users(id) not null)

create or replace function users_comments_counter()
returns trigger as $$
begin
  update users set comments_counter = comments_counter + 1
  where id = NEW.user_id;
  return null;
end;
$$ language plpgsql;

create trigger users_comments_counter_trg
after insert on comments
for each row execute procedure users_comments_counter();

Is it possible that inserting a row to the comments table will throw could not serialize access due to concurrent update?

Or maybe in other words: is it possible to use such counter (with ANY of transaction isolation levels) while avoiding situation where "retry" (due transaction serialization error) is needed, and data consistency is guaranteed (counter will behave as expected – will be incremented only if comment row will be successfuly inserted).

Best Answer

The actions taken in the trigger are part of the transaction that fires it. This means that the counter increment will happen only if the whole transaction (or the subtransaction defined by a SAVEPOINT) succeeds. This way we can say that the above trigger will increment the counter atomically, from the point of view of the INSERT INTO comments statement.

If this is all you have inside the transaction (INSERT and the increment), you are safe from concurrency issues, and the default isolation level (READ COMMITTED) is enough.

Furthermore, depending on how the INSERT is issued, you might even spare the trigger, which would save you some overhead. In order to do this, the following conditions must be met:

  • you can wrap the INSERT INTO comments and UPDATE users statements into a transaction
  • there is no other way adding a comment than the transaction in the previous point (i. e. no manual editing of the data by an administrator or similar)
Related Question