Subject: Re: One long transaction or multiple short transactions?
To: "Graeme B. Bell" <graeme.bell@nibio.no>, Laurent Martelli
 <laurent.martelli@enercoop.org>, "pgsql-performance@postgresql.org list"
 <pgsql-performance@postgresql.org>
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>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <56226889.4050609@BlueTreble.com>
Date: Sat, 17 Oct 2015 10:26:01 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0)
 Gecko/20100101 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: <884892BF-DD42-4E0A-8973-3A691E25E450@skogoglandskap.no>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:23
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 17 Oct 2015 15:26:08.1527 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY2PR11MB0677
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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