Python Sqlalchemy如何实现select for update

作者:笨手笨脚越 时间:2022-01-23 02:26:31 

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

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

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

标签:Python,Sqlalchemy,select,for,update
0
投稿

猜你喜欢

  • 在 Golang 中实现一个简单的Http中间件过程详解

    2024-05-09 14:55:35
  • Windows下pycharm安装第三方库失败(通用解决方案)

    2022-06-07 22:54:37
  • [翻译]标记语言和样式手册 Chapter 13 为文字指定样式

    2008-02-15 16:08:00
  • python 实现网易邮箱邮件阅读和删除的辅助小脚本

    2022-03-17 10:55:34
  • Python实现随机生成图片验证码详解

    2023-02-26 12:20:20
  • Python 高级专用类方法的实例详解

    2023-10-11 14:13:52
  • 纯CSS Tooltips提示

    2008-10-18 16:01:00
  • python 串口读取+存储+输出处理实例

    2023-03-30 01:46:26
  • Python中import语句用法案例讲解

    2023-08-07 05:33:47
  • 让长字符自动换行 比如URL和Email地址等

    2008-07-01 14:41:00
  • 三达不溜:www

    2009-03-28 11:44:00
  • Python根据指定日期计算后n天,前n天是哪一天的方法

    2022-12-27 19:13:50
  • vue-router路由懒加载和权限控制详解

    2024-04-27 15:51:24
  • python装饰器初探(推荐)

    2023-01-19 14:40:27
  • IE和Firefox下event事件杂谈

    2024-04-22 13:25:33
  • php判断正常访问和外部访问的示例

    2024-05-11 09:45:46
  • Python Web版语音合成实例详解

    2021-11-28 04:37:20
  • python中通过pip安装库文件时出现“EnvironmentError: [WinError 5] 拒绝访问”的问题及解决方案

    2023-08-29 20:25:40
  • HTML中的setCapture和releaseCapture使用介绍

    2024-04-18 09:51:18
  • Python代码实现KNN算法

    2023-05-07 10:45:36
  • asp之家 网络编程 m.aspxhome.com