When inserting multiple images at the same time, we could deadlock -- inserting

new rows into images would (due to the foreign key constraint) lock the corresponding
row in event using a shared lock, then the update (due to last_picture) would
upgrade the lock to exclusive, causing a deadlock later. To fix this, we move
last_picture into its own table; we could also lock it explicitly earlier, but
that would stall other transactions (and they can run for 10-15 seconds or so,
or perhaps even longer) so it is not desirable.

diff --git a/perl/Sesse/pr0n/Common.pm b/perl/Sesse/pr0n/Common.pm
index ecc039e15d8144cb9443891c0929d7cbf8e0d5e8..d03f46f1f01a01a10b656bb0efeb53aeb1adfb6e 100644 (file)
--- a/perl/Sesse/pr0n/Common.pm
+++ b/perl/Sesse/pr0n/Common.pm
@@ -192,7 +192,7 @@ sub update_width_height {
 
        # update the last_picture cache as well (this should of course be done
        # via a trigger, but this is less complicated :-) )
 
        # update the last_picture cache as well (this should of course be done
        # via a trigger, but this is less complicated :-) )

-       $dbh->do('UPDATE events SET last_picture=GREATEST(last_picture, ?) FROM images WHERE event=events.id) WHERE id=(SELECT event FROM images WHERE id=?)',
+       $dbh->do('UPDATE last_picture_cache SET last_picture=GREATEST(last_picture, ?) FROM images WHERE event=events.id) WHERE id=(SELECT event FROM images WHERE id=?)',

                undef, $datetime, $id)
                or die "Couldn't update last_picture in SQL: $!";
 }
                undef, $datetime, $id)
                or die "Couldn't update last_picture in SQL: $!";
 }

diff --git a/perl/Sesse/pr0n/Listing.pm b/perl/Sesse/pr0n/Listing.pm
index 8447040a760421e50718afc3a88f71142766f22a..041e9568046e3625f7820d695a9515b9b418326f 100644 (file)
--- a/perl/Sesse/pr0n/Listing.pm
+++ b/perl/Sesse/pr0n/Listing.pm
@@ -18,7 +18,7 @@ sub handler {
 
 #      my $q = $dbh->prepare('SELECT t1.id,t1.date,t1.name FROM events t1 LEFT JOIN images t2 ON t1.id=t2.event WHERE t1.vhost=? GROUP BY t1.id,t1.date,t1.name ORDER BY COALESCE(MAX(t2.date),\'1970-01-01 00:00:00\'),t1.id') or
 #              dberror($r, "Couldn't list events");
 
 #      my $q = $dbh->prepare('SELECT t1.id,t1.date,t1.name FROM events t1 LEFT JOIN images t2 ON t1.id=t2.event WHERE t1.vhost=? GROUP BY t1.id,t1.date,t1.name ORDER BY COALESCE(MAX(t2.date),\'1970-01-01 00:00:00\'),t1.id') or
 #              dberror($r, "Couldn't list events");

-       my $q = $dbh->prepare('SELECT id,date,name FROM events WHERE vhost=? ORDER BY last_picture DESC')
+       my $q = $dbh->prepare('SELECT id,date,name FROM events e JOIN last_picture_cache c ON e.id=c.event WHERE vhost=? ORDER BY last_picture DESC')

                or dberror($r, "Couldn't list events");
        $q->execute($r->get_server_name)
                or dberror($r, "Couldn't get events");
                or dberror($r, "Couldn't list events");
        $q->execute($r->get_server_name)
                or dberror($r, "Couldn't get events");

diff --git a/sql/pr0n.sql b/sql/pr0n.sql
index c1349e7e4e5aea9ad58c382870bf70d653c28b54..9d1f9374d3bae4e86a9fbd92dfa288d17cfb8c77 100644 (file)
--- a/sql/pr0n.sql
+++ b/sql/pr0n.sql
@@ -2,8 +2,13 @@ CREATE TABLE events (
     id character varying NOT NULL PRIMARY KEY,
     date character varying NOT NULL,
     name character varying NOT NULL,
     id character varying NOT NULL PRIMARY KEY,
     date character varying NOT NULL,
     name character varying NOT NULL,

-    vhost character varying NOT NULL,
-    last_picture timestamp without time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL
+    vhost character varying NOT NULL
+);
+
+-- In a separate table to avoid deadlocks.
+CREATE TABLE last_picture_cache ( 
+   event varchar PRIMARY KEY references events ( id ),
+   last_picture timestamp without time zone

 );
 
 CREATE TABLE images (
 );
 
 CREATE TABLE images (