spring - 事务可重复读取隔离在 PostgreSQL 中无法正常工作

Question

我将 Spring 框架与 DataSourceTransactionManager 结合使用我注意到 Repeatable Read Isolation 不能与 Postgresql 一起正常工作。根据 Postgres documentation :

repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began.

假设我们有 2 个事务:T1 和 T2。假设这种情况:

T1 开始 -> T2 开始 -> T2 更新行 -> T2 提交 -> T1 更新同一行 -> T1 commit

事务 T1 应随消息一起回滚

ERROR: could not serialize access due to concurrent update

但 T1 已提交并覆盖 T2 更新。我在 Spring 中做了一个简单的例子来演示它:

SQL:

CREATE TABLE tab
(
  id bigint NOT NULL,
  name character varying(50),
  CONSTRAINT tab_pkey PRIMARY KEY (id )
)
INSERT INTO tab(id, name) VALUES (1, 'name');

数据源配置:

 <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:context="http://www.springframework.org/schema/context"
        xmlns:jdbc="http://www.springframework.org/schema/jdbc"
        xmlns:tx="http://www.springframework.org/schema/tx"
        xmlns:jpa="http://www.springframework.org/schema/data/jpa"
        xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
            http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
            http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.1.xsd
            http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd">

        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
            <property name="driverClassName" value="${jdbc.driverClassName}" />
            <property name="url" value="${jdbc.url}" />
            <property name="username" value="${jdbc.username}" />
            <property name="password" value="${jdbc.password}" />
        </bean>
        <bean id="transactionManager"
            class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
        </bean>
        <tx:annotation-driven transaction-manager="transactionManager" />
        <context:annotation-config />

        <bean
            class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
            id="property">
            <property name="location" value="classpath:database.properties"></property>
        </bean>
        <bean id="service" class="com.kulig.db_test.ServiceImpl">
            <constructor-arg ref="dataSource"></constructor-arg>
        </bean>
    </beans>

服务接口(interface):

public interface Service {
            public void dosth(String name);
            public void dosth2(String name);    
        }

服务接口(interface)的实现:

