How to do DB or table lock with webpy

Question

I am unable to find what I need to do in the following situation: transactions or locking or a combination? And how is this done using webpy?

I have the following situation: A user logs in via a browser, that user is allowed to make 1 update to the db via a post that is handled by the web server (webpy framework).

That user might login on multiple browsers to try to circumvent my security, so I would like to make sure that when the user post an update request, the following happens:

• lock required tables
• read data from table to make sure this user is still allowed to make this request
• if user is not allowed, unlock tables and return an error
• write table - make the requested change for this user
• write table - change user flag so user can not do this again
• unlock tables

When using single threaded app this would just be a simple implementation. but since I am using webpy I don't know for sure how to achieve this. Is it as simple as running my own commands? Or should it be enclosed in a single transaction? Or....?

Would the (pseudo) code be as simple as:

class posted():
  def POST(self):
    if userAllowed(): # userAllowed() reads session data that is set during login
      try:
        result=db.query('LOCK TABLES A write ,B write')
      except:            
        return 'DB Locking failed, please notify admin'

      res=db.query( table A for user flag)
      if res is not allowed:
        db.query('UNLOCK TABLES;')
        return 'You are not allowed to do this'

      db.update( table A, update flag to disallow future changes)
      db.update( table B with user request)
      db.query('UNLOCK TABLES;')
      return 'Your request has been handled, thank you.' 
    else:
      return 'You are not authorized to do anything like this'

Thank you.

Answer 1

A single POST() will run within a thread -- the POST() by another user (or from same user in another browser / tab) will run in a different thread or possibly separate process.

Simply put your items within a database transaction and it should do what you want.

t = db.transaction()
if db.query(table A for user flag):
    db.update( table A )
    db.update( table B )
    t.commit()
    return 'Your request has been handled, thank you'
t.rollback()
return 'You are not allowed to do this.'

If you're worried about locking the table "for so long", write a stored procedure to do the whole interaction, so python makes a single call to postgres. (often, query parsing, marshalling and unmarshalling the data takes longer than the actual query.)

and, of course surround with try block and do t.rollback() when necessary.