Re: pg and transactions

On Mon, 30 Dec 2002, Medi Montaseri wrote:

> You might want to experiment your case by just using psql(1)....this way
> you can
> see if any other layer is introducing issues...
>
> Get two xterms (representing your connections), connect to your test
> database and
> go from there...
>
> Also you might want to use lock to really lock a table....but I don't
> think a transaction
> implies locking a table...ie
>
> Transaction A starts earlier
> Transaction A does some work
> Transaction B starts
> Transaction B does some work
> Transaction B ends
> Transaction A does some more work
> Transaction A ends

In the example connection 1 starts a transaction but doesn't commit before
connection 2 starts it's own transaction. I see nothing wrong in this example
of the select of the counter in connection 2 returning 1000 in this instance.
Mind you I don't know what jdbc might be trying to do with the transaction
isolation level.

On the face of it it's a little misunderstanding. You need to either lock the
whole table, as Medi says, use a select ... for update or change the
transaction isolation level. These facilities are discussed in another current
thread, with the subject 'lock table question', on the -general list. Tom Lane
has just given a nice summary in that thread.

Nigel Andrews

>
> pginfo wrote:
>
> >Hi,
> >
> >Actualy I do not need sequences. It was only a litle example. The real
> >situation is very complex.
> >
> >Ok I will try to explain the problem.
> >
> >I have one simple table tableA (ID int, value float8 ) with one record
> >(1,1000).
> >
> >From workstation1 I create jdbc connection ( Connection 1). I do not use
> >nothing special and use the ps jdbc defaults.
> >I check the transaction isolation level by calling
> >MyConnection.getTransactionIsolation().
> >I receive 2 ( it is TRANSACTION_RED_COMMITED . In oracle I get the same).
> >
> >From workstation2 I create also jdbc connection (Connection2).
> >
> >And the test example executes :
> >
> >Connection1-> begin transaction ( the real command is
> >MyConnection.setAutoCommit(false) ).
> >Connection1-> read the record from tableA ( I create statement Statement st
> >= MyConnection.createStatement();
> > ResultSet rs = st.executeQuery("select values from tableA where ID = 1;
> >
> > rs.next();
> > long myValue = rs.getLong(1)
> > ). It returns 1000.
> >
> >Connection1-> increase the value to 1001. ( real command in java is myValue
> >++; )
> >
> >Connection1-> update the new value in tableA by (st.executeUpdate("update
> >tableA set value = 1001 where ID = 1");).
> >
> >Connection2-> begin transaction ( All command for connaction 2 are the same
> >as for connection 1)
> >
> >Connection2-> read the record from tableA ( select values from tableA
> >where ID = 1). It returns 1000. !!!
> >
> >Connection2-> increase the value with 1. It gives 1001.
> >
> >Connection2-> update the new value in tableA by update tableA set value
> >= 1001 where ID = 1.
> >
> >Connection2->commit.(MyConnection.setAutoCommit(true))
> >
> >do some other jobs with connection1.
> >
> >Connection1->commit
> >
> >Actualy I use the table as a simple counter for unique values and I
> >expect that Connection2 will wait until Connection1 finish the task.
> >
> >On oracle the second workstation wait until the end of all the tasks in
> >workstation1 .
> >
> >Also as I wrote the both (pg and oracle have the same transaction isolation
> >level).
> >
> >My question is : Are pg and pg jdbc supporting correct the transactions and
> >isolation levels ?
> >For me it is very important because in the application we nead realy working
> >transactions.
> >Also the working application on oracle is running from 2 years without any
> >problems with data.
> >
> >
> >I searched for info about how is pg jdbc working, but do not found any
> >(jdbc.postgresql.org)
> >
> >I hope some one can help me,
> >regards
> >ivan.
> >
> >Bruno Wolff III wrote:
> >
> >
> >
> >>On Sat, Dec 28, 2002 at 16:13:17 +0100,
> >> pginfo <pginfo(at)t1(dot)unisoftbg(dot)com> wrote:
> >>
> >>
> >>>Actualy I use the table as a simple counter for unique values and I
> >>>expect that Connection2 will wait until Connection1 finish the task.
> >>>Is it normal for pg to work so with transactions?
> >>>If yes how can I lock all the tables after beginning the transaction?
> >>>If no where can I make mistake?
> >>>
> >>>
> >>If you just want a unique value use sequences. They will be faster.
> >>
> >>You didn't provide the exact commands you used in your test. Without
> >>seeing them it is hard to tell what you might have done wrong.