public class ServiceImpl extends JdbcTemplate implements Service {
    public ServiceImpl(DataSource dataSource) {
        super(dataSource);
    }
    String sqlQuery="update tab set name=? where id=?";
    @Transactional(isolation=Isolation.REPEATABLE_READ)
    public void dosth(String name) {
        System.out.println("Before waiting, "+Thread.currentThread().getName());
        try {
            Thread.sleep(10000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println("Start, "+Thread.currentThread().getName());
        update(sqlQuery, name,1);
        System.out.println("Stop,"+Thread.currentThread().getName());
    }
    @Transactional(isolation=Isolation.REPEATABLE_READ)
    public void dosth2(String name) {
        System.out.println("Before waiting, "+Thread.currentThread().getName());
        try {
            Thread.sleep(5000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println("Start, "+Thread.currentThread().getName());
        update(sqlQuery, name,1);
        System.out.println("Stop, "+Thread.currentThread().getName());
    }

}

主要内容:

public class MainApp {
public static void main(String[] args) {
        ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("data-tx-jpa.xml");
        final Service service = applicationContext.getBean(Service.class);

        Runnable runnable1 = new Runnable() {
            public void run() {
                service.dosth("name1");
            }
        };
        Runnable runnable2 = new Runnable() {
            public void run() {
                try {
                    Thread.sleep(1000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                service.dosth2("name2");

            }
        };
        Thread thread1 = new Thread(runnable1, "thread1");
        Thread thread2 = new Thread(runnable2, "thread2");
        thread1.start();
        thread2.start();

    }

}

日志:

main 2014-01-08 10:34:04,636 INFO [org.springframework.context.support.ClassPathXmlApplicationContext] - <Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@786bb78a: startup date [Wed Jan 08 10:34:04 CET 2014]; root of context hierarchy>
main 2014-01-08 10:34:04,687 INFO [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] - <Loading XML bean definitions from class path resource [data-tx-jpa.xml]>
main 2014-01-08 10:34:04,967 INFO [org.springframework.beans.factory.config.PropertyPlaceholderConfigurer] - <Loading properties file from class path resource [database.properties]>
main 2014-01-08 10:34:04,998 INFO [org.springframework.beans.factory.support.DefaultListableBeanFactory] - <Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@4d8657b9: defining beans [dataSource,transactionManager,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,org.springframework.context.annotation.internalPersistenceAnnotationProcessor,property,service,org.springframework.context.annotation.ConfigurationClassPostProcessor.importAwareProcessor]; root of factory hierarchy>
main 2014-01-08 10:34:05,080 DEBUG [org.springframework.transaction.annotation.AnnotationTransactionAttributeSource] - <Adding transactional method 'dosth2' with attribute: PROPAGATION_REQUIRED,ISOLATION_REPEATABLE_READ; ''>
thread1 2014-01-08 10:34:05,108 DEBUG [org.springframework.transaction.annotation.AnnotationTransactionAttributeSource] - <Adding transactional method 'dosth' with attribute: PROPAGATION_REQUIRED,ISOLATION_REPEATABLE_READ; ''>
thread1 2014-01-08 10:34:05,116 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Creating new transaction with name [com.kulig.db_test.ServiceImpl.dosth]: PROPAGATION_REQUIRED,ISOLATION_REPEATABLE_READ; ''>
thread1 2014-01-08 10:34:05,180 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Acquired Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver] for JDBC transaction>
thread1 2014-01-08 10:34:05,188 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Changing isolation level of JDBC Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver] to 4>
thread1 2014-01-08 10:34:05,204 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Switching JDBC Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver] to manual commit>
Before waiting, thread1
thread2 2014-01-08 10:34:06,106 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Creating new transaction with name [com.kulig.db_test.ServiceImpl.dosth2]: PROPAGATION_REQUIRED,ISOLATION_REPEATABLE_READ; ''>
thread2 2014-01-08 10:34:06,111 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Acquired Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver] for JDBC transaction>
thread2 2014-01-08 10:34:06,111 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Changing isolation level of JDBC Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver] to 4>
thread2 2014-01-08 10:34:06,112 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Switching JDBC Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver] to manual commit>
Before waiting, thread2
Start, thread2
thread2 2014-01-08 10:34:11,115 DEBUG [com.kulig.db_test.ServiceImpl] - <Executing prepared SQL update>
thread2 2014-01-08 10:34:11,116 DEBUG [com.kulig.db_test.ServiceImpl] - <Executing prepared SQL statement [update tab set name=? where id=?]>
thread2 2014-01-08 10:34:11,125 DEBUG [com.kulig.db_test.ServiceImpl] - <SQL update affected 1 rows>
Stop, thread2
thread2 2014-01-08 10:34:11,127 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Initiating transaction commit>
thread2 2014-01-08 10:34:11,128 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Committing JDBC transaction on Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver]>
thread2 2014-01-08 10:34:11,149 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Resetting isolation level of JDBC Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver] to 2>
thread2 2014-01-08 10:34:11,149 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Releasing JDBC Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver] after transaction>
thread2 2014-01-08 10:34:11,149 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Returning JDBC Connection to DataSource>
Start, thread1
thread1 2014-01-08 10:34:15,205 DEBUG [com.kulig.db_test.ServiceImpl] - <Executing prepared SQL update>
thread1 2014-01-08 10:34:15,205 DEBUG [com.kulig.db_test.ServiceImpl] - <Executing prepared SQL statement [update tab set name=? where id=?]>
thread1 2014-01-08 10:34:15,207 DEBUG [com.kulig.db_test.ServiceImpl] - <SQL update affected 1 rows>
Stop,thread1
thread1 2014-01-08 10:34:15,207 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Initiating transaction commit>
thread1 2014-01-08 10:34:15,207 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Committing JDBC transaction on Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver]>
thread1 2014-01-08 10:34:15,233 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Resetting isolation level of JDBC Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver] to 2>
thread1 2014-01-08 10:34:15,234 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Releasing JDBC Connection [jdbc:postgresql://localhost:5432/test, UserName=postgres, PostgreSQL Native Driver] after transaction>
thread1 2014-01-08 10:34:15,234 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Returning JDBC Connection to DataSource>

错误在哪里？是我做错了什么还是 Postgres 事务不起作用？

编辑这个场景正常工作:T1 开始 -> **T1 选择行** ->T2 开始 -> T2 更新行 -> T2 commit -> T1 更新同一行 -> T1 commit

Answer 1

您正在使用非法的外部知识来说明一笔交易先于另一笔交易开始。只要存在从数据库中产生与实际产生的结果相同的最终结果的合法行动顺序，数据库就履行了其义务。不需要此合法顺序与您认为用秒表测量的顺序一致。