I'm reading through the PG docs, and it operates at Read Committed
isolation level by default.
Does this mean that, if two backends start UPDATEs on the same row at
the same time, they will serialize? In other words, let's say we have
a table called "account", with a columns "number" and "dollars", and
account #99 has 10 dollars in it and this happens:
One backend does this:
UPDATE account SET dollars = dollars + 5 WHERE number = 99;
and the other backend does this:
UPDATE account SET dollars = dollars + 7 WHERE number = 99;
As I understand Read Committed Isolation, the following are true:
1. The resulting value of the "dollars" column for account 99 will
always be 22 after both UPDATEs go through, even if both start at
the same time.
2. Most importantly, one of these two UPDATEs will block completely
until the other is completely finished. In other words, PG does
the WHERE statement, puts a row-level lock on the row, completes
the UPDATE, and then unlocks the row so the next UPDATE can happen.
The reason why I'm asking this is because I would like to do some
row-level locking from within pl/pgsql, but there is no way to do
this, but, if UPDATE has implicit row-level locking, I could do it
that way.
Thanks for any tips.
