Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / December 2007

Tip: Looking for answers? Try searching our database.

Lock Escalation

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
dunleav1 - 10 Dec 2007 19:27 GMT
I have db2 9 installation and I think our application isn't handling
locking optimally.

I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The
value of these parameters in the parenthesis are the auto tuned values
after running my application for a while.
I notice values in db2diag.log that these values are being increased
to prevent lock escalation.

How do I determine the sql that is causing these high escalations?
Would it be better from a performance standpoint to specifically lock
the table in question instead?
On a machine with 4G of memory - is there a rule of thumb of about how
much memory should be allocated for locking?  - locklist and maxlocks?
If I set locklist and maxlocks to a lower value and monitor
db2diag.log will I get the offending sql that is causing the lock
escalation?

Thanks.
Mark A - 10 Dec 2007 22:48 GMT
>I have db2 9 installation and I think our application isn't handling
> locking optimally.
[quoted text clipped - 15 lines]
>
> Thanks.

That is a huge amount of locklist. Do you have a data warehouse application,
or an OLTP application (with lots of updates, inserts, and deletes)? If you
have almost all selects, you may NOT want to avoid lock escalation since
multiple share locks on table coexist fine, or maybe even use UR isolation
level.
dunleav1 - 11 Dec 2007 17:52 GMT
> >I have db2 9 installation and I think our application isn't handling
> > locking optimally.
[quoted text clipped - 21 lines]
> multiple share locks on table coexist fine, or maybe even use UR isolation
> level.

It's both. This application is a oltp application but it moves data
into a reporting schema.
Basically, I move transactions into a star schema based reporting
schema.

The isolation level would be whatever DB2 chooses when using the DB2
jdbc driver.
Mark A - 12 Dec 2007 00:51 GMT
"dunleav1" <jmdunleavy@comcast.net> wrote in message
news:853ff41e-5d23-4030-8d3e-
> It's both. This application is a oltp application but it moves data
> into a reporting schema.
[quoted text clipped - 3 lines]
> The isolation level would be whatever DB2 chooses when using the DB2
> jdbc driver.

On any select statement, you can alter the default isolation level using the
WITH UR (or CS, RR, RS) clause. Using with UR "might" be a good idea when
you are pulling data from the tables to load the datawarehouse, especially
if it is historical data and you know it is not being changed at the exact
moment you pull it..
Lennart - 11 Dec 2007 05:30 GMT
> I have db2 9 installation and I think our application isn't handling
> locking optimally.

What isolation level is your application using?

> I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The
> value of these parameters in the parenthesis are the auto tuned values
[quoted text clipped - 3 lines]
>
> How do I determine the sql that is causing these high escalations?

get snapshot for applications on <db>

and

get snapshot for locks on <db>

will give you a start

/Lennart
dunleav1 - 11 Dec 2007 17:57 GMT
> > I have db2 9 installation and I think our application isn't handling
> > locking optimally.
>
> What isolation level is your application using?

The isolation level would be whatever DB2 chooses when using the DB2
jdbc driver.

> > I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The
> > value of these parameters in the parenthesis are the auto tuned values
[quoted text clipped - 13 lines]
>
> /Lennart

I have a process that runs approximately 500 transactions for my test.
I'll have to watch the db2diag.log file for an alert than map it back
to db2diag.log file. That's kind of a bother. db2diag already knows
the table that it is escalating on, I should be able to log the
offending statement shouldn't I?
dunleav1 - 12 Dec 2007 17:00 GMT
> > > I have db2 9 installation and I think our application isn't handling
> > > locking optimally.
[quoted text clipped - 27 lines]
> the table that it is escalating on, I should be able to log the
> offending statement shouldn't I?

I figured out the offending statements by syncing a sql "trace" and
the db2diag.log file.
I have 7 insert statements that do a a join between the insert table
(T1) and a second table (T2) that look like this:
(ie) insert into T1 (val1,val2,val3) (select val1,val2,val3) from T2
where not exists (select 1 from T2 where T2.val1 = T1.val1) and val2 =
1 and val3 >10000000)

The lock escalation happens on the table being inserted into if I set
maxlocks and locklist to a permanant lower value. (ie) 50, 20000 for
example.
Now T1 may be selected or inserted into from different processes so I
want the default isolation level of RR from jdbc.

Will I see any significant performance gain by issuing a specific lock
table prior to the insert statement?
Or is it better to just let DB2 automatically escalate the sql to a
table lock?
dunleav1 - 12 Dec 2007 17:08 GMT
> > > > I have db2 9 installation and I think our application isn't handling
> > > > locking optimally.
[quoted text clipped - 46 lines]
> Or is it better to just let DB2 automatically escalate the sql to a
> table lock?

The 7 inserts take - 43 seconds, 35 seconds, 369 seconds, 8 seconds,
29 seconds, 29 seconds, 29 seconds.
Based on these numbers  I'm really wondering just how much of
performance hit a lock escalation really is. Because it doesn't look
like much to worry about.
dunleav1 - 11 Dec 2007 18:26 GMT
I'm pretty sure it's REPEATABLE_READ by default.
Mark A - 12 Dec 2007 00:53 GMT
> I'm pretty sure it's REPEATABLE_READ by default.

