SQL事务，插入后在插入成功时更新

Question

我想做以下事情：

• 表A只有一列，里面是一些独特的数字（这些数字不是连续的）
• 表B有两列，一列是id（独特的非连续整数），另一列是hit_number（正整数）

给定一个数字和一个id，我想要：

检查这个数字是否在表A中。

• 如果在，那就什么都不做，只需要告诉我一声
• 如果不在，就把它插入到表A中，同时把表B中对应这个id的hit_number加一，并告诉我。

这个操作需要在一个事务中完成，如果有两个同时的连接使用相同的值，hit_number只应该增加一次（所以我想我需要一些隔离）。

我想出了一个解决方案（在这个例子中我使用的是sqlite和python），但我不确定这个方案是否足够好：

大概是这样的

con = sqlite3.connect(":memory:", isolation_level="EXCLUSIVE")
con.execute("create table A (num integer primary key);")
con.execute("create table B (user_id integer primary key, hit_number integer);")

# ... fill it with something

def hit(v, id, con):
    try:
        with con:
           con.execute("INSERT INTO A VALUES (?);" +
                       "UPDATE B SET hit_number=hit_number+1 WHERE user_id=(?);", (v, id))
    except sqlite3.IntegrityError:
       return False
    return True

这个事务是原子的（atomic）吗？我真的需要EXCLUSIVE吗，还是说使用更低的隔离级别也能得到相同的效果？有没有更好的方法？

Answer 1

使用.execute()方法时，每次只能执行一个语句。在一个事务中，没必要在一次调用中执行两个语句；如果这样做，会出现完整性错误的异常，这会导致事务被中止：

try:
    with con:
       con.execute("INSERT INTO A VALUES (?);", (v,))
       con.execute("UPDATE B SET hit_number=hit_number+1 WHERE user_id=(?);", (id,)
except sqlite3.IntegrityError:
   return False
return True