Forum OpenACS Q&A: Re: PostgreSQL MVCC transaction model

Collapse
Posted by Mark Aufflick on
Damn - this is a gotcha. It doesn't match up with my prior belief about transactions.

For instance in a piece of code I recently wrote, I do something like:

db_transaction {
  db_0or1row get {
   select id, task from queue limit 1
  }

  do_task $task
  # if this throws an error the transaction will be aborted

  db_dml del {
    delete from queue where id = :id
  }
}
This queue handler is run from various places in code as well as a cron script. My belief was that I was protected from race conditions by wrapping the work in a transaction, but it appears that this is not the case?

Collapse
Posted by Andrew Piskorski on
Mark, I would always do a "select for update" in code like that.

Your code also seems a little wonky, in that you apparently have a queue of rows, but when an item is complete rather than doing the normal thing and updating a "done_p" flag column, you're deleting it from the table. Hard to say withou knowing more about your application, but that's also probably a problem. Unless I had a very good reason to do otherwise, I would probably update the row, not delete it; then if necessary run a sweeper job at some later point to archive or delete out old obsolete completed rows.

Collapse
Posted by russ m on
Adding a status column also lets you set the task to 'in progress' straight after it's been selected, reducing the window during which another thread could try and run the task only to be rolled back when the final delete/update fails.
Collapse
Posted by Mark Aufflick on
That code (actually, a perl version of it) is being used for a telco internal provisioning system. There is never any user feedback, and it could potentially take hundreds of tasks a minute - maybe more. Still, I like the idea of keeping some history - thanks for the input!