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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/U-jpoIxGoNoJ. 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.
