Hey all,

I have a java application which accessses a DB2 7.2 FP9 DB. One of the operations the application performs takes out hundreds of locks (often over 1000). Now most of these are row level locks, and it performs a lot of updates and inserts, so many of them are exclusive locks, the problem is that it takes out table level exclusive locks almost as soon as it starts executing, effectively locking all other users out of the system until it completes (which can take up to 5 mins, this is another issue that needs looking at).

The isolation level of the DB is CS, all the tables are set to row level locking.
There are no lock escalations and the locking memory is easily large enough o maintain the many locks it obtains.

Is there any suggestions you can make where I can go from here?
Do I need to look at the DB2 settings on the Webserver (The Java ap has a web front end, so all DB connections come from a websphere server)? If so, which settings?

Any help appreciated.

Cheers.

Dramtically increase locklist size, which will force escalation to table level when it fills up.

You should probably also increase maxlocks. Use something over 50 (%) if you really want to curtail lock escalation.

Marcus,

Thanks for the reply, there are no lock escalations taking place at all. The locklist and maxlocks are already larger then they strictly need to be.
The problem is that exclusinve table locks are taken almost immediately, then the row locks begin to mount up as the application goes through what I assume are loop iterations, but it's the instant table locks that are causing me the problems I think.

Which applications are taking the exclusive table locks? Are they utilities or regular applcations? I would take a snapshot for locks.

It's just the one Java App (a web based client served by websphere application server 4).
The application is performing a task where is adds/updates several records for a customer, it's one of the larger customer operations, but its still a very small operation in the scheme of things and its not acceptable to shut all other users out (as is effectively going to be the case if I can't figure out how to stop the IX locks)

You don't necessarily want to eliminate IX locks. And don't confuse "exlusive locks" with table locks (they are not the same).

Any time you "select for update", DB2 takes an IX lock (intent to escalate to exclusive on that row). This prevent others from taking an X lock or an IX lock, but does not prevent S (share) locks. The IX lock is needed to make sure that no one else can change the data between the time the "select for update" is issued and when the update is issued. Doing a commit after the update (or as soon as possible in any transaction) will release locks (in almost all cases).

The IX lock should occur at the row level if escalation to table level has not occured, which is a completely different issue from escalation from S to IX, IX to X, etc (a completely different kind of escalation). However, if you are using version 7, next key locking of indexes may be locking more rows than the one needed (this has been fixed in version 8 with type 2 indexes).

Unfortuneately the term escalation has two completely different meanings in DB2 and this causes as lot of confusion.

Marcus,

I probably haven't been completely clear. I get IX table level locks immediately when the application starts to perform this particular task.
Here is what I mean:

Lock Object Name = 258
Node number lock is held at = 0
Object Type = Row
Tablespace Name = PKGACM_TBSP
Table Schema = DBAUSR
Table Name = PKGACM
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 2
Node number lock is held at = 0
Object Type = Table
Tablespace Name = PKGACM_TBSP
Table Schema = DBAUSR
Table Name = PKGACM
Mode = IX
Status = Granted
Lock Escalation = NO

Lock Object Name = 2307
Node number lock is held at = 0
Object Type = Row
Tablespace Name = BENDTL_TBSP
Table Schema = DBAUSR
Table Name = BENDTL
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 2
Node number lock is held at = 0
Object Type = Table
Tablespace Name = BENDTL_TBSP
Table Schema = DBAUSR
Table Name = BENDTL
Mode = IX
Status = Granted
Lock Escalation = NO

When I look at the snapshot for database afterward, I see no deadlocks or escalations.

Does this application open a cursor (updateable)? If this requires a tablespace scan, it may be locking the entire table. Try looking at the Explain for the SQL statements in question.

I don't think it uses cursors. The developers say it doesn't (the guy who wrote the code is gone) so I'm not 100% I can take that claim at face value.
I can't get a copy of the SQL from the developers as they say they can't extract it.
I've tried to obtain it from the Dynamic SQL snap, but thats a bit of a nightmare.

Is there any other way to tell if cursors at involved?
Should I have setup anything on the web server, which is effectively the client machine (CLI settings or something)?

Sorry, I don't know of anything off-hand that would cause the table locks, other than what has already been discussed. But maybe someone else will come up with a suggestion.

Okie, cheers for the above in any case.

If you want to find out which SQL statements are executed, you can define an event monitor to catch them.