Mamta Satoor wrote:
Hi,
I might be showing my ignorance here but will probably learn in the
process. I looked at
http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts15366.html
<http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts15366.html> and
found under Table 3 that for TRANSACTION_READ_COMMITTED, it is possible
to see phantom reads. I think what Alex is encountering is a phantom
read and not a dirty read (as per the examples in Table 2) and hence I
agree with Alex that CLIENT_B should not block in the following scenario.
thanks,
Mamta
On 1/18/07, *Alex Boisvert* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
Hi,
I stumbled upon an interesting locking behavior in Derby 10.2.2.0
<http://10.2.2.0/> yesterday and thought I'd post to the list to get
a better understanding of what's happening under the covers.
Assume a table with two indexes:
create table MY_TABLE (
ID bigint,
FOO varchar(255),
BAR varchar(255),
primary key (ID)
);
create index IDX_FOO on MY_TABLE (FOO);
create index IDX_BAR on MY_TABLE (BAR);
With isolation level READ_COMMITTED, here's a simple concurrent
scenario:
CLIENT_A: start transaction
CLIENT_A: insert into MY_TABLE values (1, 'FOO-1', 'BAR');
===> OK (1 row inserted)
CLIENT_B: start transaction
CLIENT_B: insert into MY_TABLE values (2, 'FOO-2', 'BAR');
===> OK (1 row inserted)
CLIENT_B: select * from MY_TABLE where FOO = 'FOO-2'
===> OK (1 row matched)
CLIENT_B: select * from MY_TABLE where FOO = 'FOO-2' and BAR = 'BAR'
===> OK (1 row matched)
CLIENT_B: select * from MY_TABLE where BAR = 'BAR' and FOO = 'FOO-2'
===> OK (1 row matched)
CLIENT_B: select * from MY_TABLE where BAR = 'BAR'
===> BLOCKS!
at that point CLIENT_B blocks until CLIENT_A commits or rollback.
And if CLIENT_A also selects BAR = 'BAR' we get into a deadlock
situation.
What I don't understand is why CLIENT_B blocks to obtain a read lock
on a row that has not been committed by CLIENT_A. Would anyone be
able to explain the underlying mechanism at work here?
As you point out it would be "legal" to return without waiting from
CLIENT_B:select * from MY_TABLE where BAR = 'BAR'. But Derby only uses
locks to implement isolation level. It does not have versioning or any
special tags on the data. So Derby doesn't know when it does the select
that the row: 1, 'FOO-1', 'BAR' is a new row in the DB that has been
inserted by an uncommitted transaction. Note the problem is even more
interesting if the row is not inserted but an update by an uncommitted
transaction.
It just uses locks. So CLIENT_A has an X lock on the row, and the select
see's that the row fulfills the where clause and the only way Derby
currently has to tell if the row is committed or not is to wait until
CLIENT_B can be granted a shared row lock on the row.
So in Derby the biggest concurrency win for using READ COMMITTED is
not that you can read data currently being updated by other users (as
exclusive locks will always be held until end of transaction), it is
that users will release read locks much sooner than in higher isolation
levels, and thus your update is more likely to procede without being
interfered by other readers.
regards,
alex
