You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Alex Boisvert <bo...@intalio.com> on 2007/01/18 17:46:08 UTC
Derby row locking semantic
Hi,
I stumbled upon an interesting locking behavior in Derby 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?
regards,
alex
Re: Derby row locking semantic
Posted by Alex Boisvert <bo...@intalio.com>.
On 1/18/07, Øystein Grøvlen <Oy...@sun.com> wrote:
>
> 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.
Thanks for your explanation Øystein, that makes sense. Although it leaves
me wishing for higher levels of concurrency ;)
alex
Re: Derby row locking semantic
Posted by Francois Orsini <fr...@gmail.com>.
Correct - That is actually how Sybase ASE behaves (non-MVCC model).
On 1/18/07, Øystein Grøvlen <Oy...@sun.com> wrote:
>
>
> 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
>
Re: Derby row locking semantic
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
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
Re: Derby row locking semantic
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Mamta Satoor wrote (2007-01-18 13:54:28):
> 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 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.
Note the SQL definition says that phantom reads are allowed, not
required. A standards compliant DB may give you a higher isolation
level than you ask for or implement an isolation level stricter than
the SQL standard allows the DB to be.
>
> thanks,
> Mamta
>
>
> On 1/18/07, Alex Boisvert <bo...@intalio.com> wrote:
> >
> >Hi,
> >
> >I stumbled upon an interesting locking behavior in Derby 10.2.2.0yesterday
> >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?
> >
> >regards,
> >alex
> >
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: Derby row locking semantic
Posted by Mike Matrigali <mi...@sbcglobal.net>.
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* <boisvert@intalio.com
> <ma...@intalio.com>> 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
>
>
Re: Derby row locking semantic
Posted by Mamta Satoor <ms...@gmail.com>.
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 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 <bo...@intalio.com> wrote:
>
> Hi,
>
> I stumbled upon an interesting locking behavior in Derby 10.2.2.0yesterday 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?
>
> regards,
> alex
>