当使用Spring Hibernate CrudRepository Dao模式时，如何获得表上的insert锁？

Question

null

    @Query(value = "LOCK TABLES mail WRITE", nativeQuery = true)
    void lockTableForWriting();

    @Query(value = "UNLOCK TABLES", nativeQuery = true)
    void unlockTable();

但它们在lock和unlock上抛出了一个SQLGrammerException。

我无法获得行锁，因为行还不存在。或者，如果它确实存在，我不会更新任何东西，我只会不插入任何东西，而转移到其他东西。

还有其他情况，我希望用相同的事务id保存多个记录，所以我不能仅仅使列唯一并try/catch保存。

@Repository
public interface MailDao extends CrudRepository<Mail, Long> {

    default Mail safeSave(Mail mail) {
        return Optional.ofNullable(findByTransactionId(mail.getTransactionId()))
                       .orElseGet(() -> save(mail));
    }

    default Mail findByTransactionId(String transactionId) {
        final List<Mail> mails = findAllByTransactionId(transactionId);
        // Snipped code that selects a single entry to return
        // If one can't be found, null is returned.
    }

    @Query(value = "SELECT m " +
                   "  FROM Mail m " +
                   " WHERE m.transactionId = :transactionId ")
    List<Mail> findAllByTransactionId(@Param("transactionId") String transactionId);
}

下面是mail模型的一些内容：

@Entity
@Table(name = "mail")
public class Mail implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "mail_id", unique = true, nullable = false)
    private Long mailId;

    @Column(name = "transaction_id", nullable = false)
    private String transactionId;

    // Snipped code for other parameters, 
    // constructors, getters and setters.
}

下面是调用safeSave的服务方法的一般思想。

@Service
public class MailServiceImpl implements MailService {
    @Inject private final MailDao mailDao;
    // Snipped injected other stuff

    @Override
    public void saveMailInfo(final String transactionId) {
        Objects.requireNonNull(transactionId, "null transactionId passed to saveMailInfo");
        if (mailDao.findByTransactionId(transactionId) != null) {
            return;
        }
        // Use one of the injected things to do some lookup stuff
        // using external services
        if (mailDao.findByTransactionId(transactionId) != null) {
            return;
        }
        // Use another one of the injected things to do more lookup
        if (/* we've got everything we need */) {
            final Mail mail = new Mail();
            mail.setTransactionId(transactionId);
            // Snipped code to set the rest of the stuff
            mailDao.safeSave(mail);
        }
    }
}

我要防止的是对saveMailinfo的两个几乎同时的调用导致数据库中的重复记录。

public interface MailDaoCustom {
    Mail safeSave(Mail mail);
}

public interface MailDao extends CrudRepository<Mail, Long>, MailDaoCustom

则IMPL：

public class MailDaoImpl implements MailDaoCustom {

    @Autowired private MailDao mailDao;
    @Autowired private EntityManager em;

    public Mail safeSave(Mail mail) {
        Query lockTable = em.createNativeQuery("LOCK TABLES mail WRITE");
        Query unlockTable = em.createNativeQuery("UNLOCK TABLES");
        try {
            lockTable.executeUpdate();
            return Optional.ofNullable(mailDao.findByTransactionId(mail.getTransactionId()))
                           .orElseGet(() -> mailDao.save(mail));
        } finally {
            unlockTable.executeUpdate();
        }
    }
}

这是我在测试上面的时候得到的错误。这与我尝试在dao中使用查询来锁定和解锁方法时的情况相同：

SQL Error: 42000, SQLState: 42000
Syntax error in SQL statement "LOCK[*] TABLES MAIL WRITE "; SQL statement:
LOCK TABLES mail WRITE [42000-168]

不过，我的测试是使用单元测试完成的，单元测试使用的是H2内存数据库，而不是MySQL。不过，看起来H2可能并没有真正的表锁定。

public interface MailDaoCustom {
    Mail safeSave(Mail mail);
}

public interface MailDao extends CrudRepository<Mail, Long>, MailDaoCustom

public class MailDaoImpl implements MailDaoCustom {

    @Autowired private MailDao dao;
    @Autowired private EntityManager em;

    public Mail safeSave(Mail mail) {
        // Store a new mail record only if one doesn't already exist.
        Query uniqueInsert = em.createNativeQuery(
                        "INSERT INTO mail " +
                        "       (transaction_id, ...) " +
                        "SELECT :transactionId, ... " +
                        " WHERE NOT EXISTS (SELECT 1 FROM mail " +
                        "                   WHERE transaction_id = :transactionId) ");
        uniqueInsert.setParameter("transactionId", mail.getTransactionId());
        // Snipped setting of the rest of the parameters in the query
        uniqueInsert.executeUpdate();

        // Now go get the record
        Mail entry = dao.findByTransactionId(mail.getTransactionId());
        // Detatch the entry so that we can attach the provided mail object later.
        em.detach(entry);

        // Copy all the data from the db entry into the one provided to this method
        mail.setMailId(entry.getMailId());
        mail.setTransactionId(entry.getTransactionId());
        // Snipped setting of the rest of the parameters in the provided mail object

        // Attach the provided object to the entity manager just like the save() method would.
        em.merge(mail);

        return mail;
    }
}

它并不像我希望的那样干净，我担心我可能犯了一些错误，一些隐藏的东西，我不会发现，直到事情爆炸。但我们拭目以待。

Answer 1

我使用了insert INTO...WHERE NOT exists查询和自定义回购。上面在更新2下列出了它，但我也把它放在这里，这样就更容易找到了。

public interface MailDaoCustom {
    Mail safeSave(Mail mail);
}

更新了maildao以实现它：

public interface MailDao extends CrudRepository<Mail, Long>, MailDaoCustom

然后impl如下所示：

public class MailDaoImpl implements MailDaoCustom {

    @Autowired private MailDao dao;
    @Autowired private EntityManager em;

    public Mail safeSave(Mail mail) {
        // Store a new mail record only if one doesn't already exist.
        Query uniqueInsert = em.createNativeQuery(
                        "INSERT INTO mail " +
                        "       (transaction_id, ...) " +
                        "SELECT :transactionId, ... " +
                        " WHERE NOT EXISTS (SELECT 1 FROM mail " +
                        "                   WHERE transaction_id = :transactionId) ");
        uniqueInsert.setParameter("transactionId", mail.getTransactionId());
        // Snipped setting of the rest of the parameters in the query
        uniqueInsert.executeUpdate();

        // Now go get the record
        Mail entry = dao.findByTransactionId(mail.getTransactionId());
        // Detach the entry so that we can attach the provided mail object later.
        em.detach(entry);

        // Copy all the data from the db entry into the one provided to this method
        mail.setMailId(entry.getMailId());
        mail.setTransactionId(entry.getTransactionId());
        // Snipped setting of the rest of the parameters in the provided mail object

        // Attach the provided object to the entity manager just like the save() method would.
        em.merge(mail);

        return mail;
    }
}