Re: Fwd: Re: Synchronization issues with pg73jdbc3.jar and

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>