>>> Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> I don't think this is PostgreSQL-specific, I think non-MVCC database
> systems face this same choice (although the terminology would be
> different).
A somewhat dated description for Sybase (it predates their support of
row level locks and the related predicate locks on indexes) is here:
http://manuals.sybase.com/onlinebooks/group-asarc/srv10024/sag/@Generic__BookTextView/41766;pt=41535/*
Simplified, in a READ COMMITTED transaction a SELECT takes a lock
which conflicts with update before reading, and holds it until the
executing statement is done with that table; an UPDATE takes a lock
which conflicts with any access (read or write) before it updates a
row, and holds it until COMMIT or ROLLBACK. This guarantees that the
SELECT doesn't see the results of an incomplete UPDATE, at the expense
of taking locks, blocking, and possible serialization failures (in the
form of deadlocks).
-Kevin