sqlalchemy 對于行級鎖有兩種實(shí)現(xiàn)方式，with_lockmode(self, mode): 和 with_for_update(self, read=False, nowait=False, of=None)，前者在sqlalchemy 0.9.0 被廢棄，用后者代替。所以我們使用with_for_update !

看下函數(shù)的定義：

@_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 是標(biāo)識(shí)加互斥鎖還是共享鎖. 當(dāng)為 True 時(shí), 即 for share 的語句, 是共享鎖. 多個(gè)事務(wù)可以獲取共享鎖, 互斥鎖只能一個(gè)事務(wù)獲取. 有'多個(gè)地方'都希望是'這段時(shí)間我獲取的數(shù)據(jù)不能被修改, 我也不會(huì)改', 那么只能使用共享鎖.nowait 其它事務(wù)碰到鎖, 是否不等待直接'報(bào)錯(cuò)'.of 指明上鎖的表, 如果不指明, 則查詢中涉及的所有表(行)都會(huì)加鎖.

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

對應(yīng)于sql：

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

mysql 不支持這幾個(gè)參數(shù)，轉(zhuǎn)成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())

結(jié)果：

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)的數(shù)據(jù)是判別就得要注意一下了。由于InnoDB 預(yù)設(shè)是Row-Level Lock，所以只有「明確」的指定主鍵，MySQL 才會(huì)執(zhí)行Row lock (只鎖住被選取的數(shù)據(jù)) ，否則mysql 將會(huì)執(zhí)行Table Lock (將整個(gè)數(shù)據(jù)表單給鎖住)。

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持好吧啦網(wǎng)。