Thread: Server error and deadlocks

Server error and deadlocks

From
"Orr, Steve"
Date:

Newbie here...

We have a web app with a MySQL (ISAM) db server and we're wanting to port and run it on PostgreSQL. We've got data in PostgreSQL and code running under Mercury Interactive "LoadRunner" but I'm seeing these messages on the server:

------------------------------------------------------
WARNING:  COMMIT: no transaction in progress
LOG:  pq_recvbuf: unexpected EOF on client connection
ERROR:  deadlock detected
ERROR:  deadlock detected
ERROR:  deadlock detected
. . .
------------------------------------------------------
Seems to me like commits are being made when there's nothing to commit. There's gotta be some overhead here so I'm thinking unnecessary commits should be removed from the code.

Also, on the EOF on client connection messages I'm thinking the cgi code should explicitly disconnect. Does it matter?

Finally, there are LOTS of deadlocks and I'm thinking it's because they are not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or they are constantly updating the same rows. It seems our duhvelopers need to get up to speed on transaction management with PostgreSQL. Any suggestions on how to get them to do PostgreSQL transactions and wean them off MySQL ISAM?

TIA!!!!!!!!
db

Re: Server error and deadlocks

From
Stephan Szabo
Date:
On Mon, 13 Jan 2003, Orr, Steve wrote:

> Finally, there are LOTS of deadlocks and I'm thinking it's because they are
> not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or they
> are constantly updating the same rows. It seems our duhvelopers need to get

Well, if you're using foreign keys, you might be running into a deficiency
in the foreign key implementation.  If that is it, then currently a
partial workaround may be to make the constraints deferred which lessens
the length of the lock, but doesn't remove the base deadlock possibility.


Re: Server error and deadlocks

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Mon, 13 Jan 2003, Orr, Steve wrote:
>> Finally, there are LOTS of deadlocks and I'm thinking it's because they are
>> not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or they
>> are constantly updating the same rows. It seems our duhvelopers need to get

> Well, if you're using foreign keys, you might be running into a deficiency
> in the foreign key implementation.

If these folk are immigrants from MySQL, I bet they're not using foreign
keys.  Taking table-level locks seems like a more likely route to
deadlock.  But this is all guesswork --- we need to see some info about
the queries being issued before we can opine about the real cause.

            regards, tom lane

Re: Server error and deadlocks

From
"Orr, Steve"
Date:

You're right about the lack of foreign keys... it's a foreign concept to the DUHvelopers. I suspect table locks but are there any database level tracing tools to show the SQL? Will the trace_locks and debug_deadlocks parameters help and how do I use them?

TIA

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, January 13, 2003 9:18 PM
To: Stephan Szabo
Cc: Orr, Steve; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Server error and deadlocks

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Mon, 13 Jan 2003, Orr, Steve wrote:
>> Finally, there are LOTS of deadlocks and I'm thinking it's because they are
>> not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or they
>> are constantly updating the same rows. It seems our duhvelopers need to get

> Well, if you're using foreign keys, you might be running into a deficiency
> in the foreign key implementation.

If these folk are immigrants from MySQL, I bet they're not using foreign
keys.  Taking table-level locks seems like a more likely route to
deadlock.  But this is all guesswork --- we need to see some info about
the queries being issued before we can opine about the real cause.

                        regards, tom lane

Re: Server error and deadlocks

From
Tom Lane
Date:
"Orr, Steve" <sorr@rightnow.com> writes:
> I suspect table locks but are there any database level tracing
> tools to show the SQL? Will the trace_locks and debug_deadlocks parameters
> help and how do I use them?

I do not think those will help you; they are low-level debugging aids
and are probably far too verbose to be useful for tracing an occasional
application problem.  Besides which, they're not even compiled in by
default.

It seems to me that it would be appropriate for DeadLockCheck to emit
more information about the problem it's found than just "there's a
deadlock".  How do people feel about reporting the detected cycle as
a series of NOTICE messages?  It would look pretty similar to the
pg_locks view:

