Re: [GENERAL] Locking in PostgreSQL?

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