On Dec 2, 2012, at 3:29 PM, YKdvd wrote: > 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.
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). -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
