what is a phantom read ?

Discussion in 'Java' started by gk, May 20, 2010.

  1. gk

    gk Guest

    Here is a Phantom read example I read:

    /* Query 1 */

    SELECT * FROM users
    WHERE age BETWEEN 10 AND 30;

    return 2 records.



    /* Query 2 */
    INSERT INTO users VALUES ( 3, 'Bob', 27 );
    COMMIT;



    /* Query 1 */
    SELECT * FROM users
    WHERE age BETWEEN 10 AND 30;


    return 3 records.



    see It seems to me normal . I don't understand why they are called
    'Phantom read' . Look , all transactions are happening in different
    time , so we are getting the latest data always. Initially there
    were 2 records , Later on , 1 record inserted ..so when we run Query 1
    again, we get the updated data i.e 3 records.

    So,what is wrong here ? what to be worried here ? why its called
    phantom read ?
     
    gk, May 20, 2010
    #1
    1. Advertising

  2. gk

    Jeff Higgins Guest

    On 5/20/2010 8:50 AM, gk wrote:
    > Here is a Phantom read example I read:
    >
    > /* Query 1 */
    >
    > SELECT * FROM users
    > WHERE age BETWEEN 10 AND 30;
    >
    > return 2 records.
    >
    >
    >
    > /* Query 2 */
    > INSERT INTO users VALUES ( 3, 'Bob', 27 );
    > COMMIT;
    >
    >
    >
    > /* Query 1 */
    > SELECT * FROM users
    > WHERE age BETWEEN 10 AND 30;
    >
    >
    > return 3 records.
    >
    >
    >
    > see It seems to me normal . I don't understand why they are called
    > 'Phantom read' . Look , all transactions are happening in different
    > time , so we are getting the latest data always.


    What has time got to do with it?

    Initially there
    > were 2 records , Later on , 1 record inserted ..so when we run Query 1
    > again, we get the updated data i.e 3 records.
    >
    > So,what is wrong here ? what to be worried here ? why its called
    > phantom read ?


    Did you read the entire article?
    <http://en.wikipedia.org/wiki/Isolation_(database_systems)>
     
    Jeff Higgins, May 20, 2010
    #2
    1. Advertising

  3. gk

    gk Guest

    On May 20, 6:14 pm, Jeff Higgins <> wrote:
    > On 5/20/2010 8:50 AM, gk wrote:
    >
    >
    >
    >
    >
    > > Here is a Phantom read example I read:

    >
    > > /* Query 1 */

    >
    > > SELECT * FROM users
    > > WHERE age BETWEEN 10 AND 30;

    >
    > > return 2 records.

    >
    > > /* Query 2 */
    > > INSERT INTO users VALUES ( 3, 'Bob', 27 );
    > > COMMIT;

    >
    > > /* Query 1 */
    > > SELECT * FROM users
    > > WHERE age BETWEEN 10 AND 30;

    >
    > > return 3 records.

    >
    > > see  It seems to me normal . I don't understand why they are  called
    > > 'Phantom read'  .  Look , all transactions are happening in different
    > > time , so we are getting the latest data always.

    >
    > What has time got to do with it?
    >
    > Initially there
    >
    > > were 2 records , Later on , 1 record inserted ..so when we run Query 1
    > > again, we get the updated data i.e 3 records.

    >
    > > So,what is wrong here ? what to be worried here ? why its called
    > > phantom read ?

    >
    > Did you read the entire article?
    > <http://en.wikipedia.org/wiki/Isolation_(database_systems)>- Hide quoted text -
    >
    > - Show quoted text -



    it seems to me perfectly normal . Do you see any trouble in this
    scenario ? I don't understand where is the trouble yet . why the
    trouble will come up ?
     
    gk, May 20, 2010
    #3
  4. gk

    Lew Guest

    gk wrote:
    >>> Here is a Phantom read example I read:

    >>
    >>> /* Query 1 */

    >>
    >>> SELECT * FROM users
    >>> WHERE age BETWEEN 10 AND 30;

    >>
    >>> return 2 records.

    >>
    >>> /* Query 2 */
    >>> INSERT INTO users VALUES ( 3, 'Bob', 27 );
    >>> COMMIT;

    >>
    >>> /* Query 1 */
    >>> SELECT * FROM users
    >>> WHERE age BETWEEN 10 AND 30;

    >>
    >>> return 3 records.

    >>
    >>> see It seems to me normal . I don't understand why they are called
    >>> 'Phantom read' . Look , all transactions are happening in different
    >>> time , so we are getting the latest data always.


    Jeff Higgins wrote:
    >> What has time got to do with it?


    Pay attention here, gk!

    gk wrote:
    >> Initially there
    >>
    >>> were 2 records , Later on , 1 record inserted ..so when we run Query 1
    >>> again, we get the updated data i.e 3 records.

    >>
    >>> So,what is wrong here ? what to be worried here ? why its called
    >>> phantom read ?


    Jeff Higgins wrote:
    >> Did you read the entire article?
    >> <http://en.wikipedia.org/wiki/Isolation_(database_systems)>


    The key word here is "entire". You might otherwise miss
    "Note that *transaction 1 executed the same query twice*. [emph. orig.] If the
    highest level of isolation were maintained, the same set of rows should be
    returned both times, and indeed that is what is mandated to occur in a
    database operating at the SQL SERIALIZABLE isolation level. However, at the
    lesser isolation levels, a different set of rows may be returned the second time."

    The fact that the same query returns different results at lesser levels means
    that one or both results are "phantoms" - not the real answer.

    > it seems to me perfectly normal . Do you see any trouble in this
    > scenario ? I don't understand where is the trouble yet . why the
    > trouble will come up ?


    The part you're missing is that the two queries occur *inside the same
    transaction*. That's *inside the same transaction*. It's the fact that it's
    the *same* transaction getting different results that makes it a "problem".
    If the isolation level is low, then the transaction is not isolated (get it?)
    from the effects of the other transaction. Were the two queries in different
    transactions the isolation level would be irrelevant, but they're in the same
    transaction.

    This is not to say you always need repeatable-read isolation, but when you do,
    phantom reads are a "problem".

    Why might you need repeatable read? Well, if you're building intermediate
    results, say bringing in a set of rows to process, you could get bizarre
    results if that set changes while the transaction progresses. It's sort of
    like a 'ConcurrentModificationException' in the collections classes. You
    can't build a house on shifting sands.

    If you could, you wouldn't bother putting the multiple queries in the *same
    transaction*.

    --
    Lew
     
    Lew, May 20, 2010
    #4
  5. gk

    Lew Guest

    Lew wrote:
    > This is not to say you always need repeatable-read isolation, but when
    > you do, phantom reads are a "problem".


    s/repeatable[- ]read/serializable/g

    --
    Lew
     
    Lew, May 20, 2010
    #5
  6. gk <> wrote:
    >> > see  It seems to me normal . I don't understand why they are  called
    >> > 'Phantom read'

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

    > it seems to me perfectly normal . Do you see any trouble in this
    > scenario ? I don't understand where is the trouble yet . why the
    > trouble will come up ?


    It may be normal, but there are also other definitions of "normal".
    Also, a phantom isn't necessarily something abnormal.

    The phantomity lies in that you get something back from a read, but
    cannot be sure that that thing you just read is still there exactly
    the same way the very next nanosecond.

    Other isolation levels, otoh, will guarantee, that what you saw
    once (exactly those two lines), you'll see (or be able to update/
    delete) anytime later until *your* session does a commit or rollback.

    In Java, access to shared variables (e.g. fields of instances known
    to more than one thread) are also like phantom reads, unless all
    writing threads agree on respecting some particular lock: then some
    thread holding that lock will get repeatable reads until it drops
    the lock.
     
    Andreas Leitgeb, May 20, 2010
    #6
  7. gk

    gk Guest

    On May 20, 6:59 pm, Lew <> wrote:
    > gk wrote:
    > >>> Here is a Phantom read example I read:

    >
    > >>> /* Query 1 */

    >
    > >>> SELECT * FROM users
    > >>> WHERE age BETWEEN 10 AND 30;

    >
    > >>> return 2 records.

    >
    > >>> /* Query 2 */
    > >>> INSERT INTO users VALUES ( 3, 'Bob', 27 );
    > >>> COMMIT;

    >
    > >>> /* Query 1 */
    > >>> SELECT * FROM users
    > >>> WHERE age BETWEEN 10 AND 30;

    >
    > >>> return 3 records.

    >
    > >>> see  It seems to me normal . I don't understand why they are  called
    > >>> 'Phantom read'  .  Look , all transactions are happening in different
    > >>> time , so we are getting the latest data always.

    > Jeff Higgins wrote:
    > >> What has time got to do with it?

    >
    > Pay attention here, gk!
    >
    > gk wrote:
    > >> Initially there

    >
    > >>> were 2 records , Later on , 1 record inserted ..so when we run Query 1
    > >>> again, we get the updated data i.e 3 records.

    >
    > >>> So,what is wrong here ? what to be worried here ? why its called
    > >>> phantom read ?

    > Jeff Higgins wrote:
    > >> Did you read the entire article?
    > >> <http://en.wikipedia.org/wiki/Isolation_(database_systems)>

    >
    > The key word here is "entire".  You might otherwise miss
    > "Note that *transaction 1 executed the same query twice*. [emph. orig.] If the
    > highest level of isolation were maintained, the same set of rows should be
    > returned both times, and indeed that is what is mandated to occur in a
    > database operating at the SQL SERIALIZABLE isolation level. However, at the
    > lesser isolation levels, a different set of rows may be returned the second time."
    >
    > The fact that the same query returns different results at lesser levels means
    > that one or both results are "phantoms" - not the real answer.
    >
    > > it seems to me perfectly normal . Do you see any trouble in this
    > > scenario ? I don't understand where is the trouble yet . why the
    > > trouble will come up ?

    >
    > The part you're missing is that the two queries occur *inside the same
    > transaction*.  That's *inside the same transaction*.  It's the fact that it's
    > the *same* transaction getting different results that makes it a "problem".
    > If the isolation level is low, then the transaction is not isolated (get it?)
    > from the effects of the other transaction.  Were the two queries in different
    > transactions the isolation level would be irrelevant, but they're in the same
    > transaction.
    >
    > This is not to say you always need repeatable-read isolation, but when you do,
    > phantom reads are a "problem".
    >
    > Why might you need repeatable read?  Well, if you're building intermediate
    > results, say bringing in a set of rows to process, you could get bizarre
    > results if that set changes while the transaction progresses.  It's sort of
    > like a 'ConcurrentModificationException' in the collections classes.  You
    > can't build a house on shifting sands.
    >
    > If you could, you wouldn't bother putting the multiple queries in the *same
    > transaction*.
    >
    > --
    > Lew


    What I understand I am summarizing below

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

    Whats the resolution then ? Do we have to do it anything from java
    side . Or it will be taken care of database itself automatically ?
     
    gk, May 20, 2010
    #7
  8. gk

    Lew Guest

    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#setTransactionIsolation(int)>

    --
    Lew
     
    Lew, May 20, 2010
    #8
  9. gk wrote:
    >
    > Whats the resolution then ? Do we have to do it anything from java
    > side . Or it will be taken care of database itself automatically ?


    Java is simply reporting what the DBMS returns. If you want stricter
    isolation, you need to tell the DBMS to apply it (which you can do via
    JDBC.)
     
    Mike Schilling, May 20, 2010
    #9
  10. gk

    gk Guest

    On May 20, 11:32 pm, Lew <> 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 ?
     
    gk, May 21, 2010
    #10
  11. gk

    Lew Guest

    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.
     
    Lew, May 21, 2010
    #11
  12. gk

    gk Guest

    On May 21, 4:48 pm, Lew <> 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.
     
    gk, Jun 19, 2010
    #12
  13. gk

    Lew Guest

    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.
     
    Lew, Jun 19, 2010
    #13
  14. On Thu, 20 May 2010 23:59:29 -0700, gk wrote:

    > On May 20, 11:32 pm, Lew <> 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 |
     
    Martin Gregorie, Jun 19, 2010
    #14
  15. gk

    gk Guest

    On Jun 19, 6:17 pm, Martin Gregorie <>
    wrote:
    > On Thu, 20 May 2010 23:59:29 -0700, gk wrote:
    > > On May 20, 11:32 pm, Lew <> 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 ?
     
    gk, Jun 19, 2010
    #15
  16. gk

    Lew Guest

    gk wrote:
    >>> 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 ?


    Martin Gregorie wrote:
    >> 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.


    gk wrote:
    > 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 ?


    Did you read my answer to this question?
    >> 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)"


    Phantom reads are a consequence of not setting transaction isolation to at
    least REPEATABLE READ.

    You should read the answers to the questions you ask.

    Please trim your posts.

    --
    Lew
     
    Lew, Jun 19, 2010
    #16
  17. gk

    gk Guest

    On Jun 19, 11:27 pm, Lew <> wrote:

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

    > Phantom reads are a consequence of not setting transaction isolation to at
    > least REPEATABLE READ.
    >
    > You should read the answers to the questions you ask.
    >
    > Please trim your posts.
    >
    > --
    > Lew


    did you look at the link I posted .
    http://docs.google.com/View?id=dc83hzcs_380f8hkzdfb

    Those are taken from Wikipedia . What I am asking was to see the
    Definition of PHANTOM READ and NON-REPEATABLE READ there. It seems
    to me that both of them are discussing the same problem .Both of
    them shows *Transaction 1 showing different results in two runs
    of Query 1*

    where is the key difference(if any) ?
     
    gk, Jun 20, 2010
    #17
  18. gk

    Lew Guest

    gk wrote:
    > Those are taken from Wikipedia . What I am asking was to see the
    > Definition of PHANTOM READ and NON-REPEATABLE READ there. It seems
    > to me that both of them are discussing the same problem .Both of
    > them shows *Transaction 1 showing different results in two runs
    > of Query 1*
    >
    > where is the key difference(if any) ?


    Phantom read is a different number of rows returned from the query.
    Non-repeatable read is different values returned for a given row.

    Once again, this question was answered in the web page to which you've been
    repeatedly directed:
    http://en.wikipedia.org/wiki/Isolation_(database_systems)

    specifically at
    http://en.wikipedia.org/wiki/Isolation_(database_systems)#Repeatable_reads_.28phantom_reads.29
    vs.
    http://en.wikipedia.org/wiki/Isolation_(database_systems)#Read_Committed_.28Non-repeatable_reads.29

    --
    Lew
     
    Lew, Jun 20, 2010
    #18
  19. gk

    Lew Guest

    Lew wrote:
    > Phantom reads are a consequence of not setting transaction isolation to
    > at least REPEATABLE READ.


    Oops. I mean at least *stricter than* REPEATABLE READ, i.e., SERIALIZABLE. I
    apologize for the misinformation.

    --
    Lew
     
    Lew, Jun 20, 2010
    #19
  20. gk

    gk Guest

    On Jun 20, 10:34 am, Lew <> wrote:
    > gk wrote:
    >
    > > where is the key difference(if any) ?

    >
    > Phantom read is a different number of rows returned from the query.


    True . because in the example they are using "INSERT" in transaction
    2

    surprisingly , Phantom read also called "REPEATABLE READ" . Is it
    because they are not MODIFYING the values but adding some extra
    records into it ? i.e it repeates the values with some additional
    records.


    > Non-repeatable read is different values returned for a given row.


    True . because in the example they are using "UPDATE" in transaction
    2.


    However,both of them are showing faulty results at the end of
    Transaction 1.


    > Once again, this question was answered in the web page to which you've been
    > repeatedly directed:http://en.wikipedia.org/wiki/Isolation_(database_systems)
    >
    > specifically athttp://en.wikipedia.org/wiki/Isolation_(database_systems)#Repeatable_...
    > vs.http://en.wikipedia.org/wiki/Isolation_(database_systems)#Read_Commit....
    >
    > --
    > Lew
     
    gk, Jun 20, 2010
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rod Billett

    Phantom space and resize problem.

    Rod Billett, Oct 24, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    464
    Rod Billett
    Oct 24, 2003
  2. Thomas Scheiderich

    Phantom folder in path

    Thomas Scheiderich, Jun 27, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    476
    Thomas Scheiderich
    Jun 27, 2004
  3. tfs

    Phantom folder in path

    tfs, Jun 27, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    508
    John Saunders
    Jun 27, 2004
  4. Frank
    Replies:
    1
    Views:
    298
    Eric Pearson
    Feb 15, 2006
  5. TB

    phantom records

    TB, Mar 29, 2006, in forum: ASP .Net
    Replies:
    9
    Views:
    570
Loading...

Share This Page