Greetings Folks,

I am trying to understand how DB2 locks data and can't believe my eyes.
Have read the literature about the difference isolation levels and have
a general understanding about intent, update, share and exclusive
locks.

I am working on the sample database tables and using the default
isolation level of CS.

Situation:
-Session1 does a simple update on the Org table.
CLI Session 1 (autocommit off): UPDATE org SET location = 'Montreal'
WHERE deptnumb = 10

-the db2pd lock trace:
Locks:
Address TranHdl Lockname Type Mode Sts
Owner Dur HldCnt Att Rlse
0x020CA318 2 53514c4332453036bd4a32c841 Internal P ..S G 2
1 0 0 0x0
0x020CA598 2 02001100000000000000000054 Table .IX G 2
1 0 0 0x0
0x020CA458 2 02001100040000000000000052 Row ..X G 2
1 0 0 0x0

As we can see, a Intent Exclusive table lock on table org and an
exclusive row lock (on deptnumb 10) is acquired. Everything is fine and
dandy so far.

-Session2 tries to so a simple update on a different row of table Org.
CLI Session 2 (autocommit off): UPDATE org SET location = 'Toronto'
WHERE deptnumb = 15
BOOM! Session is blocked!

Looking at the lock trace again:

Address TranHdl Lockname Type Mode Sts
Owner Dur HldCnt Att Rlse
0x020CA318 2 53514c4332453036bd4a32c841 Internal P ..S G 2
1 0 0 0x0
0x020CA390 3 53514c4332453036bd4a32c841 Internal P ..S G 3
1 0 0 0x0
0x020CA598 2 02001100000000000000000054 Table .IX G 2
1 0 0 0x0
0x020CA840 3 02001100000000000000000054 Table .IX G 3
1 0 0 0x0
0x020CA458 2 02001100040000000000000052 Row ..X G 2
1 0 0 0x0
0x020CA5C0 3 02001100040000000000000052 Row ..U W 0
1 0 0 0x0
0x020CAAC0 3 01000000010000000100140056 Internal V ..S G 3
1 0 0 0x0

We can see from the lock trace that an update lock is in waiting status
and there is another table IX lock showing up.

Question: If I am updating a another row in session 2 why does session2
still get blocked?

On page 64 (table) of Administration Guide: Performance manual, I quote
the explanation for IX table locks: "The lock owner and concurrent
applications can read and update data. Other concurrent applications
can both read and update the table.

So clearly the first session's IX table lock is not going to block the
2nd session.

What could be the reason? Thank you for your time.

"chessplayer" <suchak@gmail.com> wrote in message
news:1115431077.409577.195060@f14g2000cwb.googlegr oups.com...[color=blue]
> Greetings Folks,
>
> I am trying to understand how DB2 locks data and can't believe my eyes.
> Have read the literature about the difference isolation levels and have
> a general understanding about intent, update, share and exclusive
> locks.
>
> I am working on the sample database tables and using the default
> isolation level of CS.
>
> Situation:
> -Session1 does a simple update on the Org table.
> CLI Session 1 (autocommit off): UPDATE org SET location = 'Montreal'
> WHERE deptnumb = 10
>
> -the db2pd lock trace:
> Locks:
> Address TranHdl Lockname Type Mode Sts
> Owner Dur HldCnt Att Rlse
> 0x020CA318 2 53514c4332453036bd4a32c841 Internal P ..S G 2
> 1 0 0 0x0
> 0x020CA598 2 02001100000000000000000054 Table .IX G 2
> 1 0 0 0x0
> 0x020CA458 2 02001100040000000000000052 Row ..X G 2
> 1 0 0 0x0
>
> As we can see, a Intent Exclusive table lock on table org and an
> exclusive row lock (on deptnumb 10) is acquired. Everything is fine and
> dandy so far.
>
> -Session2 tries to so a simple update on a different row of table Org.
> CLI Session 2 (autocommit off): UPDATE org SET location = 'Toronto'
> WHERE deptnumb = 15
> BOOM! Session is blocked!
>
> Looking at the lock trace again:
>
> Address TranHdl Lockname Type Mode Sts
> Owner Dur HldCnt Att Rlse
> 0x020CA318 2 53514c4332453036bd4a32c841 Internal P ..S G 2
> 1 0 0 0x0
> 0x020CA390 3 53514c4332453036bd4a32c841 Internal P ..S G 3
> 1 0 0 0x0
> 0x020CA598 2 02001100000000000000000054 Table .IX G 2
> 1 0 0 0x0
> 0x020CA840 3 02001100000000000000000054 Table .IX G 3
> 1 0 0 0x0
> 0x020CA458 2 02001100040000000000000052 Row ..X G 2
> 1 0 0 0x0
> 0x020CA5C0 3 02001100040000000000000052 Row ..U W 0
> 1 0 0 0x0
> 0x020CAAC0 3 01000000010000000100140056 Internal V ..S G 3
> 1 0 0 0x0
>
> We can see from the lock trace that an update lock is in waiting status
> and there is another table IX lock showing up.
>
> Question: If I am updating a another row in session 2 why does session2
> still get blocked?
>
> On page 64 (table) of Administration Guide: Performance manual, I quote
> the explanation for IX table locks: "The lock owner and concurrent
> applications can read and update data. Other concurrent applications
> can both read and update the table.
>
> So clearly the first session's IX table lock is not going to block the
> 2nd session.
>
> What could be the reason? Thank you for your time.
>[/color]
I have seen that behavior with some client interfaces. If DB2 does a
tablespace scan in session 1 to determine the qualifying rows, it causes all
the rows read to be locked (not just the qualifying rows). If the qualifying
row is determined via an index (using stage 1 predicate), then the problem
disappears.

