Hi:
Try:
Max storage for lock list (4KB) (LOCKLIST) = 1000
Interval for checking deadlock (ms) (DLCHKTIME) = 5000
Percent. of lock lists per appl. (%) (MAXLOCKS) = 16
Lock timeout (sec) (LOCKTIMEOUT) = 10
This formula can help you relate all the lock
parameters & no. of applications that can be supported
with the list of parameters at threshold.
maxlocks = 100 *
(n locks per application
* 32 bytes per lock
* n times the average ) / (locklist *
4096 bytes)
Also do a REBIND PACKAGE & applications to update
access plans.
Then run snapshot monitor on tables and locks to see
how it performs. After a few iterations of changing
the above set of parameters you should be able to get
rid of waits, escalations and deadlocks.
Increase LOCKLIST if you have waits. Increase MAXLOCKS
if you see escalations (Also will need to increase
LOCKLIST accordingly). Try keeping the Timeout small
if you have good amount of memory (4 Kb Pages)
allocated for LOCKLIST. Aviod LOCKTIMEOUTS by
increasing your LOCKLIST.
Also, MAXLOCKS indicates the soft-limit for the % of
LOCKLIST. Once this limit is reached, the application
will be unable to obtain locks resulting in lock
ESCALATION. With respect to the above suggested
values, when 16% of 1000 4KB pages is reached,
ESCALATION will occur. These values might not be a
perfect fit, so you many change them to suit your
system.
Tks!
Vijay
--- Steve Levy <[EMAIL PROTECTED]> wrote:
> With a MAXLOCKS parameter of 100 only 100 rows may
> be locked at one time.
> You cursors (selects statements) are hitting this
> wall then escalating to
> table locks on your "3 main tables." First LOCKLIST
> is a %-age so 100 is
> not a great choice unless you really WANT one use to
> be able to "use up" all
> of your databases locks (only for REAL heavy
> batch-based apps would this be
> the case). If you increase your MAXLOCKS you will
> find you are able to
> support more users. Try MAXLOCKS 600 and LOCKLIST
> 20. This will give each user 20% of 600 possible
> locks or everyone will get
> about 100 rows. If these numbers seem too high
> decrease as you need to.
> Good Luck
>
> ----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf
> Of KG
> Sivaramakrishnan
> Sent: Wednesday, July 18, 2001 7:17 AM
> To: [EMAIL PROTECTED]
> Subject: DB2EUG: DB2 lock timeout / deadlock problem
>
>
>
> Hi all,
>
> We have a serious problem( deladlock / lock timeout
> error) in our
> application when 6+ users are connected to the
> database and working on
> different modules. We have 3 main tables which
> are accessed accross all
> modules and the same gets read/updated/inserted
> using cursors / normal SQL
> statements. We have checked the commit points in
> the code and the it seems
> to be fine. We also tried with changing the
> isolation level from RR to
> CS/CR and increase the locklist size. Please find
> the following details
> about H/W, Software and the DB parameters.
>
> H/W P3 with 128 MB RAM
> Software UDB 5.2 on NT with FP 2, MF Cobol
> Database Parameters:
> Isolation level CS
> maxappls 60
> avg_appls 1
> maxfilop 64
> dlchktime 10000 milliseconds
> Locktimeout 180 Seconds
> MaxLocks 100
> Locklist 100
>
> All other DB parameters and DBM parameters, we have
> retained the default
> value. Now 50+ users wanted to work on this
> application. Kindly suggest
> some solution to overcome this problem since it is
> very urgent.
>
> Please find the following snapshot taken during the
> problem.
>
>
>
> Database Lock Snapshot
>
> Database name = CAWR1
> Database path =
> E:\DB2\NODE0000\SQL00006\
> Input database alias = CAWR1
> Locks held = 142
> Applications currently connected = 10
> Agents currently waiting on locks = 4
> Snapshot timestamp =
> 07-12-2001 12:32:33.351065
> Application handle = 45
> Application ID =
> 0AAD72B5.1706.010712064120
> Sequence number = 0001
> Application name =
> CA61.EXE
> Authorization ID = USERID
> Application status =
> Lock-wait
> Status change time =
> 07-12-2001 12:32:33.246818
> Application code page = 1252
> Locks held = 43
> Total wait time (ms) = 0
>
>
>
> Subsection waiting for lock = 0
> ID of agent holding lock = 56
> Application ID holding lock =
> 0AAD727D.2305.010712065417
> Lock object type = Row
> Lock mode =
> Exclusive Lock (X)
> Name of tablespace holding lock = TSTEMP
> Schema of table holding lock = CP
> Name of table holding lock = COLL
> Lock wait start timestamp =
> 07-12-2001 12:32:33.246823
> Lock is a result of escalation = NO
>
>
> Object Name Object Type Tablespace Name
> Table Schema Table
> Name Mode Status Lock Escalation
> ----------- --------------- --------------------
> -------------------- ------
> -------------- ---- ---------- ---------------
>
> 1570050 Row TSCAMS CP
> COLL
> X Granted NO
> 973825 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 1569819 Row TSCAMS CP
> COLL
> X Granted NO
> 973058 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 1569817 Row TSCAMS CP
> COLL
> X Granted NO
> 973056 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 1569814 Row TSCAMS CP
> COLL
> X Granted NO
> 1569809 Row TSCAMS CP
> COLL
> X Granted NO
> 972800 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 1569807 Row TSCAMS CP
> COLL
> X Granted NO
> 972546 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 1569813 Row TSCAMS CP
> COLL
> X Granted NO
> 1569804 Row TSCAMS CP
> COLL
> X Granted NO
> 972291 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 1569812 Row TSCAMS CP
> COLL
> X Granted NO
> 1569801 Row TSCAMS CP
> COLL
> X Granted NO
> 972288 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 1569811 Row TSCAMS CP
> COLL
> X Granted NO
> 1569796 Row TSCAMS CP
> COLL
> X Granted NO
> 971779 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 1569810 Row TSCAMS CP
> COLL
> X Granted NO
> 1569792 Row TSCAMS CP
> COLL
> X Granted NO
> 971523 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 972801 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 1569583 Row TSCAMS CP
> COLL
> X Granted NO
> 971520 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 1569576 Row TSCAMS CP
> COLL
> X Granted NO
> 971009 Row TSCAMS CP
> DEP_DESCRIPTION X Granted NO
> 260 Table TSCAMS CP
> DEP_DESCRIPTION IX Granted NO
> 263 Table TSCAMS CP
> COLL
> IX Granted NO
> 2126082 Row TSCAMS CP
> FOL_DESCRIPTION X Granted NO
>
=== message truncated ===
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
