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

Vladimir,

This simply won't work, you will need to either synchronize transaction,
or use some other locking mechanism. As I said, postgres supports
concurrent transactions, they will both succeed simultaneously.

Your options are select the row for update, which will lock it from
other transactions updating it.

synchronize transaction so that only one gets in there at a time.

use pessimistic locking which involves a timestamp and you have to check
to make sure that the row has not changed before you update it, although
without synchronization this won't work either.

Dave


On Wed, 2004-07-14 at 16:10, Vladimir Stankovic wrote:
> I guess it would've been handy if I included the source code!
>
> /*
>  * ConcurencyTest.java
>  * Created on 09 July 2004, 15:39
>  */
> import java.sql.*;
>
> public class ConcurrencyTest implements Runnable
> {
>     Connection con;
>     private int olquantity;
>
>     public ConcurrencyTest(int olquantity)
>     {
>         this.olquantity = olquantity;
>
>         con = new ConnectionManager(128).GetConnection();
>
>      }
>
>     public void run ()
>     {
>         transaction(olquantity);
>         try
>         {
>             con.commit();
>         }
>         catch (SQLException sqle)
>         {
>              System.out.println("Could not commit the connection: " +
> olquantity);
>         }
>     }
>
>     public void transaction(int olquantity)
>     {
>         try
>         {
>             Statement sta = con.createStatement();
>
>             ResultSet rs_1 = sta.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) + "\t" + olquantity/100);
>             rs_1.close();
>
>             sta.executeUpdate("UPDATE Orderline SET ol_quantity = " +
> olquantity + " WHERE ol_o_id = 1 AND ol_d_id = 1 AND ol_w_id = 1 AND ol_number
> = 4");
>         }
>         catch (SQLException sqle)
>         {
>             System.out.println("Error in Transaction with olquantity: " +
> olquantity + " : " + sqle);
>         }
>
>     }
>
>     public static void main(String args[])
>     {
>         Thread thread1, thread2;
>
>         thread1 = new Thread(new ConcurrencyTest(100));
>         thread2 = new Thread(new ConcurrencyTest(200));
>         thread1.start();
>         thread2.start();
>     }
> }
>
>
> On Jul 14 2004, Dave Cramer wrote:
>
> > 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
>
> Vladimir Stankovic                    Tel: +44 20 7040 0273
> Research Student                      Fax: +44 20 7040 8585
> Centre for Software Reliability       Email: V.Stankovic@city.ac.uk
> City University
> Northampton Square, London EC1V 0HB
> ________________________________________________________________________
>
>
>
>
> !DSPAM:40f5933d22475672335348!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561