So in this example, if there is an index on deptnumb, then DB2 will not need
to do a tablespace scan, and only the updated row(s) will be locked,
allowing session 2 to update its row while session 1 hold locks on other
rows.

However, for the sample database, even if an index was created on deptnumb,
the table is so small (few rows) that DB2 would not use the index and would
do a tablespace scan anyway. This can be resolved by setting the table to
"volatile" (see alter table) and the index usage will be "strongly
encouraged" and the contention problem you described should go away.

Obviously, you should make sure to perform runstats on the table and indexes
to provide DB2 accurate information for selecting the optimum access path,
and then, if the table is large enough, the volatile is not needed.

The above comments apply to version 8.1 FP4a and I don't know if anything
has changed in a later 8.1 fixpak or 8.2.

Mark A wrote:[color=blue]
> "chessplayer" <suchak@gmail.com> wrote in message
> news:1115431077.409577.195060@f14g2000cwb.googlegr oups.com...[color=green]
> > Greetings Folks,
> >
> > I am trying to understand how DB2 locks data and can't believe my[/color][/color]
eyes.[color=blue][color=green]
> > Have read the literature about the difference isolation levels and[/color][/color]
have[color=blue][color=green]
> > a general understanding about intent, update, share and exclusive
> > locks.
> >
> > I am working on the sample database tables and using the default
> > isolation level of CS.
> >
> > Situation:
> > -Session1 does a simple update on the Org table.
> > CLI Session 1 (autocommit off): UPDATE org SET location =[/color][/color]
'Montreal'[color=blue][color=green]
> > WHERE deptnumb = 10
> >
> > -the db2pd lock trace:
> > Locks:
> > Address TranHdl Lockname Type Mode[/color][/color]
Sts[color=blue][color=green]
> > Owner Dur HldCnt Att Rlse
> > 0x020CA318 2 53514c4332453036bd4a32c841 Internal P ..S G[/color][/color]
2[color=blue][color=green]
> > 1 0 0 0x0
> > 0x020CA598 2 02001100000000000000000054 Table .IX G[/color][/color]
2[color=blue][color=green]
> > 1 0 0 0x0
> > 0x020CA458 2 02001100040000000000000052 Row ..X G[/color][/color]
2[color=blue][color=green]
> > 1 0 0 0x0
> >
> > As we can see, a Intent Exclusive table lock on table org and an
> > exclusive row lock (on deptnumb 10) is acquired. Everything is fine[/color][/color]
and[color=blue][color=green]
> > dandy so far.
> >
> > -Session2 tries to so a simple update on a different row of table[/color][/color]
Org.[color=blue][color=green]
> > CLI Session 2 (autocommit off): UPDATE org SET location = 'Toronto'
> > WHERE deptnumb = 15
> > BOOM! Session is blocked!
> >
> > Looking at the lock trace again:
> >
> > Address TranHdl Lockname Type Mode[/color][/color]
Sts[color=blue][color=green]
> > Owner Dur HldCnt Att Rlse
> > 0x020CA318 2 53514c4332453036bd4a32c841 Internal P ..S G[/color][/color]
2[color=blue][color=green]
> > 1 0 0 0x0
> > 0x020CA390 3 53514c4332453036bd4a32c841 Internal P ..S G[/color][/color]
3[color=blue][color=green]
> > 1 0 0 0x0
> > 0x020CA598 2 02001100000000000000000054 Table .IX G[/color][/color]
2[color=blue][color=green]
> > 1 0 0 0x0
> > 0x020CA840 3 02001100000000000000000054 Table .IX G[/color][/color]
3[color=blue][color=green]
> > 1 0 0 0x0
> > 0x020CA458 2 02001100040000000000000052 Row ..X G[/color][/color]
2[color=blue][color=green]
> > 1 0 0 0x0
> > 0x020CA5C0 3 02001100040000000000000052 Row ..U W[/color][/color]
0[color=blue][color=green]
> > 1 0 0 0x0
> > 0x020CAAC0 3 01000000010000000100140056 Internal V ..S G[/color][/color]
3[color=blue][color=green]
> > 1 0 0 0x0
> >
> > We can see from the lock trace that an update lock is in waiting[/color][/color]
status[color=blue][color=green]
> > and there is another table IX lock showing up.
> >
> > Question: If I am updating a another row in session 2 why does[/color][/color]
session2[color=blue][color=green]
> > still get blocked?
> >
> > On page 64 (table) of Administration Guide: Performance manual, I[/color][/color]
quote[color=blue][color=green]
> > the explanation for IX table locks: "The lock owner and concurrent
> > applications can read and update data. Other concurrent[/color][/color]
applications[color=blue][color=green]
> > can both read and update the table.
> >
> > So clearly the first session's IX table lock is not going to block[/color][/color]
the[color=blue][color=green]
> > 2nd session.
> >
> > What could be the reason? Thank you for your time.
> >[/color]
> I have seen that behavior with some client interfaces. If DB2 does a[/color]
[color=blue]
> tablespace scan in session 1 to determine the qualifying rows, it[/color]
causes all[color=blue]
> the rows read to be locked (not just the qualifying rows). If the[/color]
qualifying[color=blue]
> row is determined via an index (using stage 1 predicate), then the[/color]
problem[color=blue]
> disappears.
>
> So in this example, if there is an index on deptnumb, then DB2 will[/color]
not need[color=blue]
> to do a tablespace scan, and only the updated row(s) will be locked,
> allowing session 2 to update its row while session 1 hold locks on[/color]
other[color=blue]
> rows.
>
> However, for the sample database, even if an index was created on[/color]
deptnumb,[color=blue]
> the table is so small (few rows) that DB2 would not use the index and[/color]
would[color=blue]
> do a tablespace scan anyway. This can be resolved by setting the[/color]
table to[color=blue]
> "volatile" (see alter table) and the index usage will be "strongly
> encouraged" and the contention problem you described should go away.
>
> Obviously, you should make sure to perform runstats on the table and[/color]
indexes[color=blue]
> to provide DB2 accurate information for selecting the optimum access[/color]
path,[color=blue]
> and then, if the table is large enough, the volatile is not needed.
>
> The above comments apply to version 8.1 FP4a and I don't know if[/color]
anything[color=blue]
> has changed in a later 8.1 fixpak or 8.2.[/color]



