Link to home
Start Free TrialLog in
Avatar of jmarrara
jmarrara

asked on

DB2 Table Lock

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.
Avatar of ghp7000
ghp7000

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?

Avatar of jmarrara

ASKER

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)
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.
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
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
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 ?
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
We changed our application to select for update before update but we still have the same problem. Do you have any other suggestion ?
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
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.
I believe this is the same problem I am experiencing here:
https://www.experts-exchange.com/questions/20762708/Exclusive-table-locks.html
set your isloation level in db2cli.ini to txnisolation=4

db2 update cli cfg for <database name> using txnisolation 4
ASKER CERTIFIED SOLUTION
Avatar of jmarrara
jmarrara

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial