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
