Re: pg and transactions

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
>
>