When will DB2 get MVCC?

[Troels Arvin]

Hello,

In a rather large database, I face this problem:
Several tables are regularly updated through rather long-running DELETE
+INSERTs. Using uncommitted read as isolation level seems unsafe, as I'd
like consistent data (that's one of the main points of using a DBMS in
the first place, in my opinion).

Almost all other significant databases have gotten MultiVersion
Concurrency Control (MVCC)[1] by now, either by default, or optionally
(like in MSSQL2005: the DBA needs to activate it explicitly[2]).

The very nice and obvious advantage with MVCC is that readers don't block
writers, and vice versa: If I'm working read-only with a table, I simply
work with the latest committed data for that table at the time when my
query started. A concurrently running transaction with write activity
will continue working, but the reader doesn't see its actions unless
he starts a new query after the writing transaction has finished.
The obvious disadvantage -- more temporary data for the DBMS to keep
track of -- is a price which I'd gladly pay.

I've read a number of articles on how to mitigate DB2's locking problem,
but they either seem to suggest actions which don't really improve the
situation significantly, or they suggest actions which I find unsafe/ugly
(committing more often; but I _want_ consistency!).

What's keeping IBM?


Notes:
1) many different synonyms seem to exist for MVCC, such as
"row versioning", "snapshot isolation", ...
2) for reasons that I find strange -- turning it off should be the
optional setting if they were to ask me

--
Regards,
Troels Arvin <troels@arvin.d k>

http://troels.arvin.dk/

[Serge Rielau]

Re: When will DB2 get MVCC?

Troels Arvin wrote:

What's keeping IBM?

Nothing. Last Comitted is in Cheetah...

DB2 is tip toeing in the area.
Have you tried:
db2set DB2_SKIP_INSERT ED=yes
There are similar settings for delete and update

Cheers
Serge

PS: One of the things that is most confusing about MvCC is that it is
confused with concurrency. WHich is your request MvCC? If so Which
level: Transaction, statement, long term
Or simply readers not blocking writers which can be achieved with LAST
COMMITTED ?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Troels Arvin]

Re: When will DB2 get MVCC?

On Thu, 19 Apr 2007 22:44:56 -0400, Serge Rielau wrote:

>What's keeping IBM?

Nothing. Last Comitted is in Cheetah...

Cheetah seems to be Informix(?)

DB2 is tip toeing in the area.
Have you tried:
db2set DB2_SKIP_INSERT ED=yes

http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/

com.ibm.db2.udb .doc/admin/c0012389.htm sounds like something we should
consider. I'm somewhat confused by the page, however; it discourages
changing to DB2_SKIP_INSERT ED=yes, if
- you use the DBMS for inter-process communication; we
do not do this
- you don't use many DELETEs and subsequent INSERTs; we do this
for some of our data

PS: One of the things that is most confusing about MvCC is that it is
confused with concurrency. WHich is your request MvCC? If so Which
level: Transaction, statement, long term Or simply readers not blocking
writers which can be achieved with LAST COMMITTED ?

I agree that MVCC is not just MVCC -- that the concept has many nuances.
However, first and foremost, I don't want readers to block writers, but
as I see it, LAST COMMITTED is Informix land (which is irrelevant for us).

DB2's locking model makes it hard for me to understand why anyone would
ever recommend DB2 for anything but strict OLTP (where I can see that non-
MVCC may have performance advantages). I'm involved with systems that sit
somewhere between OLTP and datawarehousing , and for this, I regularly run
into trouble with DB2's locking. Everytime I go looking for information
on how to alleviate such trouble, I seem to run into new registry
variables that I could try adjusting (or ugly hacks which seem to
compromise consistency).

--
Regards,
Troels Arvin <troels@arvin.d k>

http://troels.arvin.dk/

[Serge Rielau]

Re: When will DB2 get MVCC?

Troels Arvin wrote:

On Thu, 19 Apr 2007 22:44:56 -0400, Serge Rielau wrote:

>>What's keeping IBM?

>Nothing. Last Comitted is in Cheetah...

>
Cheetah seems to be Informix(?)

