Thread: No exception with concurrent updates
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();
}
}
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
>
>
>
> !DSPAM:40f57e51167398493720393!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561
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
> >
> >
> >
> > !DSPAM:40f57e51167398493720393!
> >
> >
>
--
________________________________________________________________________
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
________________________________________________________________________
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
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. > I used debug only to see where the programme execution stops. > 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. This is exactly what happened when I introduced threads in the programme (see the programme listing below). And since the execution of the threads is non-deterministic I sometimes end-up with the ol_quantity having the value of the first update. Did I fulfill the necessary condition to start the transactions at the same time? But i still don't understand why the programme didn't finish in the previous version. > > there is more information here > > http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html > > Dave Vladimir ________________________________________________________________________ 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 ________________________________________________________________________
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
________________________________________________________________________
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
Dave, On Jul 14 2004, Dave Cramer wrote: > 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. > I thought that this is taken care of when you set TRANSACTION_SERIALIZABLE on the Connection object. > 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 > > This programme was a part of the comparison between PostgreSQL and InterBase (known now under name Firebird for open source developement) regarding their concurrent updates handling, which as I know doesn't use 2 Phase Commit either. The term for MVCC in IB is multi-generational database architecture. By the way there exist the NO WAIT parameter in IB, which can be specified when starting a transaction and does not require a transaction to wait until the end of concurrent transaction. Using this mode the first version of my programme (without threads) did not halt - I received the exception and the second transaction's update did not commit. Thanks for the prompt responses, Vladimir ________________________________________________________________________ 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 ________________________________________________________________________
Yes, you are correct, it should be handled properly in this case, you should look at the logs in the postgresl log, to see what is really going on, it is possible they truly aren't concurrent. Set logpid on, and log statement Dave On Wed, 2004-07-14 at 17:36, Vladimir Stankovic wrote: > Dave, > > On Jul 14 2004, Dave Cramer wrote: > > > 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. > > > I thought that this is taken care of when you set TRANSACTION_SERIALIZABLE on > the Connection object. > > > 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 > > > > > This programme was a part of the comparison between PostgreSQL and InterBase > (known now under name Firebird for open source developement) regarding their > concurrent updates handling, which as I know doesn't use 2 Phase Commit either. > The term for MVCC in IB is multi-generational database architecture. By the way > there exist the NO WAIT parameter in IB, which can be specified when starting a > transaction and does not require a transaction to wait until the end of > concurrent transaction. Using this mode the first version of my programme > (without threads) did not halt - I received the exception and the second > transaction's update did not commit. > > Thanks for the prompt responses, > > Vladimir > > ________________________________________________________________________ > > 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:40f5a78e197681998034425! > > -- Dave Cramer 519 939 0336 ICQ # 14675561