Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to use transaction isolation

how to use transaction isolation

From
"Gong"
Date:
In the java code below, I set the transaction isolation to serialization.
 
public class IsolationTest {

    private static String select = "select * from tmp where url = 'aaa'";
    public static void main(String[] args) throws Exception{
        //ConncetionFactory is a factory class for managing connection
        Connection con = ConnectionFactory.getConnection();  
        con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        con.setAutoCommit(false);
        Statement smt = con.createStatement();
        ResultSet r1 = smt.executeQuery(select);
        System.out.println(r1.next());  //(1)   
        con.commit();               //(2)

        ResultSet r2 = smt.executeQuery(select);
        System.out.println(r2.next());  //(3)
        con.commit();
        smt.close();        

        ConnectionFactory.closeConnection();
    }
}
 
I set a break point at (2), then I run this code in debug mode. When it suspended at (2), line(1) print "false". Then, I execute an insert statement in pgadmin: insert into tmp values('aaa'), after that I continued to run the code, and line(3) print "true". I have set the transaction isolation to serialization, didn't the two select statements print the same result?
 
btw: postgresql version is 8.2, jdbc version is postgresql-8.2-506.jdbc3.jar, and jdk version is 1.5

Re: how to use transaction isolation

From
"Jan de Visser"
Date:
On 4/10/08, Gong <fredkung@sohu.com> wrote:
>
>
> In the java code below, I set the transaction isolation to serialization.
>
> public class IsolationTest {
>
>     private static String select = "select * from tmp where url = 'aaa'";
>
>     public static void main(String[] args) throws Exception{
>         //ConncetionFactory is a factory class for managing connection
>         Connection con = ConnectionFactory.getConnection();
>
>
> con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
>         con.setAutoCommit(false);
>
>         Statement smt = con.createStatement();
>         ResultSet r1 = smt.executeQuery(select);
>         System.out.println(r1.next());  //(1)
>         con.commit();               //(2)
>
>         ResultSet r2 = smt.executeQuery(select);
>         System.out.println(r2.next());  //(3)
>         con.commit();
>         smt.close();
>
>         ConnectionFactory.closeConnection();
>     }
> }
>
> I set a break point at (2), then I run this code in debug mode. When it
> suspended at (2), line(1) print "false". Then, I execute an insert statement
> in pgadmin: insert into tmp values('aaa'), after that I continued to run the
> code, and line(3) print "true". I have set the transaction isolation to
> serialization, didn't the two select statements print the same result?

Your commit at (2) ends the transaction, and the second select runs in
a new one.

jan

Re: how to use transaction isolation

From
"Jan de Visser"
Date:
On 4/10/08, Gong <fredkung@sohu.com> wrote:
>
>
> In the java code below, I set the transaction isolation to serialization.
>
> public class IsolationTest {
>
>     private static String select = "select * from tmp where url = 'aaa'";
>
>     public static void main(String[] args) throws Exception{
>         //ConncetionFactory is a factory class for managing connection
>         Connection con = ConnectionFactory.getConnection();
>
>
> con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
>         con.setAutoCommit(false);
>
>         Statement smt = con.createStatement();
>         ResultSet r1 = smt.executeQuery(select);
>         System.out.println(r1.next());  //(1)
>         con.commit();               //(2)
>
>         ResultSet r2 = smt.executeQuery(select);
>         System.out.println(r2.next());  //(3)
>         con.commit();
>         smt.close();
>
>         ConnectionFactory.closeConnection();
>     }
> }
>
> I set a break point at (2), then I run this code in debug mode. When it
> suspended at (2), line(1) print "false". Then, I execute an insert statement
> in pgadmin: insert into tmp values('aaa'), after that I continued to run the
> code, and line(3) print "true". I have set the transaction isolation to
> serialization, didn't the two select statements print the same result?

Your commit at (2) ends the transaction, and the second select runs in
a new one.

Re: how to use transaction isolation

From
"Gong"
Date:
On 4/11/08, Jan de Visser <jdevisser@digitalfairway.com> wrote:

>Your commit at (2) ends the transaction, and the second select runs in
>a new one.

>jan

--
I got it. I didn't notice it is the *TRANSACTION* isolation.
Thanks a lot!