Yes. You ask what's keeping IBM. Informix is IBM.
IBM picked IDS to lead the charge.
If you believe that this means that a future version of DB2 may follow
that is certainly not an outlandish thought.

Thanks for answering my question. You care for concurrency, not the
snapshot isolation. Most customers fall into that category.
True MvCC is a drain on throughput because it greatly increases I/O.

DB2_SKIP_INSERT ED may become the default behavior soon.
It is LAST COMMITTED semantics.
The fear for DELETE/INSERT combination is related to applications that
do DELETE/INSERT instead of UPDATE. I doubt you fall into that category.

I ALWAYS recommend DB2_SKIP_INSERT ED.

These hacks you refer to oftentimes are bleeding edge behavior that
become default later. Customers must have the opportunity to acquaint
themselves with new behaviour and opt in rather than opt out.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[MeBuggyYouJane]

Re: When will DB2 get MVCC?

Serge Rielau wrote:

Troels Arvin wrote:

>What's keeping IBM?

Nothing. Last Comitted is in Cheetah...
>
DB2 is tip toeing in the area.
Have you tried:
db2set DB2_SKIP_INSERT ED=yes
There are similar settings for delete and update
>
Cheers
Serge
>
PS: One of the things that is most confusing about MvCC is that it is
confused with concurrency. WHich is your request MvCC? If so Which
level: Transaction, statement, long term
Or simply readers not blocking writers which can be achieved with LAST
COMMITTED ?
>

The following texts (two of them Bibles of transaction processing),
treat MVCC either within chapters on concurrency or in a dedicated chapter.

Weikum&Vossen, chapter 5
Gray&Reuter, chapter 7 (12.4)
Elmasri&Navathe , chapter 18 (5)
Date, chapter 16 (notes)

"There have been several attempts at implementing time domain
addressing, but the best-developed one comes from Dave Reed. The
leading examples of these ideas today [1993] are Interbase, Oracle,
Rdb, and Postgres. In Reed's design, each object has a sequence of
values over time".
-- G&R, page 436

IBM just has a different philosophy.

Beyond that, IBM's clients likely don't care. Here's why:

- according to the Gartner reports, going back to 2000, DB2 has majority
revenue and revenue growth on the MainFrame.
- said MainFrame clients mostly run decades olde COBOL code, running DB2
data through cursors and copybooks, just like they did for the VSAM data
for which the code was originally written. They have no use for MVCC
semantics.
- the remaining pool of convertible VSAM clients has got to be getting
as shallow as an Australian reservoir. There is a growing, albeit
slowly so, number of clients who have no use for MVCC semantics.
- DB2 on *nix is not growing much, if at all, where the non-COBOL view
of data prevails. There are a not so growing number of clients who
would benefit from MVCC semantics.
- IBM, in my opinion, made a big mistake in stuffing XML in v9; it
turned off the relationalists in the crowd (even, reading between the
lines, some IBM-ers who lurk here for our benefit), and there really
isn't much traffic here about things V9. The XML loonies wouldn't bother
with a real database anyway, much less pay for one. This year's Gartner,
due soon for subscribers (not I) and a bit later for the press release
condensed version, will be informative. I'd be willing to bet a nickel
that DB2 fares less well than the competition. Of most concern is how
the mix between MF and *nix comes up, and whether V9 has been a home
run. IBM could have added MVCC (called by some name) to V9, but chose
instead to add XML (must have caused Dr. Codd to soil his coffin, and
the IMS division to party). IBM thumbed their nose at those who would
benefit from MVCC semantics.
- the resource cavil is a strawman. If you want MVCC semantics, you pay
for them. G&R, and its references, which go back quite a ways further,
make that clear. TANSTAFL

[Serge Rielau]

Re: When will DB2 get MVCC?

Well, to each their opinions.
The market would be boring if everyone always came to the same
conclusion on what's right.
I too am looking forward to the Gartner numbers...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Mark A]

Re: When will DB2 get MVCC?

On Apr 20, 9:12 pm, Serge Rielau <srie...@ca.ibm .comwrote:

Well, to each their opinions.
The market would be boring if everyone always came to the same
conclusion on what's right.
I too am looking forward to the Gartner numbers...
>
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

