pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Deadlocks?  What happened to MVCC?
4+ messages / 3 participants
[nested] [flat]

* Deadlocks?  What happened to MVCC?
@ 2001-08-14 19:15 Josh Berkus <josh@agliodbs.com>
  2001-08-14 20:46 ` Re: Deadlocks?  What happened to MVCC? Jan Wieck <JanWieck@Yahoo.com>
  2001-08-14 21:35 ` Re: Deadlocks? What happened to MVCC? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Josh Berkus @ 2001-08-14 19:15 UTC (permalink / raw)
  To: pgsql-sql

Folks:

I'm getting deadlock errors on one of the operations on my web
application.  It's a function which adds a large number of rows to a
holding table, then updates that set of rows multiple times in order to
present scoring information to the user.

However, the function is deadlocking itself once it's in heavy use.  I
though that MVCC was supposed to supercede deadlocks, unless I
specifically set locks, which I'm not.

Help, anyone?

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Deadlocks?  What happened to MVCC?
  2001-08-14 19:15 Deadlocks?  What happened to MVCC? Josh Berkus <josh@agliodbs.com>
@ 2001-08-14 20:46 ` Jan Wieck <JanWieck@Yahoo.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Jan Wieck @ 2001-08-14 20:46 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Josh Berkus wrote:
> Folks:
>
> I'm getting deadlock errors on one of the operations on my web
> application.  It's a function which adds a large number of rows to a
> holding table, then updates that set of rows multiple times in order to
> present scoring information to the user.
>
> However, the function is deadlocking itself once it's in heavy use.  I
> though that MVCC was supposed to supercede deadlocks, unless I
> specifically set locks, which I'm not.
>
> Help, anyone?

    You   must   have  misunderstood  something.  MVCC  does  not
    supersede deadlocks, nor can it prevent them. All  MVCC  does
    is to help "readers" not beeing blocked by "writers" and vice
    versa, by avoiding the need for  shared  "read"  locks.   But
    writers  still  need  locks,  so  if  your  application  does
    something like

        Xact-1: Starts transaction
        Xact-2: Starts transaction
        Xact-1: Updates row A
        Xact-2: Updates row B
        Xact-1: Wants to update row B (blocks)
        Xact-2: Wants to update row A

    then it's a deadlock. A deadlock is a deadlock and remains to
    be a deadlock, no matter if you have versioning or not.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Deadlocks? What happened to MVCC?
  2001-08-14 19:15 Deadlocks?  What happened to MVCC? Josh Berkus <josh@agliodbs.com>
@ 2001-08-14 21:35 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-08-14 22:44   ` Re: Deadlocks? What happened to MVCC? Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2001-08-14 21:35 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

MVCC does not mean "no locks" ... particularly not when UPDATEs are
involved.  You'll need to be more specific about what your function is
doing, but my first thought would be to look for the possibility of
conflicting updates of the same row.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Deadlocks? What happened to MVCC?
  2001-08-14 19:15 Deadlocks?  What happened to MVCC? Josh Berkus <josh@agliodbs.com>
  2001-08-14 21:35 ` Re: Deadlocks? What happened to MVCC? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-08-14 22:44   ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2001-08-14 22:44 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

Tom, Jan,

> MVCC does not mean "no locks" ... particularly not when UPDATEs are
> involved.  You'll need to be more specific about what your function
> is
> doing, but my first thought would be to look for the possibility of
> conflicting updates of the same row.

Found the problem after 3 hours and a phone call to PostgreSQL Inc.
Turns out that I had one too-broad UPDATE in a 250-line function ...
feh!

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-08-14 22:44 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-14 19:15 Deadlocks?  What happened to MVCC? Josh Berkus <josh@agliodbs.com>
2001-08-14 20:46 ` Jan Wieck <JanWieck@Yahoo.com>
2001-08-14 21:35 ` Re: Deadlocks? What happened to MVCC? Tom Lane <tgl@sss.pgh.pa.us>
2001-08-14 22:44   ` Re: Deadlocks? What happened to MVCC? Josh Berkus <josh@agliodbs.com>

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