Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DB2 Table Lock

Posted on 2004-09-01
16
Medium Priority
?
22,047 Views
Last Modified: 2011-08-18
I'm using DB2 version 7.1 running on United Linux 2.4.19, and I'm experiencing a lot of table locks instead of row locks. I'm using DB2 cli in C++ to access the database. Is there any configuration that I can use to avoid table locks ? That situation is very critical once it is generating a lot of dead locks in my system.
0
Comment
Question by:jmarrara
  • 7
  • 5
16 Comments
 
LVL 13

Expert Comment

by:ghp7000
ID: 11953455
what isolation level are you using?
what type of transactions are causing the deadlocks? delete? update? insert? select?
are they deadlocks or lock timeouts?
what is the value of the locklist, maxlocks, locktimeout and dlchktime parameters in db cfg?
how many applications connect to the database at peak times?

0
 

Author Comment

by:jmarrara
ID: 11954577
what isolation level are you using?
Don´t know for sure, I think is the default.

what type of transactions are causing the deadlocks? delete? update? insert? select?
Update

are they deadlocks or lock timeouts?
Deadlocks:
Deadlocks detected = 17
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock Timeouts = 0

what is the value of the locklist, maxlocks, locktimeout and dlchktime parameters in db cfg?
locklist = 1500
maxlocks = 20
locktimeout = 300
dlchktime = 90000

how many applications connect to the database at peak times?
around 30 (High water mark for connections = 31)
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 11957301
all your parameters look good, the default isolation level is CS, change the isolation level  to RS either for the database or for the sql statement, I suggest you read up on isolation levels before doing so however, it seems that your application is locking the table and not the row, you should get a snapshot for locks to better understand what is happening.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:jmarrara
ID: 11958975
We´ve got the snapshot but it doesn´t show which lock the applications is waiting for. There are four applications working concurrently (update) and all four has the same behaviour: each has a row exclusive lock and a table intended-exclusive lock, but seems like it´s normal to have an IX table lock when we have a X row lock.

Application handle                         = 683
Application ID                             = 0A650007.10D5.040901140639
Sequence number                            = 0001
Application name                           = goMessage
Authorization ID                           = DB2INST1
Application status                         = Lock-wait
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 4
Total wait time (ms)                       = Not Collected

List Of Locks
 Lock Object Name            = 3128068
 Node number lock is held at = 0
 Object Type                 = Row
 Tablespace Name             = USERSPACE1
 Table Schema                = DB2INST1
 Table Name                  = TB_MSG
 Mode                        = X
 Status                      = Granted
 Lock Escalation             = NO

 Lock Object Name            = 3
 Node number lock is held at = 0
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = DB2INST1
 Table Name                  = TB_MSG
 Mode                        = IX
 Status                      = Granted
 Lock Escalation             = NO

 Lock Object Name            = 0
 Node number lock is held at = 0
 Object Type                 = Internal V Lock
 Tablespace Name             =
 Table Schema                =
 Table Name                  =
 Mode                        = S
 Status                      = Granted
 Lock Escalation             = NO

 Lock Object Name            = 0
 Node number lock is held at = 0
 Object Type                 = Internal P Lock
 Tablespace Name             =
 Table Schema                =
 Table Name                  =
 Mode                        = S
 Status                      = Granted
 Lock Escalation             = NO
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 11960448
in the example you posted, the application is in lock wait status and you are getting x locks on row which is not compatible with other applications also requesting x lock, that is why you have deadlock. To avoid this, code your application to select the row for update, then update the row and commit the change. Selecting the row for update will produce U locks, which are compatible across applications
0
 

Author Comment

by:jmarrara
ID: 11963364
Just to clear a the scenario, those applications are updanting especific rows, the update command where clause is over the Table's Primary Key (e.g.  update ... where pk = x). I'm sure that the applications are not trying to update the same row. Do you belive that, in this case, selecting the row for update first and then update it will solve the problem ?
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 11963712
try it at command line prompt and see for yourself
window a: select .. from .. where col=pk for update of column
window b: select .. from .. where col=pk for update of column

note that if you are trying to lock the row for update which is positioned under the row which is locked for update by window a, you may also get lock timeout as you have encountered the problem of next key locking which is fixed in v8 with type 2 indexes
0
 

Author Comment

by:jmarrara
ID: 11968643
We changed our application to select for update before update but we still have the same problem. Do you have any other suggestion ?
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 11978904
is there a lock table statement in the application?
can u post the get snapshot for locks on <db name> when you experience lock wait?
you can look in the db2diag.log to see which applications are involved in the deadlock, can u post this information?
first :
db2 update dbm cfg using diaglevel 4
db2stop
db2start
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 11979039
just another thought, how many rows do you have in the table that is being updated? If all the rows are on the same page, then you are experiencing page locking which is unavoidable in db2, since the page where the row is located is exclusively locked until the unit of work completes.
0
 
LVL 1

Expert Comment

by:meehange
ID: 11987621
I believe this is the same problem I am experiencing here:
http://www.experts-exchange.com/Databases/IBM_UDB/Q_20762708.html
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 11992654
set your isloation level in db2cli.ini to txnisolation=4

db2 update cli cfg for <database name> using txnisolation 4
0
 

Accepted Solution

by:
jmarrara earned 0 total points
ID: 12301430
None of suggestions above had solved my problem, I had to implement some semaphores in my code to protect the application from DB2 table locks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses
Course of the Month11 days, 23 hours left to enroll

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question