NOTICE:  Proc <pid> waits for <lockmode> on <rel> <db>; blocked by <pid>
NOTICE:  Proc <pid> waits for <lockmode> on <rel> <db>; blocked by <pid>
NOTICE:  Proc <pid> waits for <lockmode> on <rel> <db>; blocked by <pid>
ERROR: Deadlock detected

If that seems acceptable, I could code it up in short order.  While I
wouldn't want to apply it to the REL7_3_STABLE branch, I see no reason
Steve wouldn't be able to use the patch locally to identify his problem.

            regards, tom lane

Re: Server error and deadlocks

From
Andrew Sullivan
Date:
On Tue, Jan 14, 2003 at 10:31:14AM -0500, Tom Lane wrote:
> deadlock".  How do people feel about reporting the detected cycle as
> a series of NOTICE messages?  It would look pretty similar to the

Yes, please!!

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: Server error and deadlocks

From
"scott.marlowe"
Date:
On Tue, 14 Jan 2003, Orr, Steve wrote:

> You're right about the lack of foreign keys... it's a foreign concept to the
> DUHvelopers. I suspect table locks but are there any database level tracing
> tools to show the SQL? Will the trace_locks and debug_deadlocks parameters
> help and how do I use them?

Actually, what you need is a code audit and some training for your
developers on how real transactions work and why they don't need to use
table level locks.

I'd have them study and learn from the section in the manual on MVCC and
then go back in and change their code to use transactions with select for
update instead of table locks.


Re: Server error and deadlocks

From
Juan Jose Comellas
Date:
Does anybody know if there is a plan to improve the foreign key support in
PostgreSQL?

While working with PostgreSQL 7.2.1 (Debian Linux/testing) we found out that
when a row is inserted in a table that has columns that are foreign keys,
Postgres normally locks the rows corresponding to the foreign keys (in their
original tables) both for reading and for writing. This is strange, because
it seems to me that it should allow reading from these rows (at least Oracle
8i does this) from other transactions. According to Postgres' logs, a SELECT
FOR UPDATE is executed on each of the foreign keys referenced in an INSERT,
UPDATE. Isn't this a little bit excessive?

This is a serious bottleneck in one application we've developed because we
have some basic tables from which foreign keys are referenced in a lot of
queries that cannot be executed in parallel because of this problem.


On Monday 13 January 2003 22:16, Stephan Szabo wrote:
> On Mon, 13 Jan 2003, Orr, Steve wrote:
> > Finally, there are LOTS of deadlocks and I'm thinking it's because they
> > are not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or
> > they are constantly updating the same rows. It seems our duhvelopers need
> > to get
>
> Well, if you're using foreign keys, you might be running into a deficiency
> in the foreign key implementation.  If that is it, then currently a
> partial workaround may be to make the constraints deferred which lessens
> the length of the lock, but doesn't remove the base deadlock possibility.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Juan Jose Comellas
(juanjo@comellas.com.ar)

Re: Server error and deadlocks

From
Stephan Szabo
Date:
On Tue, 14 Jan 2003, Juan Jose Comellas wrote:

> Does anybody know if there is a plan to improve the foreign key support in
> PostgreSQL?

Umm, define plan.  Seriously, I've been looking at it, but I've only got
a couple of hours a week in general, so it's not going terribly
well/quickly.

> While working with PostgreSQL 7.2.1 (Debian Linux/testing) we found out that
> when a row is inserted in a table that has columns that are foreign keys,
> Postgres normally locks the rows corresponding to the foreign keys (in their
> original tables) both for reading and for writing. This is strange, because
> it seems to me that it should allow reading from these rows (at least Oracle
> 8i does this) from other transactions. According to Postgres' logs, a SELECT
> FOR UPDATE is executed on each of the foreign keys referenced in an INSERT,
> UPDATE. Isn't this a little bit excessive?

Yes, but there isn't a weaker lock that exists at the SQL statement level
currently that gives enough strength to actual guarantee the constraint.

Actually, getting the lock strength down is fairly easy with doing a
dirty read and some stuff with that so that you can say insert pointing
to the same row from concurrent transactions, it's dealing with the
created and existing deadlock conditions that's hard.


Re: Server error and deadlocks

