From: Markus Schiltknecht <markus@bluegap.ch>
To: Dave Cramer <pg@fastcrypt.com>
Cc: Jens Schipkowski <jens.schipkowski@apus.co.at>
Cc: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>
Cc: pgsql-general@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Locking in PostgreSQL?
Date: Wed, 06 Dec 2006 14:42:32 +0100
Message-ID: <4576C8C8.7060301@bluegap.ch> (raw)
In-Reply-To: <4ED560E4-3484-43BA-99BB-18888D968F74@fastcrypt.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: markus@bluegap.ch, pg@fastcrypt.com, jens.schipkowski@apus.co.at, J.Kraaijeveld@askesis.nl, pgsql-general@postgresql.org
Subject: Re: [PERFORM] Locking in PostgreSQL?
In-Reply-To: <4576C8C8.7060301@bluegap.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox