Hi,
Dave Cramer wrote:
> Apparently I've completely misunderstood MVCC then....
Probably not. You are both somewhat right.
Jens Schipkowski wrote:
>> Thats not right. UPDATE will force a RowExclusiveLock to rows
>> matching the WHERE clause, or all if no one is specified.
That almost right, RowExclusiveLock is a table level lock. An UPDATE
acquires that, yes. Additionally there are row-level locks, which is
what you're speaking about. An UPDATE gets an exclusive row-level lock
on rows it updates.
Please note however, that these row-level locks only block concurrent
writers, not readers (MVCC lets the readers see the old, unmodified row).
> My understanding
> is that unless you do a select ... for update then update the rows will
> not be locked.
Also almost right, depending on what you mean by 'locked'. A plain
SELECT acquires an ACCESS SHARE lock on the table, but no row-level
locks. Only a SELECT ... FOR UPDATE does row-level locking (shared ones
here...)
The very fine documentation covers that in [1].
Regards
Markus
[1]: PostgreSQL Documentation, Explicit Locking:
http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html