MVCC (or at least the concurrency part of it) just promotes bad
programming. Usually the bad programming continually gets worse until
the resulting application is a gigantic mess. With good programming
techniques and the right environment variables, etc, DB2 is just fine
the way it is.

[Knut Stolze]

Re: When will DB2 get MVCC?

Mark A wrote:

MVCC (or at least the concurrency part of it) just promotes bad
programming. Usually the bad programming continually gets worse until
the resulting application is a gigantic mess. With good programming
techniques and the right environment variables, etc, DB2 is just fine
the way it is.

I surely tend to agree with your statement, Mark. However, the truth is
that you usually have to deal with bad programming and try to keep it from
getting worse.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Ian]

Re: When will DB2 get MVCC?

Knut Stolze wrote:

I surely tend to agree with your statement, Mark. However, the truth is
that you usually have to deal with bad programming and try to keep it from
getting worse.

I don't know, Knut. My solution is usually to educate or replace bad
programmers, instead of enabling more bad behaviour. :-)

[MeBuggyYouJane]

Re: When will DB2 get MVCC?

Serge Rielau wrote:

Troels Arvin wrote:

>On Thu, 19 Apr 2007 22:44:56 -0400, Serge Rielau wrote:

>>>What's keeping IBM?
>>Nothing. Last Comitted is in Cheetah...

>>
>Cheetah seems to be Informix(?)

Yes. You ask what's keeping IBM. Informix is IBM.
IBM picked IDS to lead the charge.
If you believe that this means that a future version of DB2 may follow
that is certainly not an outlandish thought.
>
Thanks for answering my question. You care for concurrency, not the
snapshot isolation. Most customers fall into that category.
True MvCC is a drain on throughput because it greatly increases I/O.
>
DB2_SKIP_INSERT ED may become the default behavior soon.
It is LAST COMMITTED semantics.
The fear for DELETE/INSERT combination is related to applications that
do DELETE/INSERT instead of UPDATE. I doubt you fall into that category.
>
I ALWAYS recommend DB2_SKIP_INSERT ED.
>
These hacks you refer to oftentimes are bleeding edge behavior that
become default later. Customers must have the opportunity to acquaint
themselves with new behaviour and opt in rather than opt out.
>
Cheers
Serge

The rest of this thread doesn't show up on my newsreader. Ah well. For
the record, you owe me a nickel. The Gartner report is out (a month
later than normal). As expected, DB2 trails. As expected, DB2 is
mainframe mostly. Same as previous years. I don't subscribe, so the
details are unknown to me. And I don't quite get the M$ growth. Open
Source is tough to judge, due to cost model.

http://www.gartner.com/it/page.jsp?id=507466

for those who might be interested.

(And if you've a mind to, the nickel plus any overage to Amnesty
International).

[Serge Rielau]

Re: When will DB2 get MVCC?

You need a better newsreader (or provider).
Your post:
MeBuggyYouJane wrote:
IBM just has a different philosophy.

Beyond that, IBM's clients likely don't care. Here's why:

- according to the Gartner reports, going back to 2000, DB2 has majority
revenue and revenue growth on the MainFrame.
- said MainFrame clients mostly run decades olde COBOL code, running DB2
data through cursors and copybooks, just like they did for the VSAM data
for which the code was originally written. They have no use for MVCC
semantics.
- the remaining pool of convertible VSAM clients has got to be getting
as shallow as an Australian reservoir. There is a growing, albeit
slowly so, number of clients who have no use for MVCC semantics.
- DB2 on *nix is not growing much, if at all, where the non-COBOL view
of data prevails. There are a not so growing number of clients who
would benefit from MVCC semantics.
- IBM, in my opinion, made a big mistake in stuffing XML in v9; it
turned off the relationalists in the crowd (even, reading between the
lines, some IBM-ers who lurk here for our benefit), and there really
isn't much traffic here about things V9. The XML loonies wouldn't bother
with a real database anyway, much less pay for one. This year's Gartner,
due soon for subscribers (not I) and a bit later for the press release
condensed version, will be informative. I'd be willing to bet a nickel
that DB2 fares less well than the competition. Of most concern is how
the mix between MF and *nix comes up, and whether V9 has been a home
run. IBM could have added MVCC (called by some name) to V9, but chose
instead to add XML (must have caused Dr. Codd to soil his coffin, and
the IMS division to party). IBM thumbed their nose at those who would
benefit from MVCC semantics.
- the resource cavil is a strawman. If you want MVCC semantics, you pay
for them. G&R, and its references, which go back quite a ways further,
make that clear. TANSTAFL
--
My post:
Well, to each their opinions.
The market would be boring if everyone always came to the same
conclusion on what's right.
I too am looking forward to the Gartner numbers...
--
Don't recall me promising you a nickel..
Either way I'm still looking forward to the Gartner report (not the
press release)
The press release doesn't substantiate any of what you conclude.
For one XML wasn't shipped until mid last year. Way to late to have any
meaning in the 2006 market.
Are you into gardening? A green thumb requires patience.
I won't comment on the numbers themselves as the way how they are
arrived at is getting more and more dubious allowing anyone to claim
pretty much anything and, as you say, "free" offerings aren't included.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Bernard Dhooghe]