From
Juan Jose Comellas
Date:
On Tuesday 14 January 2003 15:11, Stephan Szabo wrote:
> On Tue, 14 Jan 2003, Juan Jose Comellas wrote:
> > Does anybody know if there is a plan to improve the foreign key support
> > in PostgreSQL?
>
> Umm, define plan.  Seriously, I've been looking at it, but I've only got
> a couple of hours a week in general, so it's not going terribly
> well/quickly.

What I meant is: is the PostgreSQL development team aware of the problem? Is
there a proposed fix that's not implemented yet?

BTW, where in the Postgres sources can I find the code responsible for locking
foreign keys during and INSERT/UPDATE?


> > While working with PostgreSQL 7.2.1 (Debian Linux/testing) we found out
> > that when a row is inserted in a table that has columns that are foreign
> > keys, Postgres normally locks the rows corresponding to the foreign keys
> > (in their original tables) both for reading and for writing. This is
> > strange, because it seems to me that it should allow reading from these
> > rows (at least Oracle 8i does this) from other transactions. According to
> > Postgres' logs, a SELECT FOR UPDATE is executed on each of the foreign
> > keys referenced in an INSERT, UPDATE. Isn't this a little bit excessive?
>
> Yes, but there isn't a weaker lock that exists at the SQL statement level
> currently that gives enough strength to actual guarantee the constraint.
>
> Actually, getting the lock strength down is fairly easy with doing a
> dirty read and some stuff with that so that you can say insert pointing
> to the same row from concurrent transactions, it's dealing with the
> created and existing deadlock conditions that's hard.

Do you know of any way to decrease the lock strength without modifying
Postgres' sources?

Why should the weaker lock exist at the SQL statement level? Why can't you
have some kind of internal read-write lock so that you can lock a foreign key
for writing when inserting/updating a row that references it, but still allow
reading this foreign key from other transactions?


--
Juan Jose Comellas
(juanjo@comellas.com.ar)

Re: Server error and deadlocks

From
Stephan Szabo
Date:
On Tue, 14 Jan 2003, Juan Jose Comellas wrote:

> On Tuesday 14 January 2003 15:11, Stephan Szabo wrote:
> > On Tue, 14 Jan 2003, Juan Jose Comellas wrote:
> > > Does anybody know if there is a plan to improve the foreign key support
> > > in PostgreSQL?
> >
> > Umm, define plan.  Seriously, I've been looking at it, but I've only got
> > a couple of hours a week in general, so it's not going terribly
> > well/quickly.
>
> What I meant is: is the PostgreSQL development team aware of the problem? Is
> there a proposed fix that's not implemented yet?

