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
