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.
