Re: pg and transactions

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@t1.unisoftbg.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@postgresql.org