what is a phantom read ?
in [Java Programming]
|
From: Andreas Leitgeb on 20 May 2010 10:21 gk <srcjnu(a)gmail.com> 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.
From: gk on 20 May 2010 14:27 On May 20, 6:59 pm, Lew <no...(a)lewscanon.com> 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 ?
From: Lew on 20 May 2010 14:32 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
From: Mike Schilling on 20 May 2010 15:30 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.)
From: gk on 21 May 2010 02:59 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 ?
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 5 Prev: consuming the web service Next: Need help designing some JUnit tests |