Yes and not entirely. There almost certainly is a complete
solution through the use of dirty reads and a bit of magic to prevent
deadlocks, but it's that bit of magic that I haven't managed to completely
wrap my brain around. If you look through the archives, you'll find an
incomplete patch for one of my tests for new code (doesn't entirely work).
I've started a couple more various attempts that haven't entirely worked
either, but as I said, I certainly haven't been spending alot of time on
it.

> BTW, where in the Postgres sources can I find the code responsible for locking
> foreign keys during and INSERT/UPDATE?

IIRC, backend/utils/adt/ri_triggers.c.

> > Yes, but there isn't a weaker lock that exists at the SQL statement level
> > currently that gives enough strength to actual guarantee the constraint.
> >
> > Actually, getting the lock strength down is fairly easy with doing a
> > dirty read and some stuff with that so that you can say insert pointing
> > to the same row from concurrent transactions, it's dealing with the
> > created and existing deadlock conditions that's hard.
>
> Do you know of any way to decrease the lock strength without modifying
> Postgres' sources?
>
> Why should the weaker lock exist at the SQL statement level? Why can't you
> have some kind of internal read-write lock so that you can lock a foreign key
> for writing when inserting/updating a row that references it, but still allow
> reading this foreign key from other transactions?

The triggers currently work by using SPI to run statements
(select/update/delete), so anything that the triggers want to do would
have to work within that framework unless the triggers were completely
rewritten.


Re: Server error and deadlocks

From
"Orr, Steve"
Date:

Agreed but what I need rightnow is to find a "smoking gun" to beat the duhvelopers on the head with in order to first get their attention. The smoking gun would be an example of their poor SQL technique so I can ask them, "What were you smoking when you wrote this junk?" In this context, is there any way to create some sort of trace log of all the SQL submitted to the server for a given time period, database, user, etc.? I can do this in Oracle and I think it's a necessary feature.

Steve Orr

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Tuesday, January 14, 2003 10:35 AM
To: Orr, Steve
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Server error and deadlocks

On Tue, 14 Jan 2003, Orr, Steve wrote:

> You're right about the lack of foreign keys... it's a foreign concept to the
> DUHvelopers. I suspect table locks but are there any database level tracing
> tools to show the SQL? Will the trace_locks and debug_deadlocks parameters
> help and how do I use them?

Actually, what you need is a code audit and some training for your
developers on how real transactions work and why they don't need to use
table level locks.

I'd have them study and learn from the section in the manual on MVCC and
then go back in and change their code to use transactions with select for
update instead of table locks.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: Server error and deadlocks

From
Tom Lane
Date:
"Orr, Steve" <sorr@rightnow.com> writes:
> Agreed but what I need rightnow is to find a "smoking gun" to beat the
> duhvelopers on the head with in order to first get their attention. The
> smoking gun would be an example of their poor SQL technique so I can ask
> them, "What were you smoking when you wrote this junk?" In this context, is
> there any way to create some sort of trace log of all the SQL submitted to
> the server for a given time period, database, user, etc.?

See the logging options in postgresql.conf.

If you are interested, attached is the patch I just applied to CVS HEAD
to print out information about deadlocks.  It should apply cleanly to
7.3.*.  Sample output is

regression=# lock table tenk1;
NOTICE:  Proc 27417 waits for AccessExclusiveLock on relation 18987 database 17054; blocked by 27415
NOTICE:  Proc 27415 waits for ShareLock on transaction 6446; blocked by 27417
ERROR:  deadlock detected
regression=#

            regards, tom lane

*** src/backend/storage/lmgr/deadlock.c.orig    Thu Oct 31 19:40:23 2002
--- src/backend/storage/lmgr/deadlock.c    Thu Jan 16 15:45:38 2003
***************
*** 17,22 ****
--- 17,24 ----
   *    Interface:
   *
   *    DeadLockCheck()
+  *    DeadLockReport()
+  *    RememberSimpleDeadLock()
   *    InitDeadLockChecking()
   *
   *-------------------------------------------------------------------------
***************
*** 45,56 ****
      int            nProcs;
  } WAIT_ORDER;


  static bool DeadLockCheckRecurse(PGPROC *proc);
  static bool TestConfiguration(PGPROC *startProc);
  static bool FindLockCycle(PGPROC *checkProc,
                EDGE *softEdges, int *nSoftEdges);
! static bool FindLockCycleRecurse(PGPROC *checkProc,
                       EDGE *softEdges, int *nSoftEdges);
  static bool ExpandConstraints(EDGE *constraints, int nConstraints);
  static bool TopoSort(LOCK *lock, EDGE *constraints, int nConstraints,
--- 47,73 ----
      int            nProcs;
  } WAIT_ORDER;

+ /*
+  * Information saved about each edge in a detected deadlock cycle.  This
+  * is used to print a diagnostic message upon failure.
+  *
+  * Note: because we want to examine this info after releasing the LockMgrLock,
+  * we can't just store LOCK and PGPROC pointers; we must extract out all the
+  * info we want to be able to print.
+  */
+ typedef struct
+ {
+     LOCKTAG        locktag;        /* ID of awaited lock object */
+     LOCKMODE    lockmode;        /* type of lock we're waiting for */
+     int            pid;            /* PID of blocked backend */
+ } DEADLOCK_INFO;
+

  static bool DeadLockCheckRecurse(PGPROC *proc);
  static bool TestConfiguration(PGPROC *startProc);
  static bool FindLockCycle(PGPROC *checkProc,
                EDGE *softEdges, int *nSoftEdges);