Hi Mark,

Thank you very much for your detailed and quick reply. I am very
familiar with Oracle so my followup questions are based on that:

-If the tablespace was locked any ideas why the lock trace would show
row (X) and table (IX) lock?

-Can I use hints (like in Oracle) to force the optimizer to use certain
indexes?

-Is there a definative site for DB2 where clear examples/scripts are
given to show the concepts like there is for Oracle
(asktom.oracle.com)?

Thanks once again

"chessplayer" <suchak@gmail.com> wrote in message[color=blue]
> Hi Mark,
>
> Thank you very much for your detailed and quick reply. I am very
> familiar with Oracle so my followup questions are based on that:
>
> -If the tablespace was locked any ideas why the lock trace would show
> row (X) and table (IX) lock?
>[/color]
I don't understand the question, but even if I did, I am not sure I know the
answer.
[color=blue]
> -Can I use hints (like in Oracle) to force the optimizer to use certain
> indexes?
>[/color]
No, not specific indexes. Setting a table to volatile will encourage the
"best" index to be used. The DB2 optimizer does a very good job of choosing
the best access path, a bit better than most other databases, if you perform
runstats with the right options.
[color=blue]
> -Is there a definative site for DB2 where clear examples/scripts are
> given to show the concepts like there is for Oracle
> (asktom.oracle.com)?
>[/color]
I don't know what mean by "definitive site." There are other DB2 forums such
as www.dbforums.com (which has sections for all databases). Also
www.db2click.com
[color=blue]
> Thanks once again
>[/color]

