473,320 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

db2 concurrency control question

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
7 4128
"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

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
"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
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
"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
> -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
Thanks Dairo

Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: xAvailx | last post by:
I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data...
3
by: Karl | last post by:
Hi .NET experts, I was a 2 tier database programmer using delphi. In Delphi, if user A is writting a record, this record is locked from user B for writting. If user A insert a new record, when...
8
by: Mike Kelly | last post by:
I've chosen to implement the "optimistic concurrency" model in my application. To assist in that, I've added a ROWVERSION (TIMESTAMP) column to my main tables. I read the value of the column in my...
4
by: Yoram Biberman | last post by:
I have a few questions concerning concurrency control. I shall thank whoever can help me. Question #1 ========= Assume the following (concurrent) schedule, in which both transactions run in a...
5
by: Vayse | last post by:
In my save code, most of items save fine. But sometimes I get a concurrency violation message. "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." It happens on the...
9
by: corey.coughlin | last post by:
Alright, so I've been following some of the arguments about enhancing parallelism in python, and I've kind of been struck by how hard things still are. It seems like what we really need is a more...
4
by: Bob | last post by:
While testing my my program I came up with a consistency exception. My program consists of three datagridviews, One called dgvPostes which is the parent grid and its two children,one called...
7
by: William E Voorhees | last post by:
I'm updating an Access database in a windows multi-user environment. I'm using disconnected data I read data from an Access Data table to a data object I update the data object from a...
0
by: RKT | last post by:
I have a DataGridView bound to an MS Access table. This is a single- user application. When the User is adding or editing a row, the User may click on a Control elsewhere. That Control has context...
5
by: John | last post by:
Hi I have developed the following logic to handle db concurrency violations. I just wonder if someone can tell me if it is correct or if I need a different approach.Would love to know how pros...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.