! static bool FindLockCycleRecurse(PGPROC *checkProc, int depth,
                       EDGE *softEdges, int *nSoftEdges);
  static bool ExpandConstraints(EDGE *constraints, int nConstraints);
  static bool TopoSort(LOCK *lock, EDGE *constraints, int nConstraints,
***************
*** 88,93 ****
--- 105,112 ----
  static EDGE *possibleConstraints;
  static int    nPossibleConstraints;
  static int    maxPossibleConstraints;
+ static DEADLOCK_INFO *deadlockDetails;
+ static int    nDeadlockDetails;


  /*
***************
*** 110,117 ****
--- 129,138 ----

      /*
       * FindLockCycle needs at most MaxBackends entries in visitedProcs[]
+      * and deadlockDetails[].
       */
      visitedProcs = (PGPROC **) palloc(MaxBackends * sizeof(PGPROC *));
+     deadlockDetails = (DEADLOCK_INFO *) palloc(MaxBackends * sizeof(DEADLOCK_INFO));

      /*
       * TopoSort needs to consider at most MaxBackends wait-queue entries,
***************
*** 176,181 ****
--- 197,206 ----
   * table to have a different masterLock, all locks that can block had
   * better use the same LWLock, else this code will not be adequately
   * interlocked!
+  *
+  * On failure, deadlock details are recorded in deadlockDetails[] for
+  * subsequent printing by DeadLockReport().  That activity is separate
+  * because we don't want to do it while holding the master lock.
   */
  bool
  DeadLockCheck(PGPROC *proc)
***************
*** 190,196 ****
--- 215,233 ----

      /* Search for deadlocks and possible fixes */
      if (DeadLockCheckRecurse(proc))
+     {
+         /*
+          * Call FindLockCycle one more time, to record the correct
+          * deadlockDetails[] for the basic state with no rearrangements.
+          */
+         int        nSoftEdges;
+
+         nWaitOrders = 0;
+         if (!FindLockCycle(proc, possibleConstraints, &nSoftEdges))
+             elog(FATAL, "DeadLockCheck: deadlock seems to have disappeared");
+
          return true;            /* cannot find a non-deadlocked state */
+     }

      /* Apply any needed rearrangements of wait queues */
      for (i = 0; i < nWaitOrders; i++)
***************
*** 357,365 ****
   *
   * Scan outward from the given proc to see if there is a cycle in the
   * waits-for graph that includes this proc.  Return TRUE if a cycle
!  * is found, else FALSE.  If a cycle is found, we also return a list of
   * the "soft edges", if any, included in the cycle.  These edges could
!  * potentially be eliminated by rearranging wait queues.
   *
   * Since we need to be able to check hypothetical configurations that would
   * exist after wait queue rearrangement, the routine pays attention to the
--- 394,405 ----
   *
   * Scan outward from the given proc to see if there is a cycle in the
   * waits-for graph that includes this proc.  Return TRUE if a cycle
!  * is found, else FALSE.  If a cycle is found, we return a list of
   * the "soft edges", if any, included in the cycle.  These edges could
!  * potentially be eliminated by rearranging wait queues.  We also fill
!  * deadlockDetails[] with information about the detected cycle; this info
!  * is not used by the deadlock algorithm itself, only to print a useful
!  * message after failing.
   *
   * Since we need to be able to check hypothetical configurations that would
   * exist after wait queue rearrangement, the routine pays attention to the
***************
*** 372,383 ****
                int *nSoftEdges)    /* output argument */
  {
      nVisitedProcs = 0;
      *nSoftEdges = 0;
!     return FindLockCycleRecurse(checkProc, softEdges, nSoftEdges);
  }

  static bool
  FindLockCycleRecurse(PGPROC *checkProc,
                       EDGE *softEdges,    /* output argument */
                       int *nSoftEdges)    /* output argument */
  {
--- 412,425 ----
                int *nSoftEdges)    /* output argument */
  {
      nVisitedProcs = 0;
+     nDeadlockDetails = 0;
      *nSoftEdges = 0;
!     return FindLockCycleRecurse(checkProc, 0, softEdges, nSoftEdges);
  }

  static bool
  FindLockCycleRecurse(PGPROC *checkProc,
+                      int depth,
                       EDGE *softEdges,    /* output argument */
                       int *nSoftEdges)    /* output argument */
  {
***************
*** 402,408 ****
--- 444,459 ----
          {
              /* If we return to starting point, we have a deadlock cycle */
              if (i == 0)
+             {
+                 /*
+                  * record total length of cycle --- outer levels will now
+                  * fill deadlockDetails[]
+                  */
+                 Assert(depth <= MaxBackends);
+                 nDeadlockDetails = depth;
+
                  return true;
+             }

              /*
               * Otherwise, we have a cycle but it does not include the
***************
*** 449,456 ****
                      ((1 << lm) & conflictMask) != 0)
                  {
                      /* This proc hard-blocks checkProc */
!                     if (FindLockCycleRecurse(proc, softEdges, nSoftEdges))
                          return true;
                      /* If no deadlock, we're done looking at this holder */
                      break;
                  }
--- 500,517 ----
                      ((1 << lm) & conflictMask) != 0)
                  {
                      /* This proc hard-blocks checkProc */
!                     if (FindLockCycleRecurse(proc, depth+1,
!                                              softEdges, nSoftEdges))
!                     {
!                         /* fill deadlockDetails[] */
!                         DEADLOCK_INFO  *info = &deadlockDetails[depth];
!
!                         info->locktag = lock->tag;
!                         info->lockmode = checkProc->waitLockMode;
!                         info->pid = checkProc->pid;
!
                          return true;
+                     }
                      /* If no deadlock, we're done looking at this holder */
                      break;
                  }
