Seemingly trivial Bug/Archive updates/inserts timeout

[Ian Booth]

We have seem intermittent, and quite sporadic, timeouts doing what should be very simple update or insert operations.

eg

Bug 1048984, Bug 618349

 Time Reps Database id Statement
1. 10171.0 1 SQL-main-master
INSERT INTO BugMessage (bugwatch, OWNER, INDEX, message, bug, remote_comment_id)
VALUES (NONE, $INT ... $INT, NONE) RETURNING BugMessage.id

Bug 1028105

UPDATE Bug SET date_last_updated=%s, description=%s WHERE Bug.id = %s

Bug 1059853

UPDATE Bug
SET heat=calculate_bug_heat($INT), heat_last_updated=CURRENT_TIMESTAMP AT TIME ZONE $STRING
WHERE Bug.id = $INT

Bug 911976

INSERT INTO
Karma ("action", datecreated, person, sourcepackagename, distribution, product)
VALUES (%s, CURRENT_TIMESTAMP AT TIME ZONE 'UTC', %s, %s, %s, %s)
RETURNING Karma.id

The suspicion is that locks being held are blocking things, most probably by scripts, many of which exhibit very naughty behaviour in this area.
More diagnosis is needed to establish what is locked at the time of these sorts of occurrence and what initiated the locks.

[William Grant (wgrant)]

This will be fixed by PostgreSQL 9.3's KEY SHARE locks, even if we don't fix the scripts to use more sensible transaction lengths.