1

Does PostgreSQL provide a built-in optimistic concurrency control mechanism such as SQL Server's Read Committed Snapshot Isolation? Readers can read all the rows without blocking, but writes will block and fail if row was modified during the transaction.

Edit: Writes don't actually 'block and fail' under RCSI but rather just block.

migrated from stackoverflow.com Jan 31 '16 at 9:47

This question came from our site for professional and enthusiast programmers.

4

This is how Postgres was designed from ground up (as opposed to SQL Server where this was added very late)

See the manual for details: http://www.postgresql.org/docs/current/static/mvcc-intro.html

You can't actually configure Postgres so that readers would block writers (automatically or implicitly)

The only way to block read access in Postgres is to (manually) lock the complete table exclusively (lock table foo access exclusive).

This will block any access to the table (not only individual rows).


And unlike SQL Server, Postgres also does not have lock escalation where the whole table is locked for writes when a certain threshold of row locks is exceeded.

So just because one transaction updates 95% of all rows in a table (and thus locks them for other writers), that doesn't mean that other transactions can't update the remaining 5% - with SQL Server, you'd most probably wind up with a lock escalation locking the whole table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.