By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,286 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,286 IT Pros & Developers. It's quick & easy.

db2 concurrency control question

P: n/a
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.

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"chessplayer" <su****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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.

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.
Nov 12 '05 #2

P: n/a

Mark A wrote:
"chessplayer" <su****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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.
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.


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

Nov 12 '05 #3

P: n/a
"chessplayer" <su****@gmail.com> wrote in message
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?
I don't understand the question, but even if I did, I am not sure I know the
answer.
-Can I use hints (like in Oracle) to force the optimizer to use certain
indexes?
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.
-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)?
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
Thanks once again


Did you try my suggestion? Did it resolve the concurrency problem?
Nov 12 '05 #4

P: n/a
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:
"chessplayer" <su****@gmail.com> wrote in message
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?
I don't understand the question, but even if I did, I am not sure I

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

-Can I use hints (like in Oracle) to force the optimizer to use certain indexes?
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.
-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)?
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
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.
Thanks once again


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


Yes your suggestion worked! Thanks.

Nov 12 '05 #5

P: n/a
"chessplayer" <su****@gmail.com> wrote in message
I meant why is it that in the lock trace, db2 showed row locks when it
was really locking on the tablespace level?

I am not sure. I would have to see a snapshot for locks. Maybe someone else
can tell you..

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.


Many people ask questions here (with several people from IBM responding) and
on www.dbforums.com
Yes your suggestion worked! Thanks.


You are welcome.
Nov 12 '05 #6

P: n/a
> -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)?


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

Nov 12 '05 #7

P: n/a
Thanks Dairo

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.