473,899 Members | 3,641 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

When will DB2 get MVCC?

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 <tr****@arvin.d k>
http://troels.arvin.dk/
Apr 19 '07 #1
20 6238
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
Apr 20 '07 #2
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/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_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 <tr****@arvin.d k>
http://troels.arvin.dk/
Apr 20 '07 #3
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
Apr 20 '07 #4
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
Apr 21 '07 #5
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
Apr 21 '07 #6
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.

Apr 22 '07 #7
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
Apr 22 '07 #8
Ian
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. :-)

Apr 23 '07 #9
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).
Jun 19 '07 #10

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

Similar topics

9
2165
by: flarkblark | last post by:
I recently had the displeasure of looking at the code required to implement the pop-up menus used in a pulldown menu system for a webpage. The sheer amount of Javascript required was amazing and frankly revolting. It is as though the software "engineers" have been thrown out and replaced with "programmers". That is to say, it is a sophomoric hack job, a hideous kludge.
2
2436
by: Paul Reddin | last post by:
Hi, I'm sure I read somewhere that the SELECTIVITY Clause cannot be used with static SQL, can anybody confirm/deny this? Also, at the risk of a philosophical war, when will the optimizer provide for some real form of hints. It is so very,very painful and very,very time consuming trying to work around bad optimizer plans. I know IBM think it is
2
1038
by: karunakar | last post by:
Hi All When will get .NET 2005 original version Regards, Karun
6
1174
by: Jarod | last post by:
Hey When will be final version of Atlas available ? I mean this new AJAX librabries for ASP.Net. I read a few articles about it and sounds really interesting! Jarod
5
1062
by: Doug Holton | last post by:
About when do you think VB.NET 2.0, Visual Studio .NET 2005, .NET 2.0 and the like will be released? I know there is a beta available now. And I know the name says 2005. I'm just wondering if it will be early next year or later.
5
1299
by: Eric CHAO | last post by:
A lot of application based on python claim that python 2.3 or 2.4 is needed not 2.5, ie. mysqldb. I've been using python for months. I don't care about 2.4 or 2.5. But I like the default icons of python in 2.5. So I just use that, but some scripts can't work on that. When will all these applications support 2.5? Thanks.
20
4023
by: Sebouh | last post by:
Ok, i was thinking about this the other day, and i thought i'd ask here. I'm still a newbie programmer cause i only know a little JAVA and C. But i don't only learn programming languages, i learn theory too. I've learn about computer architecture, algorithm analysis, and all that stuff that you get at the university. My question is, when will i be considered as a good programmer. Or how deep should my programming knowledge be to start working...
5
1423
by: Yarco | last post by:
When using c++, we could do: using abc = xxx.yyy; // am i right? When using python we could do: import abc.efg as efg When will php support: class My_Best_Class {
1
1011
by: =?Utf-8?B?QmVuIFc=?= | last post by:
I had gotten XP Pro x64 last year, when I downgraded from Vista due to some problems. I went to Vista Home Premium, and now I wanted to go back, cause with all my video and audio production, and my internet radio station, it messes up on Vista... Now I lost the code to install XP. When will Microsoft let us download Windows XP Pro x64
4
1694
by: Ken Fine | last post by:
I know about this: http://silverlight.net/forums/14.aspx and this: http://forums.asp.net/1127.aspx Silverlight Beta 2 was announced at TechEd. I was impressed. When does Silverlight get a first-class newsgroup? -KF
0
9843
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10494
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
8039
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7201
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5887
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6081
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4720
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4300
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3317
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.