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: Row Lock versus Table Lock
Date Wed, 03 Jan 2007 16:04:20 GMT
locking depends on isolation level, indexing, and what rows
qualify.  Note that write locks will always be held until
end of transaction, so if the the statement is deleting more
rows than the lock escalation threshhold it will always escalate
no matter what the query.

o default isolation level is read committed, are you changing it?
o do you have an index on column

The easiest way to answer you question is to set autocommit off,
run each statement, and use the lock table to see what locks have
been set.


Inns, Jeff wrote:
> Does any know if the row lock escalation algorithm will behave 
> differently for the following two statements:
> 
>  
> 
> 1. delete from SCHEMA.TABLE where COLUMN IN (select COLUMN from 
> SCHEMA.TABLE where COLUMN <= VALUE);
> 
>  
> 
> 2. delete from SCHEMA.TABLE where  COLUMN <= VALUE;
> 
>  
> 
>  
> 
> I’m trying to prevent a table lock.  I’m currently using the second SQL 
> statement, which is yielding the table lock.  I understand the need for 
> the table lock.  I also know which property needs to modified to 
> increase the escalation threshold.  I’m just wondering if the first SQL 
> statement will not lock the table or even the records that are brought 
> back in the sub-query result set. I’m hoping the delete statement will 
> just put a lock on the individual records as the sub query result set is 
> iterated through.
> 
>  
> 
> Thanks.
> 
>  
> 


Mime
View raw message