Solved

DB2 Table Lock

Posted on 2004-09-01
16
22,002 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now