Alex Boisvert 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?
I think most database systems that do not have multiversion concurrency
will behave as describe. When a scan encounters a exclusively locked
row, it will generally not know whether the uncommitted operation is an
insert or an update. For the latter, the row cannot be skipped since
its previous version should be included in the result set if the
transaction that updated the row is rolled back.
--
Øystein
|