***************
*** 496,503 ****
              if (((1 << proc->waitLockMode) & conflictMask) != 0)
              {
                  /* This proc soft-blocks checkProc */
!                 if (FindLockCycleRecurse(proc, softEdges, nSoftEdges))
                  {
                      /*
                       * Add this edge to the list of soft edges in the
                       * cycle
--- 557,572 ----
              if (((1 << proc->waitLockMode) & conflictMask) != 0)
              {
                  /* This proc soft-blocks checkProc */
!                 if (FindLockCycleRecurse(proc, depth+1,
!                                          softEdges, nSoftEdges))
                  {
+                     /* fill deadlockDetails[] */
+                     DEADLOCK_INFO  *info = &deadlockDetails[depth];
+
+                     info->locktag = lock->tag;
+                     info->lockmode = checkProc->waitLockMode;
+                     info->pid = checkProc->pid;
+
                      /*
                       * Add this edge to the list of soft edges in the
                       * cycle
***************
*** 529,536 ****
              if (((1 << proc->waitLockMode) & conflictMask) != 0)
              {
                  /* This proc soft-blocks checkProc */
!                 if (FindLockCycleRecurse(proc, softEdges, nSoftEdges))
                  {
                      /*
                       * Add this edge to the list of soft edges in the
                       * cycle
--- 598,613 ----
              if (((1 << proc->waitLockMode) & conflictMask) != 0)
              {
                  /* This proc soft-blocks checkProc */
!                 if (FindLockCycleRecurse(proc, depth+1,
!                                          softEdges, nSoftEdges))
                  {
+                     /* fill deadlockDetails[] */
+                     DEADLOCK_INFO  *info = &deadlockDetails[depth];
+
+                     info->locktag = lock->tag;
+                     info->lockmode = checkProc->waitLockMode;
+                     info->pid = checkProc->pid;
+
                      /*
                       * Add this edge to the list of soft edges in the
                       * cycle
***************
*** 758,760 ****
--- 835,901 ----
  }

  #endif
+
+ /*
+  * Report details about a detected deadlock.
+  */
+ void
+ DeadLockReport(void)
+ {
+     int        i;
+
+     for (i = 0; i < nDeadlockDetails; i++)
+     {
+         DEADLOCK_INFO  *info = &deadlockDetails[i];
+         int            nextpid;
+
+         /* The last proc waits for the first one... */
+         if (i < nDeadlockDetails-1)
+             nextpid = info[1].pid;
+         else
+             nextpid = deadlockDetails[0].pid;
+
+         if (info->locktag.relId == XactLockTableId && info->locktag.dbId == 0)
+         {
+             /* Lock is for transaction ID */
+             elog(NOTICE, "Proc %d waits for %s on transaction %u; blocked by %d",
+                  info->pid,
+                  GetLockmodeName(info->lockmode),
+                  info->locktag.objId.xid,
+                  nextpid);
+         }
+         else
+         {
+             /* Lock is for a relation */
+             elog(NOTICE, "Proc %d waits for %s on relation %u database %u; blocked by %d",
+                  info->pid,
+                  GetLockmodeName(info->lockmode),
+                  info->locktag.relId,
+                  info->locktag.dbId,
+                  nextpid);
+         }
+     }
+ }
+
+ /*
+  * RememberSimpleDeadLock: set up info for DeadLockReport when ProcSleep
+  * detects a trivial (two-way) deadlock.  proc1 wants to block for lockmode
+  * on lock, but proc2 is already waiting and would be blocked by proc1.
+  */
+ void
+ RememberSimpleDeadLock(PGPROC *proc1,
+                        LOCKMODE lockmode,
+                        LOCK *lock,
+                        PGPROC *proc2)
+ {
+     DEADLOCK_INFO  *info = &deadlockDetails[0];
+
+     info->locktag = lock->tag;
+     info->lockmode = lockmode;
+     info->pid = proc1->pid;
+     info++;
+     info->locktag = proc2->waitLock->tag;
+     info->lockmode = proc2->waitLockMode;
+     info->pid = proc2->pid;
+     nDeadlockDetails = 2;
+ }
*** src/backend/storage/lmgr/lock.c.orig    Thu Oct 31 19:40:23 2002
--- src/backend/storage/lmgr/lock.c    Thu Jan 16 15:33:29 2003
***************
*** 905,910 ****
--- 905,917 ----
           */
          LOCK_PRINT("WaitOnLock: aborting on lock", lock, lockmode);
          LWLockRelease(lockMethodTable->masterLock);
+         /*
+          * Now that we aren't holding the LockMgrLock, print details about
+          * the detected deadlock.  We didn't want to do this before because
+          * sending elog messages to the client while holding the shared lock
+          * is bad for concurrency.
+          */
+         DeadLockReport();
          elog(ERROR, "deadlock detected");
          /* not reached */
      }
*** src/backend/storage/lmgr/proc.c.orig    Thu Oct 31 16:34:16 2002
--- src/backend/storage/lmgr/proc.c    Thu Jan 16 15:34:28 2003
***************
*** 566,573 ****
                       * up correctly is to call RemoveFromWaitQueue(), but
                       * we can't do that until we are *on* the wait queue.
                       * So, set a flag to check below, and break out of
!                      * loop.
                       */
                      early_deadlock = true;
                      break;
                  }
--- 566,574 ----
                       * up correctly is to call RemoveFromWaitQueue(), but
                       * we can't do that until we are *on* the wait queue.
                       * So, set a flag to check below, and break out of
!                      * loop.  Also, record deadlock info for later message.
                       */
+                     RememberSimpleDeadLock(MyProc, lockmode, lock, proc);
                      early_deadlock = true;
                      break;
                  }
*** src/include/storage/lock.h.orig    Wed Sep  4 17:31:14 2002
--- src/include/storage/lock.h    Thu Jan 16 15:34:23 2003
***************
*** 243,248 ****
--- 243,253 ----
  extern void RemoveFromWaitQueue(PGPROC *proc);
  extern int    LockShmemSize(int maxBackends);
  extern bool DeadLockCheck(PGPROC *proc);
+ extern void DeadLockReport(void);
+ extern void RememberSimpleDeadLock(PGPROC *proc1,
+                                    LOCKMODE lockmode,
+                                    LOCK *lock,
+                                    PGPROC *proc2);
  extern void InitDeadLockChecking(void);
  extern LockData *GetLockStatusData(void);
  extern const char *GetLockmodeName(LOCKMODE mode);