Prev: consuming the web service
Next: Need help designing some JUnit tests
From: Lew on 21 May 2010 07:48
gk wrote:
>>> Whats the resolution then ? Do we have to do it anything from java [sic]
>>> side . Or it will be taken care of database itself automatically ?

Lew wrote:
>> Set the transaction isolation level for the database.
>>
>> The answer, as usual, lies in the Javadocs:
>>
>> <http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#setTra...)>

gk quoted the sig:
>> --
>> Lew

gk, please don't quote sigs.

gk wrote:
> Interesting ...Yes . I can see 5 field attributes .
>
> while coding , shall I do this ?
>
> conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);

Don't dereference static members through the instance, dereference them
through the type. You should have written 'Connection.TRANSACTION_SERIALIZABLE'.

> //select record in table1
> // insert record in table1
> //select record in table1
> conn.commit.

That depends. You don't indicate where you wish the transactions to begin and
end, or even how many transactions you want.

You don't necessarily need the highest level of transaction isolation, that's
why there are more than one level.

> is there any other extra code I need ? please let me know .where and
> how do I write the trasaction bengin and trasaction end in this code ?
^----------/

For where, that depends on where you want to put the transaction boundaries.

For how, the answer, as usual, lies in the Javadocs:
<http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#setAutoCommit(boolean)>
<http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#commit()>
<http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#rollback()>

Essentially, all three of those calls begin a transaction and the latter two
also end one.

Beyond that, have you considered reading the Java Tutorials? Google?
<http://java.sun.com/docs/books/tutorial/jdbc/index.html>

GIYF.

You can't learn a topic comprehensively very well only by asking questions in
Usenet on tiny details. You need an overview and a foundation. The materials
are out there; learn to use them. Get in the habit of using them. You will
make poor progress until you do.

--
Lew
Light a man a fire and you warm him for an hour.
Set a man on fire and you warm him for the rest of his life.
Don't quote sigs.
From: gk on 19 Jun 2010 07:46
On May 21, 4:48 pm, Lew <no...(a)lewscanon.com> wrote:
> gk wrote:
> >>> Whats the resolution then ?  Do we have to do it anything from java [sic]
> >>> side . Or it will be taken care of database itself automatically ?
> Lew wrote:
> >> Set the transaction isolation level for the database.
>
> >> The answer, as usual, lies in the Javadocs:
>
> >> <http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#setTra....)>
>
> gk quoted the sig:
>
> >> --
> >> Lew
>
> gk, please don't quote sigs.
>
> gk wrote:
> > Interesting ...Yes . I can see 5 field  attributes .
>
> > while coding , shall I do this ?
>
> > conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);
>
> Don't dereference static members through the instance, dereference them
> through the type.  You should have written 'Connection.TRANSACTION_SERIALIZABLE'.
>
> >   //select  record in table1
> >   // insert record in table1
> >   //select  record in table1
> > conn.commit.
>
> That depends.  You don't indicate where you wish the transactions to begin and
> end, or even how many transactions you want.
>
> You don't necessarily need the highest level of transaction isolation, that's
> why there are more than one level.
>
> > is there any other extra code  I need ? please let me know .where and
> > how do I write the trasaction bengin and trasaction end in this code ?
>
>                                    ^----------/
>
> For where, that depends on where you want to put the transaction boundaries.
>
> For how, the answer, as usual, lies in the Javadocs:
> <http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#setAut...)>
> <http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#commit()>
> <http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#rollback()>
>
> Essentially, all three of those calls begin a transaction and the latter two
> also end one.
>
> Beyond that, have you considered reading the Java Tutorials?  Google?
> <http://java.sun.com/docs/books/tutorial/jdbc/index.html>
>
> GIYF.
>
> You can't learn a topic comprehensively very well only by asking questions in
> Usenet on tiny details.  You need an overview and a foundation.  The materials
> are out there; learn to use them.  Get in the habit of using them.  You will
> make poor progress until you do.
>
> --
> Lew
> Light a man a fire and you warm him for an hour.
> Set a man on fire and you warm him for the rest of his life.
> Don't quote sigs.

