Re: No exception with concurrent updates - Mailing list pgsql-jdbc

I'm not sure what you are doing in debug mode, but neither of these
updates should be blocked, as you are not locking the rows.

In order to lock the row you would need to select for update in one or
both of the transactions.

As far as MVCC goes the last one that goes through wins, not the first,
since each transaction sees a snapshot of the data at the time that the
transaction starts. So if ol_quantity is 5 before both transactions then
assuming you open them at the same time they will both see 5 there, then
one will update to 10, and the second will update to 20.

there is more information here

http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html

Dave
On Wed, 2004-07-14 at 15:10, Vladimir Stankovic wrote:
> As I understand PostgreSQL uses the multiversion concurrency control (sometimes
> called optimistic) , with the so called 'first writer wins' strategy, and
> provides two transaction isolation levels. In addition it provides mutiple lock
> modes (row and table)... If the two transactions are deadlocked (is this true?)
> shouldn't it be the case that after a specified timeout the clash is resolved
> by aborting the one that started later and/or giving the exception?
>
> Appologies for the naive understanding, I'm quite new to this stuff.
>
> Thanks in advance (I forgot this in the first message),
>
> Vladimir
>
> On Jul 14 2004, Dave Cramer wrote:
>
> > Vladimir,
> >
> > Have you read how concurrency works in postgresql ?
> >
> > Dave
> > On Wed, 2004-07-14 at 12:59, Vladimir Stankovic wrote:
> > > Dear all,
> > >
> > > I wrote (extremely) simple programme in Java (jsdk1.4.2) to examine the
> > > PostgreSQL's (v7.2.4, I know, rather obsolete) handling of concurrent
> > > updates and I'm confused with the fact that the server (running on a remote
> > > machine with Red Hat Linux 6.0) does not give me any kind of exception when
> > > I execute the following code on the client machine running Win 2000. While
> > > running the programme in the debug mode the excution just hangs when the
> > > UPDATE query of the transaction2() is executed. Using setQueryTimeout()
> > > won't help much since it is available as of 7.3 version, isn't it. I set
> > > autocommit off and specify TRANSACTION_SERIALIZABLE on the Connection
> > > objects. I'm using the pg73jdbc3.jar and NetBeans 3.5.
> > >
> > > /*
> > >  * ConcurencyTest.java
> > >  * Created on 09 July 2004, 15:39
> > >  */
> > > import java.sql.*;
> > >
> > > public class ConcurrencyTest
> > > {
> > >     Connection con1, con2;
> > >
> > >     public ConcurrencyTest()
> > >     {
> > >         /*
> > >           ConnectionManager object creates Connection and the
> > >           GetConnection() method returns the reference. Value of
> > >           128 specifies the JDBC connection (rather than ODBC).
> > >          */
> > >         con1 = new ConnectionManager(128).GetConnection();
> > >         con2 = new ConnectionManager(128).GetConnection();
> > >
> > >         transaction1();
> > >         transaction2();
> > >
> > >         try
> > >         {
> > >            con2.commit();
> > >            con1.commit();
> > >            //con1.close();
> > >            //con2.close();
> > >         }
> > >         catch (SQLException sqle)
> > >         {
> > >             System.out.println("Could not close the connection");
> > >         }
> > >
> > >     }
> > >
> > >     public void transaction1()
> > >     {
> > >         try
> > >         {
> > >             con1.commit();
> > >             Statement sta1 = con1.createStatement();
> > >             sta1.setQueryTimeout(5);
> > >
> > >             rs_1 = sta1.executeQuery("SELECT * FROM Orderline WHERE ol_o_id
> > > = 1 AND ol_d_id = 1 AND ol_w_id = 1 AND ol_number = 4");
> > >
> > >             while (rs_1.next())
> > >                 System.out.println(rs_1.getInt(8) + "\tT1");
> > >             rs_1.close();
> > >
> > >             sta1.executeUpdate("UPDATE Orderline SET ol_quantity = 10 WHERE
> > > ol_o_id = 1 AND ol_d_id = 1 AND ol_w_id = 1 AND ol_number = 4");
> > >
> > >             sta1.close();
> > >         }
> > >         catch (SQLException sqle)
> > >         {
> > >             System.out.println("Error in Transaction 1: " + sqle);
> > >         }
> > >
> > >     }
> > >
> > >     public void transaction2()
> > >     {
> > >         try
> > >         {
> > >             con2.commit();
> > >             Statement sta2 = con2.createStatement();
> > >             sta2.setQueryTimeout(5);
> > >
> > >             ResultSet rs_2 = sta2.executeQuery("SELECT * FROM Orderline
> > > WHERE ol_o_id = 1 AND ol_d_id = 1 AND ol_w_id = 1 AND ol_number = 4");
> > >             while (rs_2.next())
> > >                 System.out.println(rs_2.getInt(8) + "\tT2");
> > >             rs_2.close();
> > >
> > >
> > >             sta2.executeUpdate("UPDATE Orderline SET ol_quantity = 20 WHERE
> > > ol_o_id = 1 AND ol_d_id = 1 AND ol_w_id = 1 AND ol_number = 4");
> > >
> > >
> > >             sta2.close();
> > >         }
> > >         catch (SQLException sqle)
> > >         {
> > >             System.out.println("Error in Transaction 2: " + sqle);
> > >
> > >         }
> > >
> > >     }
> > >
> > >     public static void main(String args[])
> > >     {
> > >         new ConcurrencyTest();
> > >     }
> > > }
> > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 9: the planner will ignore your desire to choose an index scan if your
> > >       joining column's datatypes do not match
> > >
> > >
> > >
> > >
> > >
> > >
> >
--
Dave Cramer
519 939 0336
ICQ # 14675561