Re: When will DB2 get MVCC?

It makes sense that a database that offers transactions would also
offer consistent read capabilities (transactions being there hust
because there is the notion of consistent real-world state), allowing
consistent real-world state reporting without halting the database
evolution (24/24 7/7 world).

Storage devices increase in capacity and throughput as do main
processor performances, so yes DB2 UDB should have this capability.

Bernard Dhooghe

[DA Morgan]

Re: When will DB2 get MVCC?

Serge Rielau wrote:

You need a better newsreader (or provider).
Your post:
MeBuggyYouJane wrote:
IBM just has a different philosophy.
>
Beyond that, IBM's clients likely don't care. Here's why:
>
- according to the Gartner reports, going back to 2000, DB2 has majority
revenue and revenue growth on the MainFrame.
- said MainFrame clients mostly run decades olde COBOL code, running DB2
data through cursors and copybooks, just like they did for the VSAM data
for which the code was originally written. They have no use for MVCC
semantics.
- the remaining pool of convertible VSAM clients has got to be getting
as shallow as an Australian reservoir. There is a growing, albeit
slowly so, number of clients who have no use for MVCC semantics.
- DB2 on *nix is not growing much, if at all, where the non-COBOL view
of data prevails. There are a not so growing number of clients who
would benefit from MVCC semantics.
- IBM, in my opinion, made a big mistake in stuffing XML in v9; it
turned off the relationalists in the crowd (even, reading between the
lines, some IBM-ers who lurk here for our benefit), and there really
isn't much traffic here about things V9. The XML loonies wouldn't bother
with a real database anyway, much less pay for one. This year's Gartner,
due soon for subscribers (not I) and a bit later for the press release
condensed version, will be informative. I'd be willing to bet a nickel
that DB2 fares less well than the competition. Of most concern is how
the mix between MF and *nix comes up, and whether V9 has been a home
run. IBM could have added MVCC (called by some name) to V9, but chose
instead to add XML (must have caused Dr. Codd to soil his coffin, and
the IMS division to party). IBM thumbed their nose at those who would
benefit from MVCC semantics.
- the resource cavil is a strawman. If you want MVCC semantics, you pay
for them. G&R, and its references, which go back quite a ways further,
make that clear. TANSTAFL
--
My post:
Well, to each their opinions.
The market would be boring if everyone always came to the same
conclusion on what's right.
I too am looking forward to the Gartner numbers...
--
Don't recall me promising you a nickel..
Either way I'm still looking forward to the Gartner report (not the
press release)
The press release doesn't substantiate any of what you conclude.
For one XML wasn't shipped until mid last year. Way to late to have any
meaning in the 2006 market.
Are you into gardening? A green thumb requires patience.
I won't comment on the numbers themselves as the way how they are
arrived at is getting more and more dubious allowing anyone to claim
pretty much anything and, as you say, "free" offerings aren't included.
>
Cheers
Serge

This post is a keeper Serge: Thanks.
--
Daniel A. Morgan
University of Washington
damorgan@x.wash ington.edu (replace x with u to respond)

[Serge Rielau]

