Lock escalation on a Merge command

[shorti]

DB2 V9.5 on AIX. This routine has been in use for years but we just
hit the problem for the first time (we moved from V8.2 to V9.5 over a
year ago but this routine doesnt get used often so maybe we've just
been luck). However, I still cant find information on why we are
having a problem:

We have a routine that places records into a temp table, it then
merges the data from the temp table into one of our main tables
(update/insert depending on if it exists). The database is in an
active state at the time (doing reads/updates/inserts). During the
merge we hit some deadlocks. As I was reviewing the deadlock details
I saw the merge operation hit lock escalation and locked the main
table....causing deadlocks on incoming table transactions.

I reviewed the routine and I see we merge 2500 records at a time (this
is from a ksh script). Autocommit is set to ON...so why the table
lock? The deadlock details shows Lock Escalation=Y and 300k+ locks
held?

Here are the important parts of the merge command and a psuedocode
version of what the routine does:

while not all rows merged
get the next 2500 col1 start and end values (where you see the between
5000 and 7500 below)
db2 "merge into table1 as OUT using (select * from tmp_table where
col1 between '5000' and '7500') as INN on
(OUT.COL1=INN.COL1) when matched then update set
COL2=INN.COL2,......
when not matched then insert (COL1,COL2,....)
values (INN.COL1,INN.COL2......) "
log start and end values of col1.
when finished we exit the loop

Logging and some values:

Option Description Current Setting
------ ---------------------------------------- ---------------
-a Display SQLCA OFF
-c Auto-Commit ON

Percent. of lock lists per application (MAXLOCKS) = 76

245) Deadlocked Connection ...
Deadlock ID: 18
Participant no.: 1
Participant no. holding the lock: 2
Appl Id: *LOCAL.xxxx.100815083310
Lock Count : 1
Hold Count : 0
Current Mode : IX - Intent Exclusive
Requesting lock as part of escalation: TRUE

Application Snapshot

Application handle = 789
Application status = UOW Executing
Status change time = Not Collected
Application code page = 1208
Application country/region code = 1
DUOW correlation token = *LOCAL.xxxx.100815083310
Locks held by application = 304427
Lock waits since connect = 14
Time application waited on locks (ms) = Not Collected
Deadlocks detected = Not Collected
Lock escalations = 16
Exclusive lock escalations = 16
Number of Lock Timeouts since connected = 0
Number of SQL requests since last commit = 1
Commit statements = 26
Rollback statements = 0
Dynamic SQL statements attempted = 37
Static SQL statements attempted = 30
Failed statement operations = 0
Select SQL statements executed = 6
Xquery statements executed = 0
Update/Insert/Delete statements executed = 17
DDL statements executed = 2
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 80000
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 0
Rows inserted = 40000
Rows updated = 0
Rows selected = 6
Rows read = 16227556
Rows written = 14406442

Does anyone see anything that stands out or something else I can look
at? As always, I appreciate your help and input.

[stefan.albert]

It would be helpful to have more information about:
1) size of lock list
2) how many sessions work in parallel
3) the deadlock object

As a first guess: your setting for MAXLOCKS (76%) is much too high if
there are more than 1 sessions holding/requesting locks - we use 15%
You should size your lock list and maxlocks so that it can hold at
least the 2500 row locks using less than maxlocks percent pages.

[shorti]

Thanks Stefan, Here is some more information :

Max storage for lock list (4KB) (LOCKLIST) = 12288

If I understand your second question you are asking how many working
agents in the database? If so, we probably had about 250 connections
to the database at the time. Not all may be actually doing work at
one particular moment. some may be detaching and reattaching to
contexts, etc. This is a multi-threaded environment.

What type of information are you looking for on the object? do you
mean the lock information? I have several deadlocks so this is just
one:

Participant #1 on ID 1 (this is a select statement with a FOR READY
ONLY..not sure why it was even part of the deadlock but trying to
resolve one issue at a time...the thread may have done some update
prior to this that got them in trouble.):

Lock Count : 1
Hold Count : 0

Lock Object Name : 6
Object Type : Table
Tablespace Name : VIS_TS
Table Schema : ADMUSER
Table Name : TABLE1
Data partition id : 0
Mode : IS - Intent Share

Participant #2 on ID 1 (this is the merge operation) //this is the
lock that it complained about in the deadlock list:

Lock Count : 1
Hold Count : 0

Lock Object Name : 2216230914
Object Type : Row
Tablespace Name : VIS_TS
Table Schema : ADMUSER
Table Name : TABLE1
Data partition id : 0
Mode : X - Exclusive

This is another lock in the same list :

Lock Count : 1
Hold Count : 0

Lock Object Name : 6
Object Type : Table
Tablespace Name : VIS_TS
Table Schema : ADMUSER
Table Name : TABLE1
Data partition id : 0
Mode : X - Exclusive
Status : Converting

Current Mode : IX - Intent Exclusive

Lock Escalation : YES

I know the deadlock ID above was 18 but it shows the same information
with # of locks held and lock escalation.

[Serge Rielau]

Check the plan for the merge. If it's using a HASH JOIN then it would
over lock. An easy way to get around that is to lower the optimization
level to 3 (and thus disable hashjoin).

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab