db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: table locking
Date Mon, 28 Nov 2005 18:21:50 GMT
I believe Derby is using row locks and that it is blocking
on a row lock request on the row locked by session 1.  To
verify this you should set the locking properties to get
extra information printed to the derby.log.  See the faq on
how to debug this:
http://db.apache.org/derby/faq.html#debug_lock_timeout

Note that without key no particular order of rows is guaranteed
in a table, so one can't assume that a row inserted before another
will be encountered first in a scan.  Also for performance reasons
Derby, when scanning a number of rows - Derby will try to process
them as a group before sending them back to the client.  So in this
case it is likely that the 1st row has been seen and sitting in
the server side but is not returned until the row lock is granted
on the second row.

to show row level locking is happening try any of the following:
o you should be able to insert into the table from 2 concurrent
   sessions without blocking.
o add an index on mscolor and in session 2 do
   select * from mscolor where col1 = 'a';

Mehran Sowdaey wrote:
> The actual default behavior that we see is table level locking when
> autocommit is turned off not row level locking. Simple test:
> 
> Before lock:
> 
> Session 1, and Session 2 can see this row:
> ij> select * from mscolor;
> COL1      |COL2
> ---------------------
> a         |b
> 
> 1 rows selected
> ij>
> 
> 
> 
> After lock:
> 
> Session 1:
> ij> autocommit off;
> ij> insert into mscolor values ('c', 'd');
> 1 row inserted/updated/deleted
> ij>
> 
> Session 2:
> 
> ij> select * from mscolor;
> COL1      |COL2
> ---------------------
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij>
> 
> 
> Session 2 can not see anything until session 1 commits or rolls back.
> 
> 
> Mike Matrigali wrote On 11/22/05 16:07,:
> 
>>I guess that table is a little confusing.  It is not saying that
>>table level locking is the default when autocommit is off.  It is
>>saying that IF you use serializable and/or table level locking then
>>you will get less concurrency if you don't use autocommit.  There
>>is a way to force table level locking but it is not the default in
>>any case.
>>
>>By default derby applications use row level locking and read committed
>>isolation level whether you have autocommit set to true or false.
>>
>>Note even in serializable the only table level lock if you are accessing
>>tables without indexes.  If there are indexes then row locks are still
>>used.
>>
>>Mehran Sowdaey wrote:
>>
>>
>>>Please see:
>>>
>>><http://db.apache.org/derby/docs/10.0/manuals/develop/develop63.html>
>>>
>>>(Table 2). This is a 10.0 document but we have seen the same in 10.1
>>>
>>>thanks,
>>>-------
>>>mehran
>>>
>>>Mike Matrigali wrote On 11/22/05 11:24,:
>>>
>>>
>>>
>>>>Derby uses row locking by default, can you please explain why you
>>>>think it is table locking.  Derby does get table level intent locks,
>>>>to enable the row locking feature - so for instances when updating
>>>>a row in a table you will get a table level IX and a row level X, but
>>>>another transaction will also be able to get a table level IX and a
>>>>row level X lock on another row.
>>>>
>>>>There are situations depending on isolation level and lock escalation
>>>>where real table level locks are requested, not sure if you are running
>>>>into that.  More information is needed.
>>>>
>>>>Mehran Sowdaey wrote:
>>>>
>>>>
>>>>
>>>>
>>>>>Hi,
>>>>>
>>>>>When running derby in server mode if autocommit is turned off table
>>>>>locking occurs. Is there anyway to prevent that and have row level
>>>>>locking. We have tried changing a few of derby parameters without success.
>>>>>
>>>>>thanks,
>>>>>-------
>>>>>mehran
>>>>>
>>>>>
>>>>>
>>>>
>>>>
> 


Mime
View raw message