On Wed, Apr 08, 2009 at 06:23:39PM +0200, Andreas Mock wrote: [...] >> I was pleased to see that there's some support for savepoints, which >> leads to the possibility of finally having an atomic create_or_update(). > I'm interested in that. How are you doing it? > Why do you need savepoints to do a atomic update-or-create? > I'm interested in the theory behind that.
Note that I'm doing this on PostgreSQL; other databases may well do things differently. For example, MySQL's REPLACE INTO covers similar ground. The current implementation does a SELECT followed by an INSERT or UPDATE as appropriate. This introduces a race condition. The whole thing is a critical section and needs to be wrapped in a transaction or savepoint. I implemented it by starting a savepoint, then just trying the INSERT. If that fails (normally due to duplicate UNIQUE keys) then the savepoint is rolled back and the SELECT and UPDATE is done as before. After the UPDATE, the savepoint is committed. One slight perk is that my benchmarks show that using this to insert new rows is about 50% faster; the downside is that if the row already exists, the performance drops by 30%. This is intuitive: we've swapped two queries for a single query to insert or three to update. However, I'm not quite sure this trick completely retains the semantics of create_or_update, so it wouldn't be a drop-in replacement. Never mind that my code doesn't work *outside* of a transaction, and probably doesn't work on all of the supported database engines. I also suspect that while savepoints may be necessary, they're not sufficient, and one also needs SELECT FOR UPDATE. How might I express that in DBIC? _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk