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
943618 Row TSCAMS CP
FOL_DESCRIPTION X Granted NO
2126849 Row TSCAMS CP
FOL_DESCRIPTION X Granted NO
948737 Row TSCAMS CP
FOL_DESCRIPTION X Granted NO
947458 Row TSCAMS CP
FOL_DESCRIPTION X Granted NO
947200 Row TSCAMS CP
FOL_DESCRIPTION X Granted NO
946688 Row TSCAMS CP
FOL_DESCRIPTION X Granted NO
946432 Row TSCAMS CP
FOL_DESCRIPTION X Granted NO
946178 Row TSCAMS CP
FOL_DESCRIPTION X Granted NO
946177 Row TSCAMS CP
FOL_DESCRIPTION X Granted NO
945664 Row TSCAMS CP
FOL_DESCRIPTION X Granted NO
272 Table TSCAMS CP
FOL_DESCRIPTION IX Granted NO
0 Internal
S Granted NO
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------
Application handle = 56
Application ID = 0AAD727D.2305.010712065417
Sequence number = 0001
Application name = CA61.EXE
Authorization ID = USERID
Application status = Lock-wait
Status change time = 07-12-2001 12:32:33.228740
Application code page = 1252
Locks held = 54
Total wait time (ms) = 0
Subsection waiting for lock = 0
ID of agent holding lock = 45
Application ID holding lock = 0AAD72B5.1706.010712064120
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 = CUS_DESCRIPTION
Lock wait start timestamp = 07-12-2001 12:32:33.228745
Lock is a result of escalation = NO
Object Name Object Type Tablespace Name Table Schema Table
Name Mode Status Lock Escalation
----------- --------------- -------------------- -------------------- ------
-------------- ---- ---------- ---------------
832037 Row TSTEMP CP EXP_RS
NX Granted NO
832257 Row TSTEMP CP EXP_RS
X Granted NO
832256 Row TSTEMP CP EXP_RS
X Granted NO
832074 Row TSTEMP CP EXP_RS
X Granted NO
832073 Row TSTEMP CP EXP_RS
X Granted NO
832072 Row TSTEMP CP EXP_RS
X Granted NO
832071 Row TSTEMP CP EXP_RS
X Granted NO
832070 Row TSTEMP CP EXP_RS
X Granted NO
270 Table TSTEMP CP EXP_RS
IX Granted NO
1569826 Row TSTEMP CP COLL
X Granted NO
1569823 Row TSTEMP CP COLL
X Granted NO
1570048 Row TSTEMP CP COLL
X Granted NO
1568789 Row TSTEMP CP COLL
X Granted NO
966915 Row TSTEMP CP
DEP_DESCRIPTION X Granted NO
1569047 Row TSTEMP CP COLL
X Granted NO
967937 Row TSTEMP CP
DEP_DESCRIPTION X Granted NO
1569822 Row TSTEMP CP COLL
X Granted NO
1569280 Row TSTEMP CP COLL
X Granted NO
973314 Row TSTEMP CP
DEP_DESCRIPTION X Granted NO
1569053 Row TSTEMP CP COLL
X Granted NO
1568794 Row TSTEMP CP COLL
X Granted NO
967170 Row TSTEMP CP
DEP_DESCRIPTION X Granted NO
1569029 Row TSTEMP CP COLL
X Granted NO
1569828 Row TSTEMP CP COLL
X Granted NO
973571 Row TSTEMP CP
DEP_DESCRIPTION X Granted NO
1569056 Row TSTEMP CP COLL
X Granted NO
968195 Row TSTEMP CP
DEP_DESCRIPTION X Granted NO
1569028 Row TSTEMP CP COLL
X Granted NO
967426 Row TSTEMP CP
DEP_DESCRIPTION X Granted NO
1569052 Row TSTEMP CP COLL
X Granted NO
1568777 Row TSTEMP CP COLL
X Granted NO
968193 Row TSTEMP CP
DEP_DESCRIPTION X Granted NO
1569825 Row TSTEMP CP COLL
X Granted NO
1568776 Row TSTEMP CP COLL
X Granted NO
966657 Row TSTEMP CP
DEP_DESCRIPTION X Granted NO
973568 Row TSTEMP CP
DEP_DESCRIPTION X Granted NO
260 Table TSTEMP CP
DEP_DESCRIPTION IX Granted NO
263 Table TSTEMP CP COLL
IX Granted NO
942593 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
2127104 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
944898 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
950018 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
950017 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
944129 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
943362 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
943104 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
942337 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
2129665 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
2129154 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
2128640 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
2128129 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
2127617 Row TSTEMP CP
FOL_DESCRIPTION X Granted NO
272 Table TSTEMP CP
FOL_DESCRIPTION IX Granted NO
0 Internal
S Granted NO
Thanks in Advance
Regards
Siva
--
This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
