Thread: Fwd: Re: Synchronization issues with pg73jdbc3.jar and pg73jdbc2ee.jar
Taken from: http://www.jguru.com/faq/view.jsp?EID=721 Dirty read: "Quite often in database processing, we come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value." This is exactly the thing that should not happen with my code, but it does. The idea was to prove that the synchronization is unstable when it comes to serializable transactions. I might just push myself into a deeper hole, but as far as I know, the whole idea of serializable transaction handling is to be able to acquire an exclusive access to the needed fields. According to the JDBC 2.1 javadoc: (http://java.sun.com/products/jdk/1.2/docs/api/java/sql/Connection.html#TRAN SACTION_SERIALIZABLE) "Dirty reads, non-repeatable reads and phantom reads are prevented." This should mean that I shouldn't be seeing the stack-trace you saw too. Regards. Andras (Which is my first name, it's all mixed up in Hungarian :)) On Mon, 26 May 2003 11:51:03 -0700, Barry Lind <blind@xythos.com> wrote : > Gerlits, > > I still don't understand your problem. From what I can see the database > is doing the correct thing. You issue a bunch of selects that will all > return the same value, and then you try to insert that value into a > table with a unique index and you end up with duplicate key in index errors. > > thanks, > --Barry > > Gerlits AndrXs wrote: > > Those stacktraces are exactly my concern. I don't expect my code to behave > > like that :). > > > > On Mon, 26 May 2003 11:30:50 -0700, Barry Lind <blind@xythos.com> wrote : > > > > > >>Gerlite, > >> > >>I ran the test program you submitted and it seems to run OK (other than > >>some duplicate key in index errors). What is the problem you are > >>seeing? Specifically what are you expecing to happen, and how does what > >>you are seeing differ from your expectatations. > >> > >>thanks, > >>--Barry > >> > >>Gerlits AndrXs wrote: > >> > >>>Attached you'll find a simple multi-threaded example of a couple of > >>>SERIALIZABLE transactions. I hope, I'm not making a complete ass of > > > > myself, > > > >>>but it seems that the JDBC driver is unprepared to handle simultaneous > >>>SERIALIZABLE transactions. > >>> > >>>The table structure to test with is really simple: > >>> > >>>CREATE TABLE test ( > >>> id integer UNIQUE NOT NULL > >>>); > >>> > >>>The program tries to access the database for the highest id available, > > > > then > > > >>>use it in a preparedstatement. > >>> > >>>(The reason we do that is to prepare for the worst DB server available, > > > > we > > > >>>know that there are other ways to do this in postgres.) > >>> > >>>It first opens the connections, stores them, than hands them to the > > > > threads. > > > >>>No connection is issued twice simultaneously. > >>> > >>>Please edit the variables at the top, but check not to have more > >>>InserterThreads than dbConnections. > >>> > >>>Thanks > >>>Andras Gerlits > >>> > >>> > >>>----------------------------------------------------------------------- - > >>> > >>> > >>>---------------------------(end of broadcast)-------------------------- - > >>>TIP 3: if posting/reading through Usenet, please send an appropriate > >>>subscribe-nomail command to majordomo@postgresql.org so that your > >>>message can get through to the mailing list cleanly > >> > >> > >> > >> > >> > > > > > > >
Andres, When your code starts up each transaction reads the current max(id). All transactions see the same value and therefore all try to insert the same value. This has nothing to do with dirty reads. --Barry Gerlits AndrXs wrote: > Taken from: http://www.jguru.com/faq/view.jsp?EID=721 > > Dirty read: > > "Quite often in database processing, we come across the situation wherein > one transaction can change a value, and a second transaction can read this > value before the original change has been committed or rolled back. This is > known as a dirty read scenario because there is always the possibility that > the first transaction may rollback the change, resulting in the second > transaction having read an invalid value." > > This is exactly the thing that should not happen with my code, but it does. > > The idea was to prove that the synchronization is unstable when it comes to > serializable transactions. I might just push myself into a deeper hole, but > as far as I know, the whole idea of serializable transaction handling is to > be able to acquire an exclusive access to the needed fields. According to > the JDBC 2.1 javadoc: > (http://java.sun.com/products/jdk/1.2/docs/api/java/sql/Connection.html#TRAN > SACTION_SERIALIZABLE) > > "Dirty reads, non-repeatable reads and phantom reads are prevented." > > This should mean that I shouldn't be seeing the stack-trace you saw too. > > Regards. > Andras > (Which is my first name, it's all mixed up in Hungarian :)) > > On Mon, 26 May 2003 11:51:03 -0700, Barry Lind <blind@xythos.com> wrote : > > >>Gerlits, >> >>I still don't understand your problem. From what I can see the database >>is doing the correct thing. You issue a bunch of selects that will all >>return the same value, and then you try to insert that value into a >>table with a unique index and you end up with duplicate key in index > > errors. > >>thanks, >>--Barry >> >>Gerlits AndrXs wrote: >> >>>Those stacktraces are exactly my concern. I don't expect my code to > > behave > >>>like that :). >>> >>>On Mon, 26 May 2003 11:30:50 -0700, Barry Lind <blind@xythos.com> > > wrote : > >>> >>>>Gerlite, >>>> >>>>I ran the test program you submitted and it seems to run OK (other than >>>>some duplicate key in index errors). What is the problem you are >>>>seeing? Specifically what are you expecing to happen, and how does > > what > >>>>you are seeing differ from your expectatations. >>>> >>>>thanks, >>>>--Barry >>>> >>>>Gerlits AndrXs wrote: >>>> >>>> >>>>>Attached you'll find a simple multi-threaded example of a couple of >>>>>SERIALIZABLE transactions. I hope, I'm not making a complete ass of >>> >>>myself, >>> >>> >>>>>but it seems that the JDBC driver is unprepared to handle simultaneous >>>>>SERIALIZABLE transactions. >>>>> >>>>>The table structure to test with is really simple: >>>>> >>>>>CREATE TABLE test ( >>>>> id integer UNIQUE NOT NULL >>>>>); >>>>> >>>>>The program tries to access the database for the highest id available, >>> >>>then >>> >>> >>>>>use it in a preparedstatement. >>>>> >>>>>(The reason we do that is to prepare for the worst DB server > > available, > >>>we >>> >>> >>>>>know that there are other ways to do this in postgres.) >>>>> >>>>>It first opens the connections, stores them, than hands them to the >>> >>>threads. >>> >>> >>>>>No connection is issued twice simultaneously. >>>>> >>>>>Please edit the variables at the top, but check not to have more >>>>>InserterThreads than dbConnections. >>>>> >>>>>Thanks >>>>>Andras Gerlits >>>>> >>>>> >>>>>----------------------------------------------------------------------- > > - > >>>>> >>>>>---------------------------(end of broadcast)-------------------------- > > - > >>>>>TIP 3: if posting/reading through Usenet, please send an appropriate >>>>>subscribe-nomail command to majordomo@postgresql.org so that your >>>>>message can get through to the mailing list cleanly >>>> >>>> >>>> >>>> >>>> >> >> >> >> > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Re: Fwd: Re: Synchronization issues with pg73jdbc3.jar and pg73jdbc2ee.jar
From
"Gerlits András"
Date:
No, the connections all get passed to the reader method, so they all use the exclusive lock in the transaction, since it has been set to Connection.TRANSACTION_SERIALIZABLE. I promise, this is the last mail from me on the matter, but I AM pretty sure, that this should not happen. What's the use of preventing dirty- reads, if I can do one (hence the program attached in my previous post)? I AM sure, that what the driver does should not happen if TRANSACTION_SERIALIZABLE is fully and correctly implemented. Regards Andras PS: If everyone else is sure that all JDBC drivers behave this way, I might just back up, although I'm still curious then, what this feature is for. On Mon, 26 May 2003 13:21:41 -0700, Barry Lind <blind@xythos.com> wrote : > Andres, > > When your code starts up each transaction reads the current max(id). > All transactions see the same value and therefore all try to insert the > same value. This has nothing to do with dirty reads. > > --Barry > > > Gerlits AndrXs wrote: > > Taken from: http://www.jguru.com/faq/view.jsp?EID=721 > > > > Dirty read: > > > > "Quite often in database processing, we come across the situation wherein > > one transaction can change a value, and a second transaction can read this > > value before the original change has been committed or rolled back. This is > > known as a dirty read scenario because there is always the possibility that > > the first transaction may rollback the change, resulting in the second > > transaction having read an invalid value." > > > > This is exactly the thing that should not happen with my code, but it does. > > > > The idea was to prove that the synchronization is unstable when it comes to > > serializable transactions. I might just push myself into a deeper hole, but > > as far as I know, the whole idea of serializable transaction handling is to > > be able to acquire an exclusive access to the needed fields. According to > > the JDBC 2.1 javadoc: > > (http://java.sun.com/products/jdk/1.2/docs/api/java/sql/Connection.html#TRAN > > SACTION_SERIALIZABLE) > > > > "Dirty reads, non-repeatable reads and phantom reads are prevented." > > > > This should mean that I shouldn't be seeing the stack-trace you saw too. > > > > Regards. > > Andras > > (Which is my first name, it's all mixed up in Hungarian :)) > > > > On Mon, 26 May 2003 11:51:03 -0700, Barry Lind <blind@xythos.com> wrote : > > > > > >>Gerlits, > >> > >>I still don't understand your problem. From what I can see the database > >>is doing the correct thing. You issue a bunch of selects that will all > >>return the same value, and then you try to insert that value into a > >>table with a unique index and you end up with duplicate key in index > > > > errors. > > > >>thanks, > >>--Barry > >> > >>Gerlits AndrXs wrote: > >> > >>>Those stacktraces are exactly my concern. I don't expect my code to > > > > behave > > > >>>like that :). > >>> > >>>On Mon, 26 May 2003 11:30:50 -0700, Barry Lind <blind@xythos.com> > > > > wrote : > > > >>> > >>>>Gerlite, > >>>> > >>>>I ran the test program you submitted and it seems to run OK (other than > >>>>some duplicate key in index errors). What is the problem you are > >>>>seeing? Specifically what are you expecing to happen, and how does > > > > what > > > >>>>you are seeing differ from your expectatations. > >>>> > >>>>thanks, > >>>>--Barry > >>>> > >>>>Gerlits AndrXs wrote: > >>>> > >>>> > >>>>>Attached you'll find a simple multi-threaded example of a couple of > >>>>>SERIALIZABLE transactions. I hope, I'm not making a complete ass of > >>> > >>>myself, > >>> > >>> > >>>>>but it seems that the JDBC driver is unprepared to handle simultaneous > >>>>>SERIALIZABLE transactions. > >>>>> > >>>>>The table structure to test with is really simple: > >>>>> > >>>>>CREATE TABLE test ( > >>>>> id integer UNIQUE NOT NULL > >>>>>); > >>>>> > >>>>>The program tries to access the database for the highest id available, > >>> > >>>then > >>> > >>> > >>>>>use it in a preparedstatement. > >>>>> > >>>>>(The reason we do that is to prepare for the worst DB server > > > > available, > > > >>>we > >>> > >>> > >>>>>know that there are other ways to do this in postgres.) > >>>>> > >>>>>It first opens the connections, stores them, than hands them to the > >>> > >>>threads. > >>> > >>> > >>>>>No connection is issued twice simultaneously. > >>>>> > >>>>>Please edit the variables at the top, but check not to have more > >>>>>InserterThreads than dbConnections. > >>>>> > >>>>>Thanks > >>>>>Andras Gerlits > >>>>> > >>>>> > >>>>>--------------------------------------------------------------------- -- > > > > - > > > >>>>> > >>>>>---------------------------(end of broadcast)------------------------ -- > > > > - > > > >>>>>TIP 3: if posting/reading through Usenet, please send an appropriate > >>>>>subscribe-nomail command to majordomo@postgresql.org so that your > >>>>>message can get through to the mailing list cleanly > >>>> > >>>> > >>>> > >>>> > >>>> > >> > >> > >> > >> > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > >
Ok, I'm not clear on the TRANSACTION_SERIALIZABLE stuff, but the correct way to do this in postgres which is a MVCC database is to do select nextval( 'sequencename' ) to get the id, or conversly insert into the record with the sequence column omitted, or DEFAULT, then use select curval( 'sequencename' ). This will guarantee that you will get a unique value for the sequence, no other method will work reliably in postgres. Dave On Mon, 2003-05-26 at 16:33, Gerlits András wrote: > No, the connections all get passed to the reader method, so they all use > the exclusive lock in the transaction, since it has been set to > Connection.TRANSACTION_SERIALIZABLE. > > I promise, this is the last mail from me on the matter, but I AM pretty > sure, that this should not happen. What's the use of preventing dirty- > reads, if I can do one (hence the program attached in my previous post)? > > I AM sure, that what the driver does should not happen if > TRANSACTION_SERIALIZABLE is fully and correctly implemented. > > Regards > Andras > > PS: If everyone else is sure that all JDBC drivers behave this way, I might > just back up, although I'm still curious then, what this feature is for. > > On Mon, 26 May 2003 13:21:41 -0700, Barry Lind <blind@xythos.com> wrote : > > > Andres, > > > > When your code starts up each transaction reads the current max(id). > > All transactions see the same value and therefore all try to insert the > > same value. This has nothing to do with dirty reads. > > > > --Barry > > > > > > Gerlits AndrXs wrote: > > > Taken from: http://www.jguru.com/faq/view.jsp?EID=721 > > > > > > Dirty read: > > > > > > "Quite often in database processing, we come across the situation > wherein > > > one transaction can change a value, and a second transaction can read > this > > > value before the original change has been committed or rolled back. > This is > > > known as a dirty read scenario because there is always the possibility > that > > > the first transaction may rollback the change, resulting in the second > > > transaction having read an invalid value." > > > > > > This is exactly the thing that should not happen with my code, but it > does. > > > > > > The idea was to prove that the synchronization is unstable when it > comes to > > > serializable transactions. I might just push myself into a deeper hole, > but > > > as far as I know, the whole idea of serializable transaction handling > is to > > > be able to acquire an exclusive access to the needed fields. According > to > > > the JDBC 2.1 javadoc: > > > > (http://java.sun.com/products/jdk/1.2/docs/api/java/sql/Connection.html#TRAN > > > SACTION_SERIALIZABLE) > > > > > > "Dirty reads, non-repeatable reads and phantom reads are prevented." > > > > > > This should mean that I shouldn't be seeing the stack-trace you saw too. > > > > > > Regards. > > > Andras > > > (Which is my first name, it's all mixed up in Hungarian :)) > > > > > > On Mon, 26 May 2003 11:51:03 -0700, Barry Lind <blind@xythos.com> > wrote: > > > > > > > > >>Gerlits, > > >> > > >>I still don't understand your problem. From what I can see the > database > > >>is doing the correct thing. You issue a bunch of selects that will all > > >>return the same value, and then you try to insert that value into a > > >>table with a unique index and you end up with duplicate key in index > > > > > > errors. > > > > > >>thanks, > > >>--Barry > > >> > > >>Gerlits AndrXs wrote: > > >> > > >>>Those stacktraces are exactly my concern. I don't expect my code to > > > > > > behave > > > > > >>>like that :). > > >>> > > >>>On Mon, 26 May 2003 11:30:50 -0700, Barry Lind <blind@xythos.com> > > > > > > wrote : > > > > > >>> > > >>>>Gerlite, > > >>>> > > >>>>I ran the test program you submitted and it seems to run OK (other > than > > >>>>some duplicate key in index errors). What is the problem you are > > >>>>seeing? Specifically what are you expecing to happen, and how does > > > > > > what > > > > > >>>>you are seeing differ from your expectatations. > > >>>> > > >>>>thanks, > > >>>>--Barry > > >>>> > > >>>>Gerlits AndrXs wrote: > > >>>> > > >>>> > > >>>>>Attached you'll find a simple multi-threaded example of a couple of > > >>>>>SERIALIZABLE transactions. I hope, I'm not making a complete ass of > > >>> > > >>>myself, > > >>> > > >>> > > >>>>>but it seems that the JDBC driver is unprepared to handle > simultaneous > > >>>>>SERIALIZABLE transactions. > > >>>>> > > >>>>>The table structure to test with is really simple: > > >>>>> > > >>>>>CREATE TABLE test ( > > >>>>> id integer UNIQUE NOT NULL > > >>>>>); > > >>>>> > > >>>>>The program tries to access the database for the highest id > available, > > >>> > > >>>then > > >>> > > >>> > > >>>>>use it in a preparedstatement. > > >>>>> > > >>>>>(The reason we do that is to prepare for the worst DB server > > > > > > available, > > > > > >>>we > > >>> > > >>> > > >>>>>know that there are other ways to do this in postgres.) > > >>>>> > > >>>>>It first opens the connections, stores them, than hands them to the > > >>> > > >>>threads. > > >>> > > >>> > > >>>>>No connection is issued twice simultaneously. > > >>>>> > > >>>>>Please edit the variables at the top, but check not to have more > > >>>>>InserterThreads than dbConnections. > > >>>>> > > >>>>>Thanks > > >>>>>Andras Gerlits > > >>>>> > > >>>>> > > >>>>>--------------------------------------------------------------------- > -- > > > > > > - > > > > > >>>>> > > >>>>>---------------------------(end of broadcast)------------------------ > -- > > > > > > - > > > > > >>>>>TIP 3: if posting/reading through Usenet, please send an appropriate > > >>>>>subscribe-nomail command to majordomo@postgresql.org so that your > > >>>>>message can get through to the mailing list cleanly > > >>>> > > >>>> > > >>>> > > >>>> > > >>>> > > >> > > >> > > >> > > >> > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Dave Cramer <Dave@micro-automation.net>
Andras, Please see:http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=transaction-iso.html for the reason you are observing the results. When a transaction is on the serializable level, a SELECT query sees only data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions. (However, the SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed.) This is different from Read Committed in that the SELECT sees a snapshot as of the start of the transaction, not as of the start of the current query within the transaction. Thus, successive Dave On Mon, 2003-05-26 at 16:33, Gerlits András wrote: > No, the connections all get passed to the reader method, so they all use > the exclusive lock in the transaction, since it has been set to > Connection.TRANSACTION_SERIALIZABLE. > > I promise, this is the last mail from me on the matter, but I AM pretty > sure, that this should not happen. What's the use of preventing dirty- > reads, if I can do one (hence the program attached in my previous post)? > > I AM sure, that what the driver does should not happen if > TRANSACTION_SERIALIZABLE is fully and correctly implemented. > > Regards > Andras > > PS: If everyone else is sure that all JDBC drivers behave this way, I might > just back up, although I'm still curious then, what this feature is for. > > On Mon, 26 May 2003 13:21:41 -0700, Barry Lind <blind@xythos.com> wrote : > > > Andres, > > > > When your code starts up each transaction reads the current max(id). > > All transactions see the same value and therefore all try to insert the > > same value. This has nothing to do with dirty reads. > > > > --Barry > > > > > > Gerlits AndrXs wrote: > > > Taken from: http://www.jguru.com/faq/view.jsp?EID=721 > > > > > > Dirty read: > > > > > > "Quite often in database processing, we come across the situation > wherein > > > one transaction can change a value, and a second transaction can read > this > > > value before the original change has been committed or rolled back. > This is > > > known as a dirty read scenario because there is always the possibility > that > > > the first transaction may rollback the change, resulting in the second > > > transaction having read an invalid value." > > > > > > This is exactly the thing that should not happen with my code, but it > does. > > > > > > The idea was to prove that the synchronization is unstable when it > comes to > > > serializable transactions. I might just push myself into a deeper hole, > but > > > as far as I know, the whole idea of serializable transaction handling > is to > > > be able to acquire an exclusive access to the needed fields. According > to > > > the JDBC 2.1 javadoc: > > > > (http://java.sun.com/products/jdk/1.2/docs/api/java/sql/Connection.html#TRAN > > > SACTION_SERIALIZABLE) > > > > > > "Dirty reads, non-repeatable reads and phantom reads are prevented." > > > > > > This should mean that I shouldn't be seeing the stack-trace you saw too. > > > > > > Regards. > > > Andras > > > (Which is my first name, it's all mixed up in Hungarian :)) > > > > > > On Mon, 26 May 2003 11:51:03 -0700, Barry Lind <blind@xythos.com> > wrote: > > > > > > > > >>Gerlits, > > >> > > >>I still don't understand your problem. From what I can see the > database > > >>is doing the correct thing. You issue a bunch of selects that will all > > >>return the same value, and then you try to insert that value into a > > >>table with a unique index and you end up with duplicate key in index > > > > > > errors. > > > > > >>thanks, > > >>--Barry > > >> > > >>Gerlits AndrXs wrote: > > >> > > >>>Those stacktraces are exactly my concern. I don't expect my code to > > > > > > behave > > > > > >>>like that :). > > >>> > > >>>On Mon, 26 May 2003 11:30:50 -0700, Barry Lind <blind@xythos.com> > > > > > > wrote : > > > > > >>> > > >>>>Gerlite, > > >>>> > > >>>>I ran the test program you submitted and it seems to run OK (other > than > > >>>>some duplicate key in index errors). What is the problem you are > > >>>>seeing? Specifically what are you expecing to happen, and how does > > > > > > what > > > > > >>>>you are seeing differ from your expectatations. > > >>>> > > >>>>thanks, > > >>>>--Barry > > >>>> > > >>>>Gerlits AndrXs wrote: > > >>>> > > >>>> > > >>>>>Attached you'll find a simple multi-threaded example of a couple of > > >>>>>SERIALIZABLE transactions. I hope, I'm not making a complete ass of > > >>> > > >>>myself, > > >>> > > >>> > > >>>>>but it seems that the JDBC driver is unprepared to handle > simultaneous > > >>>>>SERIALIZABLE transactions. > > >>>>> > > >>>>>The table structure to test with is really simple: > > >>>>> > > >>>>>CREATE TABLE test ( > > >>>>> id integer UNIQUE NOT NULL > > >>>>>); > > >>>>> > > >>>>>The program tries to access the database for the highest id > available, > > >>> > > >>>then > > >>> > > >>> > > >>>>>use it in a preparedstatement. > > >>>>> > > >>>>>(The reason we do that is to prepare for the worst DB server > > > > > > available, > > > > > >>>we > > >>> > > >>> > > >>>>>know that there are other ways to do this in postgres.) > > >>>>> > > >>>>>It first opens the connections, stores them, than hands them to the > > >>> > > >>>threads. > > >>> > > >>> > > >>>>>No connection is issued twice simultaneously. > > >>>>> > > >>>>>Please edit the variables at the top, but check not to have more > > >>>>>InserterThreads than dbConnections. > > >>>>> > > >>>>>Thanks > > >>>>>Andras Gerlits > > >>>>> > > >>>>> > > >>>>>--------------------------------------------------------------------- > -- > > > > > > - > > > > > >>>>> > > >>>>>---------------------------(end of broadcast)------------------------ > -- > > > > > > - > > > > > >>>>>TIP 3: if posting/reading through Usenet, please send an appropriate > > >>>>>subscribe-nomail command to majordomo@postgresql.org so that your > > >>>>>message can get through to the mailing list cleanly > > >>>> > > >>>> > > >>>> > > >>>> > > >>>> > > >> > > >> > > >> > > >> > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Dave Cramer <Dave@micro-automation.net>