Tim writes:
>What type of index locking, if any, needs to be considered when SQL is
executed
>against tables that have indexes defined on them?
Although I'm certain that many people will answer your question, I haven't
seen any yet, so I'll submit my attempt at an answer.
Every table should have at least one index, if only the unique index
presented by creating a primary key. Any INSERT or DELETE could lock an
unrelated row in the same table simply because the index key for that row
follows the index key of the changed row. UPDATE statements that affect
indexed columns could also introduce next key locks. Any DML can lock
seemingly unrelated data depending on the changes it has to make to the
underlying index structure (tree). The impact of these index-related locks
is lessened in DB2 V7 OS/390 as well as DB2 V8 for all platforms, due to
Type 2 indexes or block indexes. However, even if you are using DB2 V8, it
is still wise to minimize the amount of time rows are locked by deferring
any row manipulation as far from the beginning of the transaction and as
close to the end as possible.
The DB2 Administration Guide has some detailed information about the
different types of locks and their behavior under the Application
Considerations section (Application Guide chapter 22 for DB2 UDB v7).
Hope this helps,
Fred Sobotka
Database Administrator
IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for
Unix, Windows, & OS/2
CollegeNET, Inc.
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
