SEO

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.dk>
http://troels.arvin.dk/

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_INSERTED=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

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_INSERTED=yes

http://publib.boulder.ibm.com/infoce...2luw/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_INSERTED=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.dk>
http://troels.arvin.dk/

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_INSERTED 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_INSERTED.

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

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_INSERTED=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

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

On Apr 20, 9:12 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> 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.

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

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. :-)

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_INSERTED 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_INSERTED.
>
> 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).