From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Graeme B. Bell <graeme.bell@nibio.no>
To: Laurent Martelli <laurent.martelli@enercoop.org>
To: pgsql-performance@postgresql.org list <pgsql-performance@postgresql.org>
Subject: Re: One long transaction or multiple short transactions?
Date: Sat, 17 Oct 2015 10:26:01 -0500
Message-ID: <56226889.4050609@BlueTreble.com> (raw)
In-Reply-To: <884892BF-DD42-4E0A-8973-3A691E25E450@skogoglandskap.no>
References: <002a01d0ffe4$2c630e60$85292b20$@stonebanks.ca>
<003d01d0ffe4$9ae8f890$d0bae9b0$@stonebanks.ca>
<A76B25F2823E954C9E45E32FA49D70ECCD54DF53@mail.corp.perceptron.com>
<00fe01d10159$8e731980$ab594c80$@stonebanks.ca>
<56176288.9040500@enercoop.org>
<884892BF-DD42-4E0A-8973-3A691E25E450@skogoglandskap.no>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 10/9/15 3:33 AM, Graeme B. Bell wrote:
>
>> I don't think inserts can cause contention on the server. Insert do not lock tables during the transaction. You may have contention on sequence but it won't vary with transaction size.
>
> Perhaps there could be a trigger on inserts which creates some lock contention?
Except inserts *do* take a lot of locks, just not user-level locks.
Operations like finding a page to insert into, seeing if that page is in
shared buffers, loading the page into shared buffers, modifying a shared
buffer, getting the relation extension lock if you need to add a new
page. Then there's a whole pile of additional locking you could be
looking at for inserting into any indexes.
Now, most of the locks I described up there are transaction-aware, but
there's other things happening at a transaction level that could alter
that locking. So it wouldn't surprise me if you're seeing radically
different behavior based on transaction duration.
Also, it sounds like perhaps longer transactions are involving more
tables? Is this a star schema you're dealing with?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: Jim.Nasby@BlueTreble.com, graeme.bell@nibio.no, laurent.martelli@enercoop.org
Subject: Re: One long transaction or multiple short transactions?
In-Reply-To: <56226889.4050609@BlueTreble.com>
* 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