473,390 Members | 865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

Lock mode converting from NS to X, when will this happen?

DB2 V8.2 on AIX, type II index is created.
I see this from deadlock event monitor.

5) Deadlocked Connection ...
Participant no.: 2
Lock wait start time: 09/18/2006 23:04:09.911774
......
Deadlocked Statement:
Type : Dynamic
Operation: Execute
Section : 384
Creator : NULLID
Package : SYSLH202
Cursor : SQL_CURLH202C384
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
List of Locks:

Lock Name : 0x00060003052179080000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000003
Lock Count : 1
Hold Count : 0
Lock Object Name : 86079752
Object Type : Row
Tablespace Name : USER1
Table Schema : DB2ADMIN
Table Name : TEST
Mode : X - Exclusive
Status : Converting
Current Mode : NS - Share (and Next Key Share)

7) Deadlocked Connection ...
Participant no.: 1
Lock wait start time: 09/10/2006 23:04:09.914971
......
Deadlocked Statement:
Type : Dynamic
Operation: Execute
Section : 384
Creator : NULLID
Package : SYSLH202
Cursor : SQL_CURLH202C384
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
......
Lock Name : 0x000600030568B7070000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000003
Lock Count : 1
Hold Count : 0
Lock Object Name : 90748679
Object Type : Row
Tablespace Name : S04K_EMP
Table Schema : EZK100C
Table Name : EMPLOYEE_CONTRACT
Mode : X - Exclusive
Status : Converting
Current Mode : NS - Share (and Next Key Share)
......
Could any guru tell me what the database is trying to do?
Looks like 2 application is trying to update the same records.

Sep 20 '06 #1
2 5832
Deadlocks occur when two applications are unable to complete because
each is holding a lock that prevents the other from continuing it's own
updates. Your deadlock report doesn't show all of the locks - it only
shows the ones that each application is waiting on. Note that the
participants are waiting on different tables.

This problem can be avoided by having all applications that update a set
of n tables always perform the updates on the tables in the same order.

The conversion to X lock should occur when that application is ready to
do the update. UPDATE statements use the NS locks during the retrieval
portion of the update and convert to X at update time. This minimizes
the X locks when an UPDATE needs to scan many rows to satisfy the
predicates which limit the updates to a few rows.

Phil Sherman
sh*******@gmail.com wrote:
DB2 V8.2 on AIX, type II index is created.
I see this from deadlock event monitor.

5) Deadlocked Connection ...
Participant no.: 2
Lock wait start time: 09/18/2006 23:04:09.911774
.....
Deadlocked Statement:
Type : Dynamic
Operation: Execute
Section : 384
Creator : NULLID
Package : SYSLH202
Cursor : SQL_CURLH202C384
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
List of Locks:

Lock Name : 0x00060003052179080000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000003
Lock Count : 1
Hold Count : 0
Lock Object Name : 86079752
Object Type : Row
Tablespace Name : USER1
Table Schema : DB2ADMIN
Table Name : TEST
Mode : X - Exclusive
Status : Converting
Current Mode : NS - Share (and Next Key Share)

7) Deadlocked Connection ...
Participant no.: 1
Lock wait start time: 09/10/2006 23:04:09.914971
.....
Deadlocked Statement:
Type : Dynamic
Operation: Execute
Section : 384
Creator : NULLID
Package : SYSLH202
Cursor : SQL_CURLH202C384
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
.....
Lock Name : 0x000600030568B7070000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000003
Lock Count : 1
Hold Count : 0
Lock Object Name : 90748679
Object Type : Row
Tablespace Name : S04K_EMP
Table Schema : EZK100C
Table Name : EMPLOYEE_CONTRACT
Mode : X - Exclusive
Status : Converting
Current Mode : NS - Share (and Next Key Share)
.....
Could any guru tell me what the database is trying to do?
Looks like 2 application is trying to update the same records.
Sep 21 '06 #2
Sorry Phil, I forgot the update the second part, it is the same table
in the original rpt.
You can tell from the same lock object name.
Lock Object Name : 86079752
Could this be the senario?
On timestamp 1, application 1 try to update tableA - record 1 by
primary key.
On timestamp 2, application 1 get the NS lock on tableA - record 1;
On timestamp 3, application 2 try to update tableA - record 1 by
primary key.
On timestamp 4, application 2 get the NS lock on tableA - record 1;

