Message-ID: <4576C8C8.7060301@bluegap.ch>
Date: Wed, 06 Dec 2006 14:42:32 +0100
From: Markus Schiltknecht <markus@bluegap.ch>
User-Agent: Icedove 1.5.0.8 (X11/20061128)
MIME-Version: 1.0
To: Dave Cramer <pg@fastcrypt.com>
CC: Jens Schipkowski <jens.schipkowski@apus.co.at>,
	Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>,
	pgsql-general@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Locking in PostgreSQL?
References: <1165388652.6396.1.camel@panoramix>
	<30BD1C9A-1664-4290-B343-0FB1FEBA7B53@fastcrypt.com>
	<op.tj43bqe481rjf6@xjens.apus.local>
	<4ED560E4-3484-43BA-99BB-18888D968F74@fastcrypt.com>
In-Reply-To: <4ED560E4-3484-43BA-99BB-18888D968F74@fastcrypt.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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