Application locking and SQLAlchemy

[YKdvd]

I'm starting to use SQLAlchemy to access a small, in-house database.  I need to add some rudimentary concurrency handling, and just wanted to see if my general impression as to the SQLAlchemy implications are correct.  As a hypothetical  example, assume something like a Users table, and some sort of grid-like editing display displaying multiple rows.  My intention is to create a "Locks" table, which identifies the table/primaryID/lockObtainedTimestamp of a locked row in a data table like Users.  An editing routine would have to acquire locks for the rows it wished to edit.  There's three basic cases:

1) Exclusive editor - I'd just obtain a Locks on some unique identifier specific to, say, editing Users, and only one editor would be allowed to update Users at a time.  This is just a simple semaphore, and once obtained I can do all my SQLAlchemy stuff freely, since it is assumed nothing else will write to the data table.  The existing non-DB scheme does this, although I'll be able to change this to case #2.

2) Entire grid - once the desired subset of records have been retrieved (perhaps all Users in a particular group or branch office), the grid enables all records to be modified, with a single "Update" once finished.  Here I'd have to loop through the Users returned from my SQLAlchemy query, and obtain a Locks for each one (one Locks row per User).  If successful, I'd then have to call refresh() for each of the objects (just in case something updated between query and obtaining the lock).

3) Single row - only one grid row is edited/saved at a time, so I'd just obtain a Locks for it and refresh() the single object before editing. 

I'm pretty sure I know what I need to make the Locks table work, but I just wanted to be sure that refresh() is the right SQLAlchemish thing to do in this situation.  This would be low-volume, in-house sort of thing, and this type of basic pessimistic locking is acceptable over trying to resolve an editing conflict using the built-in optimistic version_id_col / StaleDataError features, although that may be useful in places.

[Michael Bayer]

refresh() is usually not the right way to go, as most relational databases will have you sitting in a transaction that has some degree of isolation from what's going on outside, so the SQLAlchemy way is to think in terms of transactions and units of work. The Session expires all state after a transaction ends, and queries in the subsequent transaction will re-query the database to get the most recent state. The simplest way to build a system like this is to create a Session to handle reading/acquiring locks, run through the lock rows needed, then commit. If you'd like to prevent any particular "lock" operation from interfering with another, you can run the transaction on that particular Session with serializable isolation, which is available as an execution option on Connection (See http://docs.sqlalchemy.org/en/rel_0_8/core/connections.html?highlight=execution_options#sqlalchemy.engine.Connection.execution_options).

[YKdvd]

D'oh!  And I had just been reading the docs about a commit expiring loaded object instances, which is what the refresh() would have done.  So the mere accessing of that User object instance (say, UserObj.name=newNameFromEdit, or temp=UserObj.name) would first trigger a reload of that User from the database, once I've committed my lock-obtaining inserts to the Locks table based on an initial Users query.  

By the way, add me to the list of grateful SQLAlchemy users.  I've played with Doctrine in PHP a bit, and if nothing else the SQLA documentation is miles better.  As a starter it is mainly a magical way of replacing writing a pile of tedious CRUD, and as someone who has hazy memories of things like the original Btrieve, an ORM is almost unsporting... :)