473,735 Members | 5,177 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_CURLH202C38 4
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
List of Locks:

Lock Name : 0x0006000305217 9080000000052
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_CURLH202C38 4
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
......
Lock Name : 0x000600030568B 7070000000052
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_CONTRA CT
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 5863
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_CURLH202C38 4
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
List of Locks:

Lock Name : 0x0006000305217 9080000000052
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_CURLH202C38 4
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
.....
Lock Name : 0x000600030568B 7070000000052
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_CONTRA CT
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_CURLH202C38 4
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
List of Locks:

Lock Name : 0x0006000305217 9080000000052
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_CURLH202C38 4
Cursor was blocking: FALSE
Text : update db2admin.test set ... where key = ?
.....
Lock Name : 0x000600030568B 7070000000052
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
2618
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 the one of the tables whose data is modified by the first process. I accept that the two second process may get blocked at times by the first but expect that to only happen rarely due to the likelihood of both processes trying to update the...
2
4469
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 locklist Lock Name = 0x020027011C0000000000000052 Lock Attributes = 0x00000000 Release Flags = 0x00000002 Lock Count = 1
0
3383
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 lock-wait, waiting to acquire row-level X locks. The lock-waits are behind applications that have row-level X locks on different rows (honestly). Both executing and lock-waiting applications have been granted IX table locks.
5
11101
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 have some questions regarding LOCK TIMEOUT and its compatibility with LOCK MODE WAIT in INFOMIX. Last week, I just found out that the following statement was complied and built in Stored Procedure SET LOCK MODE TO WAIT 5;
5
2338
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. Basically I need to assemble an identifier like
3
6662
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
5044
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 processed by the native part and calls managed code in turn) a loader lock appears. I have tracked down the call stack - the interessting part: - mscorwks.dll (where the exception is raised)
94
30333
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 Statement (C# Reference) statement to serialize access. " But when is it better to use "volatile" instead of "lock" ?
25
14846
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 have split the database into front end and back end databases and put the front end on each of the users' computers. All users can open the front end simultaneously, but once somebody updates data in the form, the back end locks up. What I...
0
8962
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9463
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9200
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8201
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6747
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6049
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4559
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2739
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2188
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.