Re: What is locktype=transactionid ?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Tony Day <tonyd(at)panztel(dot)com>
Cc: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: What is locktype=transactionid ?
Date: 2010-05-18 04:30:39
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-novice

Tony Day <tonyd(at)panztel(dot)com> writes:
> I have a process that is waiting for a lock and the locktype of the lock is
> "transactionid".
> Despite a fair bit of googling I have been unable to find more information
> on this type of lock.

In Postgres, every transaction takes an exclusive lock on its own
transactionid when it starts. Sometimes, when a transaction wants to
wait for another transaction to complete, it'll try to take share lock
on that other transaction's id. This will of course block until the
exclusive lock goes away.

Currently, the only case where anything will try to take a sharelock on
transaction id is when it is blocking on a row-level lock as a result of
trying to modify or delete or SELECT FOR UPDATE/FOR SHARE a row that the
other transaction already modified or deleted or selected FOR
UPDATE/SHARE. (Why this doesn't show up as a more obvious row-level
lock in pg_locks is an interesting technical detail, but you probably
don't care that much about that.)

Given what you're showing in pg_stat_activity, the most likely bet is
that the "idle in transaction" client is sitting on an uncommitted row
modification. You need to whack it upside the head and convince it to
commit or abort its modifications a bit more promptly. The dependency
could be a bit indirect --- for instance, modifying a row that is linked
by a foreign key dependency to the one the second transaction wants to
change --- but it's a very general rule that sitting on uncommitted
modifications for any length of time is Bad Behavior.

regards, tom lane

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Tony Day 2010-05-18 05:17:58 Re: What is locktype=transactionid ?
Previous Message Tony Day 2010-05-18 04:02:36 What is locktype=transactionid ?