From: Jens Schipkowski <jens.schipkowski@apus.co.at>
To: Dave Cramer <pg@fastcrypt.com>
To: 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:20:04 +0100
Message-ID: <op.tj43bqe481rjf6@xjens.apus.local> (raw)
In-Reply-To: <30BD1C9A-1664-4290-B343-0FB1FEBA7B53@fastcrypt.com>
References: <1165388652.6396.1.camel@panoramix>
<30BD1C9A-1664-4290-B343-0FB1FEBA7B53@fastcrypt.com>
On Wed, 06 Dec 2006 13:29:37 +0100, Dave Cramer <pg@fastcrypt.com> wrote:
> Unless you specifically ask for it postgresql doesn't lock any rows when
> you update data.
>
Thats not right. UPDATE will force a RowExclusiveLock to rows matching the
WHERE clause, or all if no one is specified.
@Joost Kraaijeveld: Yes, because there is no EntryExclusiveLock or
something like that. Roughly you can say, each UPDATE statement iterates
through the affected table and locks the WHERE clause matching records
(rows) exclusivly to prevent data inconsistancy during the UPDATE. After
that your rows will be updated and the lock will be repealed.
You can see this during an long lasting UPDATE by querying the pg_locks
with joined pg_stats_activity (statement must be enabled).
> Dave
> On 6-Dec-06, at 2:04 AM, Joost Kraaijeveld wrote:
>
>> Does PostgreSQL lock the entire row in a table if I update only 1
>> column?
>>
>>
>> --Groeten,
>>
>> Joost Kraaijeveld
>> Askesis B.V.
>> Molukkenstraat 14
>> 6524NB Nijmegen
>> tel: 024-3888063 / 06-51855277
>> fax: 024-3608416
>> web: www.askesis.nl
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
CU,
Jens
--
**
Jens Schipkowski
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: jens.schipkowski@apus.co.at, pg@fastcrypt.com, J.Kraaijeveld@askesis.nl, pgsql-general@postgresql.org
Subject: Re: [PERFORM] Locking in PostgreSQL?
In-Reply-To: <op.tj43bqe481rjf6@xjens.apus.local>
* 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