On timestamp 12, application 1 is ready for update, need to converting
the NS lock on tableA - record 1 to X lock; But there is application 2
holding the NS lock on it, so application 1 has to wait.
On timestamp 14, application 2 is ready for update, need to converting
the NS lock on tableA - record 1 to X lock; But there is application 1
holding the NS lock on it, so application 1 has to wait.

On timestamp 20, DB2 dead lock checker find the loop, and kick off 2.
If this is the senario, then I dont see any way to avoid the deadlock
on update the same object around same period of time.

Phil Sherman wrote:
Deadlocks occur when two applications are unable to complete because
each is holding a lock that prevents the other from continuing it's own
updates. Your deadlock report doesn't show all of the locks - it only
shows the ones that each application is waiting on. Note that the
participants are waiting on different tables.

This problem can be avoided by having all applications that update a set
of n tables always perform the updates on the tables in the same order.

The conversion to X lock should occur when that application is ready to
do the update. UPDATE statements use the NS locks during the retrieval
portion of the update and convert to X at update time. This minimizes
the X locks when an UPDATE needs to scan many rows to satisfy the
predicates which limit the updates to a few rows.

Phil Sherman
sh*******@gmail.com wrote:
DB2 V8.2 on AIX, type II index is created.
I see this from deadlock event monitor.

5) Deadlocked Connection ...
Participant no.: 2
Lock wait start time: 09/18/2006 23:04:09.911774
.....
Deadlocked Statement:
Type : Dynamic
Operation: Execute
Section : 384
Creator : NULLID
Package : SYSLH202
Cursor : SQL_CURLH202C384
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
List of Locks:

Lock Name : 0x00060003052179080000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000003
Lock Count : 1
Hold Count : 0
Lock Object Name : 86079752
Object Type : Row
Tablespace Name : USER1
Table Schema : DB2ADMIN
Table Name : TEST
Mode : X - Exclusive
Status : Converting
Current Mode : NS - Share (and Next Key Share)

7) Deadlocked Connection ...
Participant no.: 1
Lock wait start time: 09/10/2006 23:04:09.914971
.....
Deadlocked Statement:
Type : Dynamic
Operation: Execute
Section : 384
Creator : NULLID
Package : SYSLH202
Cursor : SQL_CURLH202C384
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
.....
Lock Name : 0x000600030568B7070000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000003
Lock Count : 1
Hold Count : 0
Lock Object Name : 90748679
Object Type : Row
Tablespace Name : USER1
Table Schema : DB2ADMIN
Table Name : TEST
Mode : X - Exclusive
Status : Converting
Current Mode : NS - Share (and Next Key Share)
.....
Could any guru tell me what the database is trying to do?
Looks like 2 application is trying to update the same records.
Sep 22 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Bob Jones | last post by:
I am using SQL server 2000 with Uniface (4GL). I am running a process which commits every 30 seconds or so on one machine and another user is trying to perform a small update at the same time on...
2
by: xixi | last post by:
i am using type 4 driver with fp3 on windows 64bit server, auto commit is true, cursor is CS type. i have a sensitive , updatable cursor open and bring back a one row resultset, i get this...
0
by: Bruce Pullen | last post by:
DB2 v7.2 (FP7 - DB2 v7.1.0.68) on AIX 5.2.0.0. We're seeing unexpected single row (then commit) insert locking behaviour. We're seeing Applications that already hold row-level W locks in...
5
by: harborboy76 | last post by:
Hi all, First of all, just want to let you know that I'm coming from Informix background, and we just recently migrated to DB2. It's been a great new learning experience. For the time being, I...
5
by: Uwe C. Schroeder | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, maybe my mind is stuck, but here's something strange. This is the classic "counter" thing, where you can't / won't use sequences....
3
by: Raj | last post by:
I created a refresh deferred MQT, and during full refresh there were 4 or 5 lock waits, all waiting on a 'S' lock on Internal Catalog Cache ? Can some one explain how to prevent this from happening?
4
by: Joey | last post by:
Hi, I wrote a mixed-mode dll (with MFC and C++/CLI) which is called from a C#-EXE. Under special cirumstances (that is: another process sends a windows-message to my process - this message is...
94
by: Samuel R. Neff | last post by:
When is it appropriate to use "volatile" keyword? The docs simply state: " The volatile modifier is usually used for a field that is accessed by multiple threads without using the lock...
25
by: zmickle | last post by:
Excuse my noobness. I am managing an access database that is shared by 4 users. Management does not want to use any technologies outside of access for this application (no SQL Server, etc). I...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.