row locking

[Berwyn Hoyt]

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)

[Anand Chitipothu (anandology)]

AFAIK only postgres supports it.

[Berwyn Hoyt (berwyn)]

Not true. MySQL supports the FOR UPDATE select clause, and it does row or page locking depending on whether you are using InnoDB or BDB storage. See http://dev.mysql.com/doc/refman/5.0/en/select.html

Oracle also seems to support "FOR UPDATE" for row locking: http://www.dba-oracle.com/t_locks_row_level_locking.htm

I don't know about other dbases.

[Berwyn Hoyt (berwyn)]

Oh yes. While "FOR UPDATE" seems to be consistent across various databases as mentioned above, they can have different variants for more specific cases. That is why the patch allows the user to pass a string override in the lock variable. This allows pgsql users to use the alternate "FOR SHARE" option and mysql users to use the equivalent "LOCK IN SHARE MODE", etc.