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

shenanwei

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

Subscribe Reply

5862

Phil Sherman

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

shenanwei

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

Similar topics

2615

Lock/block retained after commit

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...

Microsoft SQL Server

4469

IX lock not released

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

DB2 Database

3381

X Row locks causing Lock-waits

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.

DB2 Database

11100

SET CURRENT LOCK TIMEOUT

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;

DB2 Database

2338

Table Lock issue

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

PostgreSQL Database

6660

Internal Catalog Cache Lock

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?

DB2 Database

5040

"Load lock" in C++ mixed mode dll with MFC

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)

.NET Framework

30323

When is "volatile" used instead of "lock" ?

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" ?

C# / C Sharp

14843

Back end database lock-up

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...

Microsoft Access / VBA

8823

What is ONU?

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...

General

8718

Changing the language in Windows 10

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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9343

Problem With Comparison Operator <=> in G++

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...

C / C++

9104

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9047

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

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...

General

7973

AI Job Threat for Devs

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...

Career Advice

4477

Trying to create a lan-to-lan vpn between two differents networks

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...

Networking - Hardware / Configuration

3175

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2119

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

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...

General