Обсуждение: Deadlocks? What happened to MVCC?

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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

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
rowB       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

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

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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco