FAQ
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

Search Discussions

  • Jan Wieck at Aug 14, 2001 at 8:37 pm

    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
  • Tom Lane at Aug 14, 2001 at 9:35 pm
    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
  • Josh Berkus at Aug 14, 2001 at 10:44 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-sql @
categoriespostgresql
postedAug 14, '01 at 7:15p
activeAug 14, '01 at 10:44p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2018 Grokbase