Did you try my suggestion? Did it resolve the concurrency problem?

Mark,

Yes what you said worked like a charm. Creating a unique index, running
runstats and altering table to volatile helped.

Some clarifications below. Thanks again.


Mark A wrote:[color=blue]
> "chessplayer" <suchak@gmail.com> wrote in message[color=green]
> > Hi Mark,
> >
> > Thank you very much for your detailed and quick reply. I am very
> > familiar with Oracle so my followup questions are based on that:
> >
> > -If the tablespace was locked any ideas why the lock trace would[/color][/color]
show[color=blue][color=green]
> > row (X) and table (IX) lock?
> >[/color]
> I don't understand the question, but even if I did, I am not sure I[/color]
know the[color=blue]
> answer.[/color]

I meant why is it that in the lock trace, db2 showed row locks when it
was really locking on the tablespace level?

[color=blue]
>[color=green]
> > -Can I use hints (like in Oracle) to force the optimizer to use[/color][/color]
certain[color=blue][color=green]
> > indexes?
> >[/color]
> No, not specific indexes. Setting a table to volatile will encourage[/color]
the[color=blue]
> "best" index to be used. The DB2 optimizer does a very good job of[/color]
choosing[color=blue]
> the best access path, a bit better than most other databases, if you[/color]
perform[color=blue]
> runstats with the right options.
>[color=green]
> > -Is there a definative site for DB2 where clear examples/scripts[/color][/color]
are[color=blue][color=green]
> > given to show the concepts like there is for Oracle
> > (asktom.oracle.com)?
> >[/color]
> I don't know what mean by "definitive site." There are other DB2[/color]
forums such[color=blue]
> as www.dbforums.com (which has sections for all databases). Also
> www.db2click.com[/color]

What I meant by definitive site was a really great site with a wealth
of info and lots of examples. I have seen the very useful DB2 cookbooks
on:

http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM

but was wondering what DB2 professionals use to ask questions.[color=blue]
>[color=green]
> > Thanks once again
> >[/color]
>
> Did you try my suggestion? Did it resolve the concurrency problem?[/color]

Yes your suggestion worked! Thanks.

"chessplayer" <suchak@gmail.com> wrote in message[color=blue]
> I meant why is it that in the lock trace, db2 showed row locks when it
> was really locking on the tablespace level?
>[/color]

I am not sure. I would have to see a snapshot for locks. Maybe someone else
can tell you..
[color=blue][color=green]
>>[/color]
> What I meant by definitive site was a really great site with a wealth
> of info and lots of examples. I have seen the very useful DB2 cookbooks
> on:
>
> http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM
>
> but was wondering what DB2 professionals use to ask questions.[color=green]
>>[/color][/color]

Many people ask questions here (with several people from IBM responding) and
on www.dbforums.com
[color=blue]
> Yes your suggestion worked! Thanks.
>[/color]

You are welcome.

> -Is there a definative site for DB2 where clear examples/scripts are[color=blue]
> given to show the concepts like there is for Oracle
> (asktom.oracle.com)?[/color]

Support -> DB2
http://www-306.ibm.com/software/data/db2/udb/support/

DeveloperWorks -> DB2
http://www-130.ibm.com/developerworks/db2/

or articles like this:
http://www-128.ibm.com/developerwork...pta/index.html


Dario

Thanks Dairo