Concurrency In DB2

Michael Liberman

Concurrency In DB2
Hi all ,

We experiencing a problem in our application design process for
concurrency.
There are 2 process which are running in parallel mode. one performs
INSERT statement on a table in a segmented tablespace and the
other one performs a SELECT statement on table same table.
the SELECT process performs a TABLESPACE SCAN.
The INSERT process locks the page in a X mode. The second process
which suppose to read the locked page as part of the table space scan
waits for
the lock to be released and finally abends because of a deadlock.
The tablespace lock size is ROW.
My question is why DB2 force me to wait until the release of the
lock considering the fact the I don't want to read the rows that has
just been inserted.
Why can't DB2 just ignore the uncommitted rows (I know that ORACLE
works that way) and let me read only the rows that been committed
earlier.
The isolation level is CS (we cannot use the UR isolation level for
this design) and we commit every 50 sql statements (and again cannot be
changed due
to application consideration)
I believe that this is more theoretical question because probably
most of you will say "This is how DB2 works" :-)
but still, any thoughts or opinion will be greatly appreciated.

TIA,



Michael Liberman
DBA Team Leader
IBM Certified Solution Expert
DB2 V7 for OS/390 & z/OS
972 54 4685756
< http://www.novelya.com/ > www.novelya.com



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Hans-Joachim Mai

Re: Concurrency In DB2
(in response to Michael Liberman)
Hi Michael,

some more detail would be helpful.
Why are you TS-scanning, maybe an index could help?
Are you deadlocking because the select runs under repeatable read/read stability isolation,
or due to lock escalation?
Do the qualified rows of the select and the inserts necessarily intersect, or are the problems
just related to TS-scan access?
How often will the select, an insert be executed?

DB2 and Oracle differ in the implementation of the I of the "ACID" concepts,
resulting in a different interpretation of "what is the consistent truth" a sql/user will experience.

Regards,
Achim

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Michael Liberman
Sent: Sunday, January 09, 2005 11:55 AM
To: [login to unmask email]
Subject: [DB2-L] Concurrency In DB2


Hi all ,

We experiencing a problem in our application design process for concurrency.
There are 2 process which are running in parallel mode. one performs INSERT statement on a table in a segmented tablespace and the
other one performs a SELECT statement on table same table.
the SELECT process performs a TABLESPACE SCAN.
The INSERT process locks the page in a X mode. The second process which suppose to read the locked page as part of the table space scan waits for
the lock to be released and finally abends because of a deadlock.
The tablespace lock size is ROW.
My question is why DB2 force me to wait until the release of the lock considering the fact the I don't want to read the rows that has just been inserted.
Why can't DB2 just ignore the uncommitted rows (I know that ORACLE works that way) and let me read only the rows that been committed earlier.
The isolation level is CS (we cannot use the UR isolation level for this design) and we commit every 50 sql statements (and again cannot be changed due
to application consideration)
I believe that this is more theoretical question because probably most of you will say "This is how DB2 works" :-)
but still, any thoughts or opinion will be greatly appreciated.

TIA,



Michael Liberman
DBA Team Leader
IBM Certified Solution Expert
DB2 V7 for OS/390 & z/OS
972 54 4685756
www.novelya.com < http://www.novelya.com/ >


--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Kirk Hampton

Re: Concurrency In DB2
(in response to Hans-Joachim Mai)
Unfortunately, a tablespace scan will act like Repeatable Read,
even though the Select might have been bound as a lower
locking strategy like Cursor Stability. I believe this is a documented
behavior. Your best bet is to eliminate the scan by adding
predicates or changing your indexing.

Kirk Hampton
DB2 z/OS Sysprog
IBM Certified Solutions Expert - DB2 V7 Database Administration OS/390
Capgemini Energy
Dallas, Texas





Hans-Joachim.Mai@
SDM.DE To: [login to unmask email]
Sent by: DB2 Data cc:
Base Discussion Subject: Re: [DB2-L] Concurrency In DB2
List
<[login to unmask email]
ORG>


01/10/2005 04:06
AM
Please respond to
DB2 Database
Discussion list
at IDUG






Hi Michael,

some more detail would be helpful.
Why are you TS-scanning, maybe an index could help?
Are you deadlocking because the select runs under repeatable read/read
stability isolation,
or due to lock escalation?
Do the qualified rows of the select and the inserts necessarily intersect,
or are the problems
just related to TS-scan access?
How often will the select, an insert be executed?

DB2 and Oracle differ in the implementation of the I of the "ACID"
concepts,
resulting in a different interpretation of "what is the consistent truth" a
sql/user will experience.

Regards,
Achim

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Michael Liberman
Sent: Sunday, January 09, 2005 11:55 AM
To: [login to unmask email]
Subject: [DB2-L] Concurrency In DB2

Hi all ,

We experiencing a problem in our application design process for
concurrency.
There are 2 process which are running in parallel mode. one performs
INSERT statement on a table in a segmented tablespace and the
other one performs a SELECT statement on table same table.
the SELECT process performs a TABLESPACE SCAN.
The INSERT process locks the page in a X mode. The second process which
suppose to read the locked page as part of the table space scan waits for
the lock to be released and finally abends because of a deadlock.
The tablespace lock size is ROW.
My question is why DB2 force me to wait until the release of the lock
considering the fact the I don't want to read the rows that has just been
inserted.
Why can't DB2 just ignore the uncommitted rows (I know that ORACLE
works that way) and let me read only the rows that been committed earlier.
The isolation level is CS (we cannot use the UR isolation level for
this design) and we commit every 50 sql statements (and again cannot be
changed due
to application consideration)
I believe that this is more theoretical question because probably most
of you will say "This is how DB2 works" :-)
but still, any thoughts or opinion will be greatly appreciated.

TIA,



Michael Liberman
DBA Team Leader
IBM Certified Solution Expert
DB2 V7 for OS/390 & z/OS
972 54 4685756
www.novelya.com


---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm






*********************************************************************************
Confidentiality Notice: This email message, including any attachments,
contains or may contain confidential information intended only for the
addressee. If you are not an intended recipient of this message, be
advised that any reading, dissemination, forwarding, printing, copying
or other use of this message or its attachments is strictly prohibited. If
you have received this message in error, please notify the sender
immediately by reply message and delete this email message and any
attachments from your system.
*********************************************************************************
TXU Energy (REP Certificate No. 10004) and TXU Power are not
the same company as TXU Electric Delivery and are not regulated
by the Public Utility Commission of Texas, and you do not have to
buy TXU Energy's or TXU Power's products to continue to receive
quality regulated services from TXU Electric Delivery.
*********************************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: Concurrency In DB2
(in response to Kirk Hampton)
Why? If the applications is bound with Isolation Level CS
and CURRENTDATA is NO, there will be no locks on SELECT,
regardless of the type of the access path.

And even with CURRENTDATA YES and LOCKSIZE = PAGE or ROW or ANY,
only the current page gets an S lock.

Kind regards

Bernd



Am Dienstag, 11. Januar 2005 15:23 schrieben Sie:
> Unfortunately, a tablespace scan will act like Repeatable Read,
> even though the Select might have been bound as a lower
> locking strategy like Cursor Stability. I believe this is a documented
> behavior. Your best bet is to eliminate the scan by adding
> predicates or changing your indexing.
>
> Kirk Hampton
> DB2 z/OS Sysprog
> IBM Certified Solutions Expert - DB2 V7 Database Administration OS/390
> Capgemini Energy
> Dallas, Texas
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm