Обсуждение: Various locking questions

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.

From: "Dr. Evil" <drevil@sidereal.kz>

> I'm reading through the PG docs, and it operates at Read Committed
> isolation level by default.

> 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.

If both succeed - if you put both inside transaction blocks it's possible
one could time out.

> 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.

Kind of, but not quite. The second UPDATE blocks because it sees that it
cannot safely modify the row. You can select that row, and you'll see
whatever value was last committed. You can alter the transaction isolation
level so that you only see the value before your current transaction
started - I'll leave you to figure out why you'd want that.

If you want to explore how transactions are handled, open two sessions of
psql and put your UPDATEs inside explicit transactions. Scatter SELECTs
liberally and play around. I found the docs on MVCC and transaction levels
didn't make any sense until I'd seen it for myself.

> 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.

All operations take place in their own transaction, so two updates can't
overwrite each other if that's what you want.

Sounds like SELECT FOR UPDATE might well be what you're after.

- Richard Huxton