Thread: pg and transactions
Hi, I think I have problem with pg transactions ( or I do not understend how it work). I have two connections ( running on java clients via jdbc). I have one simple table tableA (ID int, value float8 ) with one record (1,1000). And make this steps: Connection1-> begin transaction Connection1-> read the record from tableA ( select values from tableA where ID = 1). It returns 1000. Connection1-> increase the value to 1001. Connection1-> update the new value in tableA by update tableA set value = 1001 where ID = 1. Connection2-> begin transaction 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. 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. 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? The same example is working well on oracle and all the task is to port one oracle based application to pg. Many tanks and regards, ivan.
Hi, Thanks for response, I am using pg 7.2.3. Actualy I am using Statement stmt = MyConnection.createStatement(); Is it possible that I need Statement stmt = MyConnection.createStatement(ResultSet.TYPE_FROWARD_ONLY,ResultSet.CONCUR_UPDATABLE) ? I nedd that after the beinn transaction all the selects and updates from the first transaction to go to the end and after it to run the second. regards, ivan. Oskar Berggren wrote: > What are your actual queries? What version of Postgresql? > Are you using FOR UPDATE in your select statements? > > /Oskar > > On Sat, 28 Dec 2002, pginfo wrote: > > > Hi, > > > > I think I have problem with pg transactions ( or I do not understend how > > it work). > > > > I have two connections ( running on java clients via jdbc). > > > > I have one simple table tableA (ID int, value float8 ) with one record > > (1,1000). > > > > And make this steps: > > > > Connection1-> begin transaction > > Connection1-> read the record from tableA ( select values from tableA > > where ID = 1). It returns 1000. > > Connection1-> increase the value to 1001. > > Connection1-> update the new value in tableA by update tableA set value > > = 1001 where ID = 1. > > Connection2-> begin transaction > > 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. > > > > 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. > > 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? > > > > The same example is working well on oracle and all the task is to port > > one oracle based application to pg. > > > > Many tanks and regards, > > ivan. > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > >
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.
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
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@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
>>
>>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
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@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.
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@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
> >>
> >>
> >
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
What are your actual queries? What version of Postgresql? Are you using FOR UPDATE in your select statements? /Oskar On Sat, 28 Dec 2002, pginfo wrote: > Hi, > > I think I have problem with pg transactions ( or I do not understend how > it work). > > I have two connections ( running on java clients via jdbc). > > I have one simple table tableA (ID int, value float8 ) with one record > (1,1000). > > And make this steps: > > Connection1-> begin transaction > Connection1-> read the record from tableA ( select values from tableA > where ID = 1). It returns 1000. > Connection1-> increase the value to 1001. > Connection1-> update the new value in tableA by update tableA set value > = 1001 where ID = 1. > Connection2-> begin transaction > 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. > > 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. > 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? > > The same example is working well on oracle and all the task is to port > one oracle based application to pg. > > Many tanks and regards, > ivan. > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >