pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLocking in PostgreSQL?
10+ messages / 8 participants
[nested] [flat]
* Locking in PostgreSQL?
@ 2006-12-06 07:04 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 3 replies; 10+ messages in thread
From: Joost Kraaijeveld @ 2006-12-06 07:04 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Locking in PostgreSQL?
@ 2006-12-06 07:11 Markus Schiltknecht <markus@bluegap.ch>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 0 replies; 10+ messages in thread
From: Markus Schiltknecht @ 2006-12-06 07:11 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-general@postgresql.org
Hi,
Joost Kraaijeveld wrote:
> Does PostgreSQL lock the entire row in a table if I update only 1
> column?
Yes. In PostgreSQL, an update is much like a delete + insert. A
concurrent transaction will still see the old row. Thus the lock only
prevents other writing transactions, not readers.
Regards
Markus
P.S.: please do not cross post such questions.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] Locking in PostgreSQL?
@ 2006-12-06 12:29 Dave Cramer <pg@fastcrypt.com>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 1 reply; 10+ messages in thread
From: Dave Cramer @ 2006-12-06 12:29 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-general@postgresql.org; pgsql-performance
Unless you specifically ask for it postgresql doesn't lock any rows
when you update data.
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] Locking in PostgreSQL?
@ 2006-12-06 13:20 Jens Schipkowski <jens.schipkowski@apus.co.at>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jens Schipkowski @ 2006-12-06 13:20 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-general@postgresql.org; pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] Locking in PostgreSQL?
@ 2006-12-06 13:26 Dave Cramer <pg@fastcrypt.com>
parent: Jens Schipkowski <jens.schipkowski@apus.co.at>
0 siblings, 3 replies; 10+ messages in thread
From: Dave Cramer @ 2006-12-06 13:26 UTC (permalink / raw)
To: Jens Schipkowski <jens.schipkowski@apus.co.at>; +Cc: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; pgsql-general@postgresql.org; pgsql-performance
On 6-Dec-06, at 8:20 AM, Jens Schipkowski wrote:
> 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).
Apparently I've completely misunderstood MVCC then.... My
understanding is that unless you do a select ... for update then
update the rows will not be locked .
Dave
>
>> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Locking in PostgreSQL?
@ 2006-12-06 13:31 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Dave Cramer <pg@fastcrypt.com>
2 siblings, 0 replies; 10+ messages in thread
From: Steinar H. Gunderson @ 2006-12-06 13:31 UTC (permalink / raw)
To: pgsql-performance
On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer wrote:
> Apparently I've completely misunderstood MVCC then.... My
> understanding is that unless you do a select ... for update then
> update the rows will not be locked .
The discussion was about updates, not selects. Selects do not in general lock
(except for ... for update, as you say).
To (partially) answer the original question: The number of columns updated
does not matter for the locking situation.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] Locking in PostgreSQL?
@ 2006-12-06 13:42 Markus Schiltknecht <markus@bluegap.ch>
parent: Dave Cramer <pg@fastcrypt.com>
2 siblings, 0 replies; 10+ messages in thread
From: Markus Schiltknecht @ 2006-12-06 13:42 UTC (permalink / raw)
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
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] Locking in PostgreSQL?
@ 2006-12-06 13:45 Martijn van Oosterhout <kleptog@svana.org>
parent: Dave Cramer <pg@fastcrypt.com>
2 siblings, 0 replies; 10+ messages in thread
From: Martijn van Oosterhout @ 2006-12-06 13:45 UTC (permalink / raw)
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
On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer 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.
>
> Apparently I've completely misunderstood MVCC then.... My
> understanding is that unless you do a select ... for update then
> update the rows will not be locked .
I think it comes down to what you mean by RowExclusiveLock. In MVCC,
writers don't block readers, so even if someone executes an update on a
row, readers (SELECT statements) will not be blocked.
So it's not a lock as such, more a "I've updated this row, go find the
new version if that's appropriate for your snapshot".
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] Locking in PostgreSQL?
@ 2006-12-06 17:58 Casey Duncan <casey@pandora.com>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 1 reply; 10+ messages in thread
From: Casey Duncan @ 2006-12-06 17:58 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-general@postgresql.org; pgsql-performance
On Dec 5, 2006, at 11:04 PM, Joost Kraaijeveld wrote:
> Does PostgreSQL lock the entire row in a table if I update only 1
> column?
Know that updating 1 column is actually updating the whole row. So if
one transaction updates column A of a row, it will block another
concurrent transaction that tries to update column B of the same row.
As was mentioned however, neither of these transactions block others
reading the row in question, though they see the row as it existed
before the updates until those update transactions commit.
If you know that your application will suffer excessive update
contention trying to update different columns of the same row, you
could consider splitting the columns into separate tables. This is an
optimization to favor write contention over read performance (since
you would likely need to join the tables when selecting) and I
wouldn't do it speculatively. I'd only do it if profiling the
application demonstrated significantly better performance with two
tables.
-Casey
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] Locking in PostgreSQL?
@ 2006-12-06 18:09 Erik Jones <erik@myemma.com>
parent: Casey Duncan <casey@pandora.com>
0 siblings, 0 replies; 10+ messages in thread
From: Erik Jones @ 2006-12-06 18:09 UTC (permalink / raw)
To: Casey Duncan <casey@pandora.com>; +Cc: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; pgsql-general@postgresql.org; pgsql-performance
Casey Duncan wrote:
> On Dec 5, 2006, at 11:04 PM, Joost Kraaijeveld wrote:
>
>> Does PostgreSQL lock the entire row in a table if I update only 1
>> column?
>
> Know that updating 1 column is actually updating the whole row. So if
> one transaction updates column A of a row, it will block another
> concurrent transaction that tries to update column B of the same row.
> As was mentioned however, neither of these transactions block others
> reading the row in question, though they see the row as it existed
> before the updates until those update transactions commit.
>
> If you know that your application will suffer excessive update
> contention trying to update different columns of the same row, you
> could consider splitting the columns into separate tables. This is an
> optimization to favor write contention over read performance (since
> you would likely need to join the tables when selecting) and I
> wouldn't do it speculatively. I'd only do it if profiling the
> application demonstrated significantly better performance with two
> tables.
>
> -Casey
Or, come up with some kind of (pre)caching strategy for your updates
wherein you could then combine multiple updates to the same row into one
update.
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-12-06 18:09 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-06 07:04 Locking in PostgreSQL? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-12-06 07:11 ` Markus Schiltknecht <markus@bluegap.ch>
2006-12-06 12:29 ` Dave Cramer <pg@fastcrypt.com>
2006-12-06 13:20 ` Jens Schipkowski <jens.schipkowski@apus.co.at>
2006-12-06 13:26 ` Dave Cramer <pg@fastcrypt.com>
2006-12-06 13:31 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-12-06 13:42 ` Markus Schiltknecht <markus@bluegap.ch>
2006-12-06 13:45 ` Martijn van Oosterhout <kleptog@svana.org>
2006-12-06 17:58 ` Casey Duncan <casey@pandora.com>
2006-12-06 18:09 ` Erik Jones <erik@myemma.com>
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