Re: When will DB2 get MVCC?

DA Morgan wrote:

This post is a keeper Serge: Thanks.

If I ever come to visit I hope to see it framed, on the wall and in the
living room

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[MeBuggyYouJane]

Re: When will DB2 get MVCC?

Serge Rielau wrote:

You need a better newsreader (or provider).
Your post:
MeBuggyYouJane wrote:
IBM just has a different philosophy.
>
Beyond that, IBM's clients likely don't care. Here's why:
>
- according to the Gartner reports, going back to 2000, DB2 has majority
revenue and revenue growth on the MainFrame.
- said MainFrame clients mostly run decades olde COBOL code, running DB2
data through cursors and copybooks, just like they did for the VSAM data
for which the code was originally written. They have no use for MVCC
semantics.
- the remaining pool of convertible VSAM clients has got to be getting
as shallow as an Australian reservoir. There is a growing, albeit
slowly so, number of clients who have no use for MVCC semantics.
- DB2 on *nix is not growing much, if at all, where the non-COBOL view
of data prevails. There are a not so growing number of clients who
would benefit from MVCC semantics.
- IBM, in my opinion, made a big mistake in stuffing XML in v9; it
turned off the relationalists in the crowd (even, reading between the
lines, some IBM-ers who lurk here for our benefit), and there really
isn't much traffic here about things V9. The XML loonies wouldn't bother
with a real database anyway, much less pay for one. This year's Gartner,
due soon for subscribers (not I) and a bit later for the press release
condensed version, will be informative. I'd be willing to bet a nickel
that DB2 fares less well than the competition. Of most concern is how
the mix between MF and *nix comes up, and whether V9 has been a home
run. IBM could have added MVCC (called by some name) to V9, but chose
instead to add XML (must have caused Dr. Codd to soil his coffin, and
the IMS division to party). IBM thumbed their nose at those who would
benefit from MVCC semantics.
- the resource cavil is a strawman. If you want MVCC semantics, you pay
for them. G&R, and its references, which go back quite a ways further,
make that clear. TANSTAFL
--
My post:
Well, to each their opinions.
The market would be boring if everyone always came to the same
conclusion on what's right.
I too am looking forward to the Gartner numbers...
--
Don't recall me promising you a nickel..
Either way I'm still looking forward to the Gartner report (not the
press release)
The press release doesn't substantiate any of what you conclude.

Shall we examine?

What I said:
I'd be willing to bet a nickel that DB2 fares less well than the
competition.
What Gartner said:
Oracle and Microsoft experienced growth rates above the industry average
at 14.9 percent and 28 percent, respectively, while IBM trailed in terms
of growth with an 8.8 percent revenue increase in 2006.

My conclusion:
I was right.


What I said:
Of most concern is how the mix between MF and *nix comes up, and whether
V9 has been a home run.
What Gartner said:
Each of the major three vendors continue to dominate their particular
platform; Oracle on Unix and Linux, Microsoft on Windows, and IBM on the
zSeries.

My conclusion:
I was right. Not so clear cut as point 1, but a reasonable deduction.
V9 does not yet exist on MF, so that sector of growth can't be V9.
Since overall growth is behind the industry, it's reasonable to conclude
that IBM's off-MF share is not growing, by much anyway. Not much room
in that growth number for V9 being a Home Run.


Now, I bear no animus toward IBM or its staff (well, may be Chamberlin
on a professional level). My CubeLand has laid it's bed with DB2. It
is better for me if DB2 is a better RDBMS. The revenge of IMS tack is
foolish, and eventually will fail; along with SOA and other such
claptrap. And, what's up with Oracle syntax "compatibility" ?? Will
snapshot isolation be implemented, too? I didn't see that until this
morning. 1 April is long past, I think.

For one XML wasn't shipped until mid last year. Way to late to have any
meaning in the 2006 market.
Are you into gardening? A green thumb requires patience.
I won't comment on the numbers themselves as the way how they are
arrived at is getting more and more dubious allowing anyone to claim
pretty much anything and, as you say, "free" offerings aren't included.

Did you cavil when Gartner showed DB2 as doing well, circa 2002?

>
Cheers
Serge