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 ? 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. ? Would it matter if the Table in question has indexes on some columns ? <snip/> Thank you. -Prashant
