Thread: 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