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>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

Python Sqlalchemy如何实现select for update

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

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

Python Sqlalchemy如何实现select for update

扫一扫手机访问