SQLAlchemy和显式锁定 [英] SQLAlchemy and explicit locking

问题描述

我有多个进程，可能会在数据库中插入重复的行.这些插入不会很频繁地发生(每小时几次)，因此它对性能不是至关重要的.

I have multiple processes that can potentially insert duplicate rows into the database. These inserts do not happen very frequently (a few times every hour) so it is not performance critical.

在插入之前，我已经尝试过存在检查，就像这样:

I've tried an exist check before doing the insert, like so:

#Assume we're inserting a camera object, that's a valid SQLAlchemy ORM object that inherits from declarative_base...
try:
  stmt = exists().where(Camera.id == camera_id)
  exists_result = session.query(Camera).with_lockmode("update").filter(stmt).first()

  if exists_result is None:
    session.add(Camera(...)) #Lots of parameters, just assume it works
    session.commit()
except IntegrityError as e:
  session.rollback()

我遇到的问题是exist()检查不能锁定表，因此，多个进程可能会尝试同时插入同一对象.在这种情况下，一个过程通过插入成功，而其他过程则由于IntegrityError异常而失败.在这种情况下，对我来说感觉并不干净".

The problem I'm running into is that the exist() check doesn't lock the table, and so there is a chance that multiple processes could attempt to insert the same object at the same time. In such a scenario, one process succeeds with the insert and the others fail with an IntegrityError exception. While this works, it doesn't feel "clean" to me.

我真的很想在执行exists()检查之前锁定Camera表.

I would really like some way of locking the Camera table before doing the exists() check.

推荐答案

也许您可能对此很感兴趣:

Pehaps this might be of interest to you:

https://groups.google.com/forum/? fromgroups =#！topic/sqlalchemy/8WLhbsp2nls

您可以通过直接执行SQL来锁定表.我不确定在Elixir中是什么样子，但是在普通SA中会是这样:

You can lock the tables by executing the SQL directly. I'm not sure what that looks like in Elixir, but in plain SA it'd be something like:

 conn = engine.connect()
 conn.execute("LOCK TABLES Pointer WRITE")
 #do stuff with conn
 conn.execute("UNLOCK TABLES")

这篇关于SQLAlchemy和显式锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！