deadlock problem - Mailing list pgsql-general

    Hello,

    I encountered a deadlock problem with an application last night,
and I was wondering if someone could help me come up with the best
resolution.

    If you don't want to read what the application is doing, skip to
PROBLEM.  I'm pretty sure I've got enough info here to answer any
questions you might have.


INTRODUCTION


    My application is a web-based photo album.

    The schema can be found here:
http://bleu.west.spy.net/~dustin/tmp/photo.sql.html (color by Vim)  An
automatically generated diagram of my schema can be found here:
http://bleu.west.spy.net/~dustin/tmp/photo.html (graphviz)  The app has
grown over several years, so a lot of the column names are confusing, so
don't look too hard.

    The application itself can be seen here:
http://bleu.west.spy.net/servlet/PhotoServlet

    There are a few things that go on behind the scenes.  The most
important and least obvious one here is an asynchronous logging daemon
thread.  As various events occur (logins, image requests, etc...), log
entry objects are created and added to a queue to be processed later, and
at a lower priority.

    When an image is uploaded, a transaction is begun to store various
bits of info about it.  First, a record is created in the ``album'' table,
then the new ID from ``album_id_seq'' is retrieved, and the image itself
is passed to the image server (on another machine responsible for
producing, sizing, caching, and scaling images).  The image data itself is
not stored in the database yet, but it needs to be, so a record is added
in the log (as part of this transaction) that indicates a new image has
been uploaded.  The ``extra_info'' column is used for this type of log to
state when the image has been stored, so the storer looks for records
where ``extra_info'' is null, stores them, and updates.

    Database is PostgreSQL 7.1.3 on i386-unknown-freebsd4.5, compiled
by GCC 2.95.3



PROBLEM



    The upload transaction creates a deadlock with the asynchronous
logger doing roughly the same query.  The upload transaction looks like
this:

    begin transaction

    insert into album(keywords, descr, cat, taken, size,
                        addedby, ts, width, height)
        values(?, ?, ?, ?, ?, ?, ?, ?, ?)

    select currval('album_id_seq')

    insert into photo_logs
        (log_type, photo_id, wwwuser_id, remote_addr, user_agent)
          values(get_log_type('Upload'), ?, ?, ?, get_agent(?))

    commit

    ...while the asynchronous logger will be issuing inserts that are
almost exactly like the last one (into ``photo_logs'').

    I have temporarily solved this problem by committing before the
insert to photo_logs and looping on deadlock, but this is obviously an
ugly solution that can cause me to lose data.

    It's not clear to me why this should deadlock.  I don't believe
any of the async log events that were being recorded were referencing the
new ID (and if they were, I'd think they'd fire an RI violation).
get_agent will insert a new record if the agent has never been seen
before, but this will not be the case while uploading an image (log events
would have already been recorded by the time you get to the form).

    Thanks in advance for the help.  If you need any more info (and
how *could* you?), let me know.

--
SPY                      My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________