Umm, have things changed? On UDB 6.1, I'm told that MAXLOCKS can be from 1 to
100 only.. Not 600. And LOCKLIST appears to be the Maximum storage for lock
list, in 4KB terms. -turgut
On Wednesday 18 July 2001 15:22, you 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
> 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
--
EgeNet Internet Services: http://www.egenet.com.tr
All of Turkey Online: http://find.egenet.com.tr
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
