SQL Server – Missing Rows Under Read Committed Isolation

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

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.