Transaction Repeatable Read Isolation does not work properly in PostgreSQL

2
votes

I use Spring framework with DataSourceTransactionManager and I noticed that Repeatable Read Isolation does not work properly with Postgresql. According to Postgres documentation:

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

Lets assume that we have 2 transactions: T1 and T2. Assume this scenario:

T1 begin -> T2 begin -> T2 update row -> T2 commit -> T1 update the same row -> T1 commit

Transaction T1 should be rolled back with the message

ERROR: could not serialize access due to concurrent update

but T1 is committed and overwrites T2 update. I made simple example in Spring to demonstrate it:

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');

DataSource configuration:

 <?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>

Service interface:

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

Implementation of service 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());
    }

}

Main:

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();

    }

}

Logs:

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>

Where is bug? Do I do something wrong or Postgres transaction does not work?

EDIT This scenerio works properly: T1 begin -> **T1 select row** ->T2 begin -> T2 update row -> T2 commit -> T1 update the same row -> T1 commit

2
springpostgresqltransactions
There is no concurrent update... The first transaction has already finished, no locks etc. so the other transaction can freely do whatever it wants. You just have 2 sequential updates not concurrent ones. – M. Deinum
Repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began. After start of T1, T2 updates row and commits so this row is changed after start of transaction T1. T1 updates the same row so T1 shuold be rolled back. – Mariusz
Only if the update is issued before the transaction commits. The update and commit are handled before the other update is even issued, so nothing can be detected. If you move the update before the wait I would expect it to work. – M. Deinum
You are right, If I move the update before the wait it works. But I still do not understand. Lets change query update tab set name=? where id=? to update tab set name=name || ? where id=?. If repeatable read transaction sees only a snapshot as of the start of the transaction why result is namename2name1: This mean that T1 transaction sees updates made by T2 after start of T1. – Mariusz
According to documentation: The Repeatable Read isolation level only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions. So it is not a true. We can do select query before T1 update and we see changes made by T2. – Mariusz

2 Answers

1
votes

You are using illegal outside knowledge to say that one transaction begin before the other. The database has fulfilled its obligation as long as there exists a legal ordering of actions which would produce the same end results from the database as the actually produced results. It is not required that this legal ordering match the ordering you think you measured with your stop watch.

0
votes

I used Wireshark to analyze communication of my java application and PostgreSQL. I noticed that begin of transaction take place before send first query. I thought that transaction begins before start of method with @Transactional annotation. So scenerio: 

T1 begin -> T2 begin -> T2 update row -> T2 commit -> T1 update the same row -> T1 commit

is not correct. Real scenerio which I tested is:

T2 begin -> `T2 update row` -> T2 commit -> T1 begin ->T1 update the same row -> T1 commit

To test first scenerio, some query (select version() for example) should be executed before waiting in transaction T1. This query is necessary to begin a transaction T1 before start of transaction T2. In this scenerio everything is working properly.