db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mehran Sowdaey <Mehran.Sowd...@Sun.COM>
Subject Re: table locking
Date Tue, 22 Nov 2005 23:48:41 GMT
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
>>>>
>>>>
>>>>
>>>
>>>
> 

-- 
Mehran Sowdaey
Sun Microsystems, Inc. Mailstop UBRM06-304
500 Eldorado Blvd. Broomfield, CO. 80021
Pager: 1-800-759-8352 (PIN 1855303)
Phone: 303-272-3192


Mime
View raw message