Spring-JDBC中的隔离级别SERIALIZABLE [英] Isolation level SERIALIZABLE in Spring-JDBC

问题描述

也许有人可以帮我解决Spring（3.1）/ Postgresql（8.4.11）中的交易问题

maybe somebody can help me with a transactional issue in Spring (3.1)/ Postgresql (8.4.11)

我的交易服务如下：

@Transactional(isolation = Isolation.SERIALIZABLE, readOnly = false)
@Override
public Foo insertObject(Bar bar) {

            // these methods are just examples
            int x = firstDao.getMaxNumberOfAllowedObjects(bar)
            int y = secondDao.getNumerOfExistingObjects(bar)
            // comparison
            if (x - y > 0){
                  secondDao.insertNewObject(...) 
            }
            ....
}

Spring配置Webapp包含：

The Spring configuration Webapp contains:

@Configuration 
@EnableTransactionManagement 
public class ....{
    @Bean
    public DataSource dataSource() {
        org.apache.tomcat.jdbc.pool.DataSource ds = new DataSource();

        ....configuration details

        return ds;
    }

    @Bean
    public DataSourceTransactionManager txManager() {
        return new DataSourceTransactionManager(dataSource());
    }
}

让我们说一个请求x和一个请求y并发执行并到达注释比较（方法insertObject）。然后允许它们都插入一个新对象并提交它们的事务。

Let us say a request "x" and a request "y" execute concurrently and arrive both at the comment "comparison" (method insertObject). Then both of them are allowed to insert a new object and their transactions are commited.

为什么我没有RollbackException？据我所知，这是Serializable isolotation级别的用途。回到上一个场景，如果x设法插入一个新对象并提交其事务，则不应该允许y的事务提交，因为有一个他没有读过的新对象。

Why am I not having a RollbackException? As far as I know that is what the Serializable isolotation level is for. Coming back to the previous scenario, if x manages to insert a new object and commits its transaction, then "y"'s transaction should not be allowed to commit since there is a new object he did not read.

也就是说，如果y再次读取secondDao.getNumerOfExistingObjects（bar）的值，它会意识到还有一个新对象。幻影？

That is, if "y" could read again the value of secondDao.getNumerOfExistingObjects(bar) it would realize that there is a new object more. Phantom?

交易配置似乎工作正常：

The transaction configuration seems to be working fine:

• 每个请求我可以看到firstDao和secondDao的相同连接


• 每次调用insertObject时都会创建一个事务

第一个和第二个DAO如下：

Both first and second DAOs are as follows:

@Autowired
public void setDataSource(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
}

@Override
public Object daoMethod(Object param) {

        //uses jdbcTemplate

}

我确信我遗失了一些东西。不知道吗？

I am sure I am missing something. Any idea?

感谢您的时间，

Javier

推荐答案

TL; DR：Pg 9.1中可检测性的可串行性冲突得到了显着改善，因此升级。

从描述中弄清楚实际的SQL是什么以及为什么期望得到回滚是很棘手的。看起来你已经严重误解了可序列化隔离，或许它认为它完美地测试了所有谓词，但它没有，特别是在Pg 8.4中。

It's tricky to figure out from your description what the actual SQL is and why you expect to get a rollback. It looks like you've seriously misunderstood serializable isolation, perhaps thinking it perfectly tests all predicates, which it doesn't, especially not in Pg 8.4.

SERIALIZABLE 并不能完美地保证事务的执行就像它们是串行运行一样 - 从性能的角度来看，这样做会非常昂贵，如果可能的话。它只提供有限的检查。确切地说检查了什么以及数据库与数据库以及版本之间的差异如何，因此您需要阅读您的数据库版本的文档。

SERIALIZABLE doesn't perfectly guarantee that the transactions execute as if they were run in series - as doing so would be prohibitively expensive from a performance point of view if it it were possible at all. It only provides limited checking. Exactly what is checked and how varies from database to database and version to version, so you need to read the docs for your version of your database.

异常是可能的，其中在 SERIALIZABLE 模式下执行的两个事务会产生不同的结果，如果这些事务真正在系列。

Anomalies are possible, where two transactions executing in SERIALIZABLE mode produce a different result to if those transactions truly executed in series.

阅读Pg中有关事务隔离的文档以了解更多信息。请注意， SERIALIZABLE 在Pg 9.1中显着改变了行为，因此请务必阅读适合您的Pg版本的手册版本。 这是8.4版本。特别请阅读 13.2.2.1。可序列化隔离与真正可串行化。现在将其与大大改进的基于谓词锁定的序列化支持进行比较在Pg 9.1文档中描述 。

