sql - Derby中带有WHERE子句的SQL UPDATE语句的原子性

Question

我正在编写一个(Java)函数，该函数将在多个进程中运行，该函数从Derby SQL数据库中选择一个随机用户名。我不想一次使用相同的用户名使用多个进程，因此我想添加一个“标志”列来充当锁。我在事务中读了the documentation时说，每个语句都是原子的，但是我对逻辑/知识的真正意义不是100％自信。

我的策略是首先选择一个随机用户名(例如，我有一个表usernames和username和flag列):

SELECT username FROM usernames WHERE flag='0' ORDER BY RANDOM() FETCH FIRST 1 ROW ONLY;

然后将检索到的用户名传递给此用户名(通过准备好的语句):

UPDATE usernames SET flag='1' WHERE username=? AND flag='0';

并保存返回值(更新的行数)。如果更新了1行，则继续使用所选的用户名。如果没有行进行更新，请重新开始并重试。

这是一种可靠的锁定机制，可以确保没有两个进程以相同的用户名结尾吗？例如。如果两个进程从 SELECT语句中获取了相同的用户名，是否可以保证只有一个进程将从 UPDATE中获得1的结果？还是我误解了一个声明是原子的意味着什么？

Answer 1

首先，我会使用 bool(boolean) 值以外的其他值，例如当前使用名称的进程的进程ID。这样，调试和释放由进程崩溃导致的伪造的flag = true锁变得更加容易。

Derby支持满足ACID属性的事务。事务可以包含任意数量的语句。例如。:

conn.setAutoCommit(false); // true by default

try {
  // execute select
  // execute update
} catch (SQLException sqle) {
  conn.rollback() // abort transaction and undo all effects
}
conn.commit();    // commit transaction, make changes visible 

请注意，确切的行为由为连接设置的 isolation level控制。默认值为READ COMMITTED。

如果以此方式进行操作，则无需测试更新是否成功，因为事务处理机制将设置必要的锁，以确保在事务处理结束之前，您选择的用户名不会被其他人修改。

请注意，在您的情况下，可能不需要回滚，因为只有更新具有需要撤消的可见效果，并且更新语句本身是原子的。