Postgresql Concurrency

Question

In a project that I'm working, there's a table with a "on update" trigger, that monitors if a boolean column has changed (ex.: false -> true = do some action). But this action can only be done once for a row.

There will be multiple clients accessing the database, so I can suppose that eventually, multiple clients will try to update the same row column in parallel.

Does the "update" trigger itself handle the concurrency itself, or I need to do it in a transaction and manually lock the table?

Answer 1

Triggers don't handle concurrency, and PostgreSQL should do the right thing whether or not you use explicit transactions.

PostgreSQL uses optimistic locking which means the first person to actually update the row gets a lock on that row. If a second person tries to update the row, their update statement waits to see if the first commits their change or rolls back.

If the first person commits, the second person gets an error, rather than their change going through and obliterating a change that might have been interesting to them.

If the first person rolls back, the second person's update un-blocks, and goes through normally, because now it's not going to overwrite anything.

The second person can also use the NOWAIT option, which makes the error happen immediately instead of blocking, if their update conflicts with an unresolved change.