Prashant <[EMAIL PROTECTED]> writes: > Thank you Knut Anders for your prompt reply. However i am not entirely sure > your answer considered the test case scenario from the original post. Let me > try to explain more clearly. > > Please find my comment in-lined. > > <snip/> > >> >> >> The isolation level is default which I think is Read Committed[2]. >> >> >> >> Now what i do not understand is that the read query (XID 8520) is >> >> waiting to obtain a Shared (S) lock on a Row that the insert thread >> >> (XID 8519) has obtained exclusive lock for. How could this be possible >> >> in a Read committed mode ? >> > >> Since the row is locked exclusively, and possibly modified, by another >> transaction, the reader needs to wait until the other transaction is >> committed in order to be able to read committed data. If you on the >> other hand ran the transaction in read uncommitted mode, it would not >> try to obtain a shared lock (I think), and the reader would not be >> blocked (but it could read uncommitted data). > > > Ok, i understand what you are saying But Would this happen if the > writer thread is <b>"Inserting"</b> data and not updating an > existing row ?
I don't think Derby distinguishes between newly inserted and modified rows. If it did, I think you are right that it would be possible to skip reading those rows instead of waiting in read committed mode (and repeatable read). > In the test case i am running writer thread only "inserts" new Data > while the Reader thread is selecting to read from the same database. > > Why would the reader thread wait on the row currently being inserted > even while the Tx that is inserting is still active and has not > Committed. ? Even though the inserter has not committed, it has still inserted rows into the table. So a reader which scans the table will see that the rows are there, but it can't lock the rows until the inserter has unlocked them. > Would it matter if the Table in question has indexes on some columns ? It could make a difference. A query like SELECT * FROM t would definitely be blocked if there are uncommitted inserts in that table. On the other hand, a query like SELECT * FROM t WHERE x = ? might succeed without being blocked if there is an index on x, since it might be able to find the row without seeing any of the uncommitted rows. If there's no index on x, the entire table would have to be scanned and the reader would be blocked when it reached an uncommitted row. -- Knut Anders