Does PHANTOM READ and NON REPEATABLE READ are same thing ?

I read in Jguru.com about NON REPEATEABLE READ as follows

"....One of the ISO-ANSI SQL defined "phenomena" that can occur with
concurrent transactions. If one transaction reads a row, then another
transaction updates or deletes the row and commits, the first
transaction, on re-read, gets modified data or no data. This is an
inconsistency problem within a transaction and addressed by isolation
levels...."


BUT this is similar to PHANTOM READ we discussed so far !

Does NON REPEATEABLE READ and PHANTOM READ are same thing ? I don't
find the difference.
From: Lew on 19 Jun 2010 09:06
gk wrote:
>> --
>> Lew
>> Light a man a fire and you warm him for an hour.
>> Set a man on fire and you warm him for the rest of his life.
>> Don't quote sigs.

Don't quote sigs.

Do trim your posts.

Don't quote sigs.

Pay attention to what you're posting. Be conscious, and show some effort.

Don't quote sigs.

> Does PHANTOM READ and NON REPEATABLE READ are same thing ?

http://en.wikipedia.org/wiki/Isolation_(database_systems)

> I read in Jguru.com about NON REPEATEABLE READ as follows
>
> "....One of the ISO-ANSI SQL defined "phenomena" that can occur with
> concurrent transactions. If one transaction reads a row, then another
> transaction updates or deletes the row and commits, the first
> transaction, on re-read, gets modified data or no data. This is an
> inconsistency problem within a transaction and addressed by isolation
> levels...."
>
>
> BUT this is similar to PHANTOM READ we discussed so far !
>
> Does NON REPEATEABLE READ and PHANTOM READ are same thing ? I don't
> find the difference.

The Wikipedia article up at the start of this thread addresses your question.

http://en.wikipedia.org/wiki/Isolation_(database_systems)
"Repeatable reads (phantom reads)"

On 05/20/2010 09:14 AM, Jeff Higgins wrote:
>> Did you read the entire article?
>> <http://en.wikipedia.org/wiki/Isolation_(database_systems)>

--
Lew
Don't quote sigs.
From: Martin Gregorie on 19 Jun 2010 09:17
On Thu, 20 May 2010 23:59:29 -0700, gk wrote:

> On May 20, 11:32 pm, Lew <no...(a)lewscanon.com> wrote:
>> gk wrote:
>> > Yes. I see Query 1 has been executed  two times in the same
>> > transaction i.e[.,] Transaction 1.
>>
>> > same select query should return same results in same transaction
>>
>> Sometimes.
>>
>> > irrespective of number of execution. But we see first time select
>> > query execution has got 2 records and second time select query
>> > excution has returned 3 records though the execution are in the same
>> > transaction . This is very bad .  This can happen only when there is
>>
>> It's sometimes bad.  Not always.
>>
>> > low  isolation level . Here we faced a low isolation problem and
>> > hence we are getting this discrepancy. There is another trasaction
>> > i.e Transaction 2 is interferring Transaction 1's  results . And so
>> > we are getting a wrong result sets in Trasaction 1.  This is called
>> > phantom read.
>>
>> Correct.
>>
>> > Whats the resolution then ?  Do we have to do it anything from java
>> > side . Or it will be taken care of database itself automatically ?
>>
>> Set the transaction isolation level for the database.
>>
>> The answer, as usual, lies in the Javadocs:
>>
>> <http://java.sun.com/javase/6/docs/api/java/sql/
Connection.html#setTra...)>
>>
>> --
>> Lew
>
> Interesting ...Yes . I can see 5 field attributes .
>
> while coding , shall I do this ?
>
> conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);
> //select record in table1
> // insert record in table1
> //select record in table1
> conn.commit.
>
>
> is there any other extra code I need ? please let me know .where and
> how do I write the trasaction bengin and trasaction end in this code ?
>
In your example you're: doing both reads and the insert in the same
transaction, so *of course* you'd expect the two selects to return
different results. On the other hand, if there were two transactions:

