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

From Vladimir Stankovic
Subject Re: No exception with concurrent updates
Date
Msg-id E1Bkq4m-0005UV-00@ms2.city.ac.uk
Whole thread Raw
In response to No exception with concurrent updates  (Vladimir Stankovic <V.Stankovic@city.ac.uk>)
Responses Re: No exception with concurrent updates
List pgsql-jdbc
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
________________________________________________________________________



pgsql-jdbc by date:

Previous
From: Vladimir Stankovic
Date:
Subject: Re: No exception with concurrent updates
Next
From: Dave Cramer
Date:
Subject: Re: No exception with concurrent updates