SQLAlchemy和显式锁定

Question

我有多个进程可能会向数据库中插入重复的行。这些插入操作不是非常频繁（每小时几次），因此性能不是关键问题。

我已经尝试在执行插入操作之前进行存在性检查，具体做法如下：

#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()检查不会锁定表，因此有可能多个进程同时尝试插入相同的对象。在这种情况下，一个进程成功插入，而其他进程则以完整性错误异常失败。虽然这样可以工作，但我觉得不够“干净”。

我真的很希望在进行exists()检查之前，有一种锁定Camera表的方法。

- CadentOrange

---

为什么不直接使用UNIQUE约束，尝试插入新行并在失败时捕获IntegrityError？这种方法有什么不妥之处吗？ - Audrius Kažukauskas

这是我目前的做法，就像代码中所示。它可以工作，但是主键计数器每次都会增加。因此，最终我得到的主键不是连续的。如果可能的话，我想避免这种情况。 - CadentOrange

Answer 1

也许这对您有所帮助：

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

您可以通过直接执行SQL来锁定表。我不确定在Elixir中是什么样子，但在普通的SA中，它可能是这样的：

 conn = engine.connect()
 conn.execute("LOCK TABLES Pointer WRITE")
 #使用conn进行操作
 conn.execute("UNLOCK TABLES")

- AlexVhr

---

并没有什么帮助，因为我已经在exists()查询中指定了“with_lockmode（“update”）”，它应该锁定了表格。 - CadentOrange

SELECT..FOR UPDATE 不会锁定整张表，只会锁定符合 SELECT 条件的那些行，对于尚不存在的行进行 INSERT 是没有用处的。你需要使用像 "LOCK TABLE" 这样的 SQL，但这取决于你使用的数据库。 - zzzeek

抱歉，我的意思是：您可以通过直接执行SQL来锁定表。我不确定在Elixir中看起来像什么，但在普通的SA中，它可能是这样的： conn = engine.connect() conn.execute("LOCK TABLES Pointer WRITE") ... 使用conn进行操作 conn.execute("UNLOCK TABLES") - AlexVhr

@AlexVhr，OP对您提供的链接感到困惑，这也是为什么SO不鼓励只有链接的答案的原因。当您提到链接时，请引用相关上下文以便他们知道您在谈论什么。 - Mike Pennington