Re: pg and transactions

Hi,

I think I found how it is working.
It is not as expected, but at the moment I can fix the problem.

I tryed to change the transaction level to seliazable, but it is working not
flexible.
The problem is that the second transaction do not wait until ending ( by commit
or rollback ) the first one.
The second drops with seriasable exception.
I do not know why is it designed so.

The solution for me is to use select ... for update ( as Oskar Berrgren wrote).
It is not the bes solution because I will need to rewrite one big part from the
code.

The question is:

Is it possible that in transaction level SERIALIZABLE the second transaction not
to drop and to wait as if I use
select ... for update with the default transaction isolation level ?

It will be great if it exist any setup for this case !

regards,
ivan.

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
>
> 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.
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >>
> >>
> >
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org