Re: pg and transactions
От | pginfo |
---|---|
Тема | Re: pg and transactions |
Дата | |
Msg-id | 3E114C92.69B2B2C3@t1.unisoftbg.com обсуждение исходный текст |
Ответ на | pg and transactions (pginfo <pginfo@t1.unisoftbg.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: