Re: One long transaction or multiple short transactions?

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