我有两个应用程序运行相同的代码，在hibernate的帮助下查询相同的Oracle数据库
有一张表，用来存放要发送的电子邮件
这两个应用程序都运行一个调度程序，在该表的一列中查询"QUEUED"字符串（标记要发送的电子邮件），创建并发送电子邮件，最后将"QUEUED"更新为"sent"，这样这些电子邮件就不会再发送了
理论上，我希望一个应用程序读取一些行，锁定它们，使其无法读取和写入，更新它们并解锁以供另一个应用使用
出于这个原因，我使用以下查询：
String jpql = "SELECT m FROM Email m WHERE m.status = :status";
return em.createQuery(jpql, Email.class)
.setParameter("status", "QUEUED")
.setLockMode(LockModeType.PESSIMISTIC_WRITE)
.getResultList();
两个应用程序都使用，根据文档，LockModeType.PESSIMISTIC_WRITE相当于"SELECT FOR UPDATE"
相反，会发生以下行为：
13:06:02,160 | MailQueueMonitor_1| Found 0 email(s) to be sent. // No rows returned from app1
13:06:03,813 | MailQueueMonitor_2| Found 0 email(s) to be sent. // No rows returned from app2
13:06:12,180 | MailQueueMonitor_1| Found 1 email(s) to be sent. // 1 mail returned from app1
13:06:12,190 | MailQueueMonitor_1| Mailer will sleep for 30s // App1 will sleep for 30s
// At this point, app2 tries to execute query but freezes as app1 has the keys to the rows
13:06:42,191 | MailQueueMonitor_1| Mailer woke up and will try to send mails // App1 wakes up
13:06:46,796 | MailQueueMonitor_1| Mailer sent mail // App1 sent mail
13:06:46,798 | MailQueueMonitor_1| Mailer changed mail status to SENT // App1 update status from QUEUED to SENT
// At this point, app1 releases the locks and app2 unfreezes and executes query looking for QUEUED rows which should not exist at this point since they where updated to SENT.
13:06:46,809 | MailQueueMonitor_2| Found 1 email(s) to be sent. // App2 queries and finds 1 row! It is like it queried a snapshot of the database before app2 updated all rows.
13:06:46,836 | MailQueueMonitor_2| Mailer will sleep for 30s // App2 will sleep for 30s
13:07:16,836 | MailQueueMonitor_2| Mailer woke up and will try to send mails // App2 wakes up
13:07:21,457 | MailQueueMonitor_2| Mailer sent mails // App2 sent mail. This is re-senting above email occuring to duplicate emails.
13:07:21,458 | MailQueueMonitor_2| Mailer changed mail status SENT // App2 update status from QUEUED to SENT, again!
问题是，为什么app2不读取更新的行，即使查询是在锁释放后执行的。为什么app2尝试查询锁定的行时不引发异常？我应该如何锁定行以防止读取或更新，并且在锁定释放后，下一个查询数据库的应用程序将看到更新的数据？
一些注意事项：
1.如果我在连续的两行上运行两次查询，当锁被释放时，以前被锁定的应用程序将执行第一个查询（即被锁定的查询），返回未更新的数据，但第二个查询将按以前有锁的应用程序返回更新的数据
2.如果我通过两个ORACLE SQL DEVELOPER实例手动运行上述过程，则行为如预期，即：
SQL_DEV_1: SELECT * FROM T_MAIL WHERE STATUS = 'QUEUED' FOR UPDATE; // Returns 1 row, locks the row
SQL_DEV_2: SELECT * FROM T_MAIL WHERE STATUS = 'QUEUED' FOR UPDATE; // Doesn't return anything but keeps waiting for locks to be released
SQL_DEV_1: UPDATE T_MAIL SET STATUS = 'SENT'; // Returns 1 row, locks the row
SQL_DEV_1: COMMIT; // Commit update, locks are released
SQL_DEV_2: // waiting query is executed, returns no rows since one and only row was update to SENT
我想我已经找到了解决方案（或变通方法）。当您使用PESSIMISTIC_WRITE时，第二个服务器不知道第一个服务器正在更改数据。显然，当使用LockModeType.PESSIMISTIC_READ时，他可以知道这一点，但在Oracle中，LockModeType.PESSIMISTIC_READ是使用LockModeType.PESSIMISTIC_WRITE实现的。因此，您的解决方法是将version字段和设置模式添加到LockModeType.PESSIMISTIC_FORCE_INCREMENT