row locking

Web.py does not do row locking as far as we can tell. We have found that a tiny patch to web.select is desirable in order to do row locking. Row locking solves the read-modify-write problem, where another thread can read your row at the same time as you, and whoever commits last wins rather than having the transactions done in sequence. This is particularly critical with financial transactions.

The solution is to allow the words "FOR UPDATE" to be appended to the sql for a select statement. The lock is automatically released at the end of the transaction. Use it by adding the keyword argument to select(for_update=True). This works fine for postgresql (pg row locking docs). I haven't tried it on other databases.

The patch to the dev branch is just five lines of db.py as below:

=== modified file 'web/db.py'
--- web/db.py 2007-09-29 12:33:02 +0000
+++ web/db.py 2008-01-16 02:00:19 +0000
@@ -418,11 +418,14 @@
         return out

     def select(self, tables, vars=None, what='*', where=None, order=None, group=None,
- limit=None, offset=None, _test=False):
+ limit=None, offset=None, lock=False, _test=False):
         """
         Selects `what` from `tables` with clauses `where`, `order`,
         `group`, `limit`, and `offset`. Uses vars to interpolate.
         Otherwise, each clause can be a SQLQuery.
+ If lock is True, it adds "FOR UPDATE" to sql query
+ If lock is a string, it adds it to the sql query
+ (so that you can do lock='FOR UPDATE NOWAIT' or lock='FOR SHARE')

             >>> db = DB()
             >>> db.select('foo', _test=True)
@@ -433,6 +436,10 @@
         if vars is None: vars = {}
         sql_clauses = self.sql_clauses(what, tables, where, group, order, limit, offset)
         clauses = [self.gen_clause(sql, val, vars) for sql, val in sql_clauses if val is not None]
+ if lock is True:
+ lock = 'FOR UPDATE'
+ if lock:
+ clauses += [lock]
         qout = SQLQuery.join(clauses)
         if _test: return qout
         return self.query(qout, processed=True)