The DB2 default is CS by default. If you are using RR, try changing it to CS
or UR using the WITH XX clause.
dunleav1 - 12 Dec 2007 15:33 GMT
> > I'm pretty sure it's REPEATABLE_READ by default.
>
> The DB2 default is CS by default. If you are using RR, try changing it to CS
> or UR using the WITH XX clause.

CS is not one of the isolation levels listed in the JDBC standard?
Lennart - 12 Dec 2007 17:28 GMT
> > "dunleav1" <jmdunle...@comcast.net> wrote in message
>
[quoted text clipped - 6 lines]
>
> CS is not one of the isolation levels listed in the JDBC standard?

This must must be the one thing that causes most confusion between DB2
DBA's and java developers.
I think JDBC uses the names as defined in ISO

DB2                                    JDBC
Repeateble Read    ->         Serilizable
Read Stability          ->         Repeatable Read
Cursor Stability        ->         Read Committed
Uncommitted Read  ->         Read Uncommitted

/Lennart
dunleav1 - 12 Dec 2007 20:46 GMT
> > > "dunleav1" <jmdunle...@comcast.net> wrote in message
>
[quoted text clipped - 18 lines]
>
> /Lennart

The IBM DB2 universal jdbc driver returns "repeatable read (version:
SQL08024)" as the default isolation level when getTransactionIsolation
is called and the isolation level isn't specifically set.
Lennart - 12 Dec 2007 22:59 GMT
> > > > "dunleav1" <jmdunle...@comcast.net> wrote in message
>
[quoted text clipped - 22 lines]
> SQL08024)" as the default isolation level when getTransactionIsolation
> is called and the isolation level isn't specifically set.

My point was merely that "Repeateble Read" means different things for
different people. Anyhow, Repeatable Read (JDBC) keep locks longer
than Read Committed. What happens with the lock escalations if you
lower the isolation level to Read Committed?

If you have identified the query that causes the escalations, you can
try db2advis to see if there are indexes that might help.

/Lennart
dunleav1 - 13 Dec 2007 15:55 GMT
> > > > > "dunleav1" <jmdunle...@comcast.net> wrote in message
>
[quoted text clipped - 32 lines]
>
> /Lennart

Yes, the sql is already optimized. I used db2advis to confirm.
Knut Stolze - 14 Dec 2007 08:50 GMT
> The IBM DB2 universal jdbc driver returns "repeatable read (version:
> SQL08024)" as the default isolation level when getTransactionIsolation
> is called and the isolation level isn't specifically set.

I would say that this is probably another stupid default setting in JDBC.

The other two I know of are auto-commit being turned on and,
consequentially, all cursors being implicitly defined as holdable.  Any
serious JDBC application should turn this off right after it is started,
IMHO.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

dunleav1 - 17 Dec 2007 17:52 GMT
> > The IBM DB2 universal jdbc driver returns "repeatable read (version:
> > SQL08024)" as the default isolation level when getTransactionIsolation
[quoted text clipped - 11 lines]
> DB2 z/OS Utilities Development
> IBM Germany

Knut can you elaborate on what you think is stupid and why?
Knut Stolze - 17 Dec 2007 20:20 GMT
>> > The IBM DB2 universal jdbc driver returns "repeatable read (version:
>> > SQL08024)" as the default isolation level when getTransactionIsolation
[quoted text clipped - 8 lines]
>
> Knut can you elaborate on what you think is stupid and why?

Repeatable read isolation level allows less concurrency than committed read
(or uncommitted read). Thus, an isolation level is chosen per default which
has an adverse effect on performance.

Another, similar issue is auto-commit being the default for JDBC
connections. First, any transactional properties are discarded with this
right away.  You cannot rollback changes.  Likewise, due to the
auto-commit, it was necessary to open cursors per default as holdable
cursors.  Otherwise, the OPEN CURSOR (as JDBC method) would cause the
cursor to be closed at the end of the OPEN already.  So you couldn't use
the cursor.  Making all cursors holdable fits in with the auto-commit.  But
again, holdable cursors allocate some resources on the database server.  If
you don't need auto-commit, you can lift the resource requirements on the
server and get better performance.

Granted, those settings are good for novice programmers who just want to
their Hello World program to work.  But for all practical purposes, you
have to change those defaults, IMHO.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

dunleav1 - 19 Dec 2007 23:24 GMT
> >> > The IBM DB2 universal jdbc driver returns "repeatable read (version:
> >> > SQL08024)" as the default isolation level when getTransactionIsolation
[quoted text clipped - 32 lines]
> DB2 z/OS Utilities Development
> IBM Germany

Thanks Knut, I'll have to run some tests with our Java programmers.
Our application supports DB2, Oracle, Sql Server. DB2 has the worst
performance out of the three.
I'll start experimenting with the isolation level to get those number
comparable (or better).
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.