Read the documentation on transaction isolation in Pg to learn more. Note that SERIALIZABLE changed behaviour dramatically in Pg 9.1, so make sure to read the version of the manual appropriate for your Pg version. Here's the 8.4 version. In particular read 13.2.2.1. Serializable Isolation versus True Serializability. Now compare that to the greatly improved predicate locking based serialization support described in the Pg 9.1 docs.

看起来你正在尝试执行类似这种伪代码的逻辑：

It looks like you're trying to perform logic something like this pseudocode:

count = query("SELECT count(*) FROM the_table");
if (count < threshold):
    query("INSERT INTO the_table (...) VALUES (...)");

如果是这样的话，当并发执行时，这不会在Pg 8.4中起作用 - 它几乎与上面链接的文档中使用的异常示例。令人惊讶的是它实际上适用于Pg 9.1;我没想到甚至9.1的谓词锁定来捕获聚合使用。

If so, that's not going to work in Pg 8.4 when executed concurrently - it's pretty much the same as the anomaly example used in the documentation linked above. Amazingly it actually works on Pg 9.1; I didn't expect even 9.1's predicate locking to catch use of aggregates.

你写的是：

回到上一个场景，如果x设法插入一个新的
对象并提交其事务，那么y的事务不应该允许
提交有一个他没有读过的新对象。

Coming back to the previous scenario, if x manages to insert a new object and commits its transaction, then "y"'s transaction should not be allowed to commit since there is a new object he did not read.

但8.4不会检测到这两个交易是相互依赖的，你可以琐碎的通过使用两个 psql 会话来证明它。只有在9.1中引入的真正可串行化的东西才会有效 - 坦率地说，我很惊讶它在9.1中工作。

but 8.4 won't detect that the two transactions are interdependent, something you can trivially prove by using two psql sessions to test it. It's only with the true-serializability stuff introduced in 9.1 that this will work - and frankly, I was surprised it works in 9.1.

如果你想做类似强制执行的事情在Pg 8.4中最大行数，您需要 LOCK 表防止并发 INSERT s，手动或通过触发功能。在触发器中执行它本身就需要锁定升级，因此会经常死锁，但会成功完成工作。最好在应用程序中完成，你可以在获得 SELECT 之前发出 LOCK TABLE my_table IN EXCLUSIVE MODE table，所以它已经具有桌面上需要的最高锁模式，因此不需要易于死锁的锁升级。 EXCLUSIVE 锁定模式是合适的，因为它允许 SELECT s，但没有别的。

If you want to do something like enforce a maximum row count in Pg 8.4, you need to LOCK the table to prevent concurrent INSERTs, doing the locking either manually or via a trigger function. Doing it in a trigger will inherently require a lock promotion and thus will frequently deadlock, but will successfully do the job. It's better done in the application where you can issue the LOCK TABLE my_table IN EXCLUSIVE MODE before obtaining even SELECTing from the table, so it already has the highest lock mode it will need on the table and thus shouldn't need deadlock-prone lock promotion. The EXCLUSIVE lock mode is appropriate because it permits SELECTs but nothing else.

以下是如何在两个psql会话中测试它：

Here's how to test it in two psql sessions:

SESSION 1                               SESSION 2

create table ser_test( x text );

BEGIN TRANSACTION 
ISOLATION LEVEL SERIALIZABLE;


                                        BEGIN TRANSACTION 
                                        ISOLATION LEVEL SERIALIZABLE;

SELECT count(*) FROM ser_test ;

                                        SELECT count(*) FROM ser_test ;

INSERT INTO ser_test(x) VALUES ('bob');


                                        INSERT INTO ser_test(x) VALUES ('bob');

 COMMIT;

                                        COMMIT;

在Pg 9.1上运行时， st提交成功然后是第二个 COMMIT`失败，并且：

When run on Pg 9.1, the st commits succeeds then the secondCOMMIT` fails with:

regress=# COMMIT;
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

但是当在8.4上运行时，两次提交提交都成功，因为8.4没有所有谓词锁定代码在9.1中添加了可串行化。

but when run on 8.4 both commits commits succeed, because 8.4 didn't have all the predicate locking code for serializability added in 9.1.

这篇关于Spring-JDBC中的隔离级别SERIALIZABLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！