JPA和MySQL事务隔离级别 [英] JPA and MySQL transaction isolation level

问题描述

我有一个本地查询，可以批量插入MySQL数据库：

I have a native query that does a batch insert into a MySQL database:

    String sql = "insert into t1 (a, b) select x, y from t2 where x = 'foo'";
    EntityTransaction tx = entityManager.getTransaction();
    try {
        tx.begin();
        int rowCount = entityManager.createNativeQuery(sql).executeUpdate();
        tx.commit();
        return rowCount;
    }
    catch(Exception ex) {
        tx.rollback();
        log.error(...);
    }

此查询导致死锁：从读取时t2 与插入..选择，另一个进程尝试在 t2 中插入一行。

This query causes a deadlock: while it reads from t2 with insert .. select, another process tries to insert a row into t2.

在执行插入时，我不关心 t2 的读取的一致性..选择并希望将事务隔离级别设置为 READ_UNCOMMITTED 。

I don't care about the consistency of reads from t2 when doing an insert .. select and want to set the transaction isolation level to READ_UNCOMMITTED.

如何在JPA中设置它？

How do I go about setting it in JPA?

更新

所以我最终为这个案例创建了一个常规SQL连接，因为它似乎给我最简单的选择。谢谢大家！

So I ended up creating a regular SQL connection for this case as it seemed to me the simplest option. Thanks everyone!

推荐答案

您需要在连接级别设置它，从实体管理器获取会话并执行此操作：

You need to set it at the connection level, get the session from the entitymanager and do this:

org.hibernate.Session session = (Session)entityManager.getDelegate();
Connection connection = session.connection();
connection.setTransactionIsolation(Connection.READ_UNCOMMITTED);

这篇关于JPA和MySQL事务隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！