title: SQlalchemy database level locking
body: <p>I wonder if it is possible to somehow to lock a couple of database tables for protected write or something like that, to prevent another application to modify these when a transaction is in progress?</p>
  
  <p>I have now something like this. Tables A, B and C, with one to many relations between them A->B and B->C. This function receives data from rabbitmq and either updates A, B and/or C (usually only C) or creates new rows if missing.</p>
  
  <pre><code>Session=scoped_session(session_factory)
  try:
      foo = Session.query(A).filter(....).one()
  except NoResultFound:
      Session.remove()
      return
  
  try:
      bar = Session.query(B).filter(......).one()
  except NoResultFound:
      bar = B(field1=x, field2=y etc.)
      Session.add(bar)
  
  try:
      xyzzy = Session.query(C).filter(...).order_by(...).limit(1).one()
  except NoResultFound:
      xyzzy = C(.......)
      Session.add(xyzzy)
  
  foo.fieldn = var1
  bar.fieldn = var2
  xyzzy.fieldn = var3
  etc. 
  
  Session.commit()
  Session.remove()
  </code></pre>
  
  <p>This all works fine.  The problem is, I have another program (entirely different python script) that does a cleanup on request. It basically deletes everything from A, B and C. This also works. </p>
  
  <p>My first program crashes immediately after a deletion to this:</p>
  
  <pre><code>sqlalchemy.orm.exc.StaleDataError: UPDATE statement 
  on table 'C' expected to update 1 row(s); 0 were matched.
  </code></pre>
  
  <p>I can catch this exception and react accordingly, but do I need to?  This problem would be solved if I could in both programs reserve A, B and C on <em>database</em> level for a short file. All these transactions are short in duration.   I understand from sqlalchemy documentation that a session should also start a transaction, but apparently this means something different than a transaction on a database level. </p>
  
  <p>What seems to happen is that my Session.query(C) finds a row but before the first program issues Session.commit(), the other program has deleted it. </p>
  
  <p>20+ years ago with 90's databases and C I always started a transaction with a DECLARE TRANSACTION ... RESERVING A,B,C FOR PROTECTED WRITE; or something like that.  Is that gone now and I just need to catch the exception, or can I still benefit from locking on database level?</p>
  
  <p>Hannu</p>