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 (将整个数据表单给锁住)。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。