db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Derby : Lock time out in Read commited mode
Date Fri, 02 Mar 2007 10:21:25 GMT
Prashant <prashant@pramati.com> 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

Mime
View raw message