# 1st transaction: serialised # 2nd transaction: auto-commit
# explicit commit unit # implicit commit unit

conn.setTransactionIsolation
(conn.TRANSACTION_SERIALIZABLE);
//select record in table1
... // insert record in table1
//select record in table1
conn.commit.


...then in this case you'd be right to expect the two selects to return
the same data set because they are in the same serialisable commit unit
and the insert is outside it.

It doesn't matter whether the two transactions are run by separate
programs or both by the same program: that has no impact on transaction
isolation.


--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |
From: gk on 19 Jun 2010 13:51
On Jun 19, 6:17 pm, Martin Gregorie <mar...(a)address-in-sig.invalid>
wrote:
> On Thu, 20 May 2010 23:59:29 -0700, gk wrote:
> > On May 20, 11:32 pm, Lew <no...(a)lewscanon.com> wrote:
> >> gk wrote:
> >> > Yes. I see Query 1 has been executed  two times in the same
> >> > transaction i.e[.,] Transaction 1.
>
> >> > same select query should return same results in same transaction
>
> >> Sometimes.
>
> >> > irrespective of number of execution. But we see first time select
> >> > query execution has got 2 records and second time select query
> >> > excution has returned 3 records though the execution are in the same
> >> > transaction . This is very bad .  This can happen only when there is
>
> >> It's sometimes bad.  Not always.
>
> >> > low  isolation level . Here we faced a low isolation problem and
> >> > hence we are getting this discrepancy. There is another trasaction
> >> > i.e Transaction 2 is interferring Transaction 1's  results . And so
> >> > we are getting a wrong result sets in Trasaction 1.  This is called
> >> > phantom read.
>
> >> Correct.
>
> >> > Whats the resolution then ?  Do we have to do it anything from java
> >> > side . Or it will be taken care of database itself automatically ?
>
> >> Set the transaction isolation level for the database.
>
> >> The answer, as usual, lies in the Javadocs:
>
> >> <http://java.sun.com/javase/6/docs/api/java/sql/
>
> Connection.html#setTra...)>
>
>
>
>
>
> >> --
> >> Lew
>
> > Interesting ...Yes . I can see 5 field  attributes .
>
> > while coding , shall I do this ?
>
> > conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);
> >  //select  record in table1
> >  // insert record in table1
> >  //select  record in table1
> > conn.commit.
>
> > is there any other extra code  I need ? please let me know .where and
> > how do I write the trasaction bengin and trasaction end in this code ?
>
> In your example you're: doing both reads and the insert in the same
> transaction, so *of course* you'd expect the two selects to return
> different results. On the other hand, if there were two transactions:
>
> # 1st transaction: serialised           # 2nd transaction: auto-commit
> # explicit commit unit                  # implicit commit unit
>
> conn.setTransactionIsolation
>    (conn.TRANSACTION_SERIALIZABLE);
>  //select  record in table1
>  ...                                     // insert record in table1
>  //select  record in table1
> conn.commit.
>
> ..then in this case you'd be right to expect the two selects to return
> the same data set because they are in the same serialisable commit unit
> and the insert is outside it.
>
> It doesn't matter whether the two transactions are run by separate
> programs or both by the same program: that has no impact on transaction
> isolation.



Please see this

http://docs.google.com/View?id=dc83hzcs_380f8hkzdfb

I still don't see any difference ....both the problems are same i.e
PHANTOM READ and NON REPEATABLE READ's *Transaction 1 showing
different results in two runs of Query 1*

Where is the key difference between them then ?






First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: consuming the web service
Next: Need help designing some JUnit tests