locking support on session/query (via select FOR UPDATE)

[Mike Bayer]

we dont have much going on with support for this right now. So lets
look at Hibernate and see what they do:

http://nhibernate.sourceforge.net/h2.0.3-docs/reference/html/transactions.html#transactions-s5 http://www.hibernate.org/hib_docs/v3/api/org/hibernate/Session.html#lock(java.lang.Object,%20org.hibernate.LockMode)

so they pretty much have a LockMode object that all the various
selecting methods can handle.

the changes I see to SA for this include:

• select(..., for_update=True) would also be able to take select (...., for_update="nowait"), which indicates "SELECT... FOR UPDATE NOWAIT" on Oracle.

• Session would get a "lock" method:

  def lock(self, object, lockmode, entity_name=None)

where lockmode is one of None, "read", "upgrade", "upgrade_nowait".
it also would cascade following cascade rules. for "read",
"upgrade", "upgrade_nowait", a SELECT is issued for the instance's
row using the appropriate FOR UPDATE NOWAIT; if the mapper
specifies a version_id column, that will be matched up against the
instance's version id.

• all the select/get methods on Query as well as Session.get/Session.load would get an extra keyword argument "lockmode" which also can specify any of those values. "upgrade" and "upgrade_nowait" would add the FOR UPDATE NOWAIT to the select statement issued (question: what should happen with lazy loaders attached to the loaded instance ? the "lock" cascade rule would have to propigate this into the lazyloaders.)

• Query gets a set_lock_mode() method that will apply the given lockmode indicator to all get/select operations.

[Former user]

adds for_update="nowait" to Oracle

[Former user]

add lockmode to the orm.query

[Former user]

second version of lockmode patch for orm/query

[Mike Bayer]

ive applied the latest patch, which is a combination of Andrija's, Mike's, and some mods of my own in changeset:1860

things to note about this patch:

• i streamlined the for_update_clause methods and had mysql/oracle fall back on that of ansisql's for when their specific keyword isnt found.

• the Query object cant use the lockmode keyword argument on the select_by/get_by methods; those methods are limited by the fact that all keyword arguments are used as query arguments. As an alternative, one should use the with_lockmode function I added:

  session.query(someclass).with_lockmode(read).select_by(x=5)

• I added an extra check for 'invalid lockmode' in changeset:1861

• sqlite doesnt support FOR UPDATE; it blanks out the field in changeset:1864

• I implemented the "version check" idea in changeset:1862/changeset:1863. this looks like:

  mapper(SomeClass, sometable, version_id_col=table.c.version_id) try: session.query(SomeClass).with_lockmode('read').get(5) except exceptions.ConcurrentModificationError: print "versions dont match"

we should see if these methods do the trick, then look into adding the lock method to Session so the ticket can be closed. thanks for all the help !

[Mike Bayer]

this is partially implemented but the session methods are not there yet.

[Mike Bayer]

most of this ticket has been completed for many weeks now; moving off the session.lock() method off to #568.

[Mike Bayer]

Removing milestone: 0.4.0 (automated comment)