Deadlock between select and truncate (postgresql)

Question

Table output_values_center1 (and some other) inherits output_values. Periodically I truncate table output_values_center1 and load new data (in one transaction). In that time user can request some data and he got error message. Why it ever happens (select query requests only one record) and how to avoid such problem:

2010-05-19 14:43:17 UTC ERROR:  deadlock detected
2010-05-19 14:43:17 UTC DETAIL:  Process 25972 waits for AccessShareLock on relation  2495092 of database 16385; blocked by process 26102.
    Process 26102 waits for AccessExclusiveLock on relation 2494865 of database 16385; blocked by process 25972.
    Process 25972: SELECT * FROM "output_values" WHERE ("output_values".id = 122312)  LIMIT 1
    Process 26102: TRUNCATE TABLE "output_values_center1"

Answer 1

"TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on the table. If concurrent access to a table is required, then the DELETE command should be used instead."

Obviously it's not clear if you just look at the "manpage" linked above why querying the parent table affects its descendant. The following excerpt from the "manpage" for the SELECT command clarifies it:

"If ONLY is specified, only that table is scanned. If ONLY is not specified, the table and any descendant tables are scanned."

Answer 2

I'd try this (in pseudocode) for truncating:

#define NOWAIT_TIMES 100
#define SLEEPTIME_USECS (1000*100)
for ( i=0; ; i++ ) {
  ok = query('start transaction');
  if ( !ok ) raise 'Unable to start transaction!';
  queries = array(
    'lock table output_values in access exclusive mode nowait',
    'truncate output_values_center1',
    'commit'
  );
  if ( i>NOWAIT_TIMES ) {
    // we will wait this time, as we tried NOWAIT_TIMES and failed
    queries[0] = 'lock table output_values in access exclusive mode';
  }
  foreach q in queries {
     ok = query(q);
     if (!ok) break;
  }
  if (!ok) {
    query('rollback');
    usleep(SLEEPTIME_USECS);
  } else {
    break;
  };
};

This way you'll be safe from deadlocks, as parent table will be exclusively locked. A user will just block for a fraction of second while truncate runs and will automatically resume after commit.

But be prepared that this can run several seconds on busy server as when table is in use then lock will fail and be retried.