Python Sqlalchemy如何实现select for update

sqlalchemy 对于行级锁有两种实现方式，with_lockmode(self, mode): 和 with_for_update(self, read=False, nowait=False, of=None)，前者在sqlalchemy 0.9.0 被废弃，用后者代替。所以我们使用with_for_update !

看下函数的定义：

@_generative()
 def with_for_update(self, read=False, nowait=False, of=None):
   """return a new :class:`.Query` with the specified options for the
   ``FOR UPDATE`` clause.

The behavior of this method is identical to that of
   :meth:`.SelectBase.with_for_update`. When called with no arguments,
   the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause
   appended. When additional arguments are specified, backend-specific
   options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE``
   can take effect.

E.g.::

q = sess.query(User).with_for_update(nowait=True, of=User)

The above query on a Postgresql backend will render like::

SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT

.. versionadded:: 0.9.0 :meth:`.Query.with_for_update` supersedes
     the :meth:`.Query.with_lockmode` method.

.. seealso::

:meth:`.GenerativeSelect.with_for_update` - Core level method with
     full argument and behavioral description.

"""

read
 是标识加互斥锁还是共享锁. 当为 True 时, 即 for share 的语句, 是共享锁. 多个事务可以获取共享锁, 互斥锁只能一个事务获取. 有"多个地方"都希望是"这段时间我获取的数据不能被修改, 我也不会改", 那么只能使用共享锁.
nowait
 其它事务碰到锁, 是否不等待直接"报错".
of
 指明上锁的表, 如果不指明, 则查询中涉及的所有表(行)都会加锁.

q = sess.query(User).with_for_update(nowait=True, of=User)

对应于sql：

SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT

mysql 不支持这几个参数，转成sql都是:

SELECT users.id AS users_id FROM users FOR UPDATE

范例：

def query_city_for_update():
 session = get_session()
 with session.begin():
   query = session.query(City).with_for_update().filter(City.ID == 8)
   print 'SQL : ％s' ％ str(query)
   print_city_info(query.first())

结果：

SQL : SELECT city."ID" AS "city_ID", city."Name" AS "city_Name", city."CountryCode" AS "city_CountryCode", city."District" AS "city_District", city."Population" AS "city_Population"
FROM city
WHERE city."ID" = :ID_1 FOR UPDATE

{'city': {'population': 234323, 'district': u'Utrecht', 'id': 8, 'country_code': u'NLD', 'name': u'Utrecht'}}

SELECT ... FOR UPDATE 的用法，不过锁定(Lock)的数据是判别就得要注意一下了。由于InnoDB 预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则mysql 将会执行Table Lock (将整个数据表单给锁住)。

来源：https://www.jianshu.com/p/7e4de9ab942c