locking mechanism semantics

[Lennart Jonsson]

Some observations, more out of curiosity than anything else:

[lelle@dustbite ~]$ db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release
"SQL10010"
with level identifier "0201010E".
Informational tokens are "DB2 v10.1.0.0", "s120403", "LINUXAMD64101",
and Fix
Pack "0".
Product is installed at "/opt/ibm/db2/V10.1".


[lelle@dustbite ~]$ db2 "create table t (x int not null primary key )"

Then I use two transactions T1 and T2. LOCKTIMEOUT -1 and Read Stability
to avoid any effect caused by CURRENTLY COMMITTED

[lelle@dustbite T1]$ db2 +c "merge into t as a using ( values (1) ) b(x)
on a.x = b.x when not matched then insert (x) values (b.x) with rs"
DB20000I The SQL command completed successfully.

[lelle@dustbite T2]$ db2 +c "merge into t as a using ( values (1) ) b(x)
on a.x = b.x when not matched then insert (x) values (b.x) with rs"

lock wait as expected

[lelle@dustbite T1]$ db2 commit

This causes SQL0803N primary key violation for T2


I would have expected T2 to try to acquire the lock on T and when that
succeeded, evaluate the predicate for the merge statement. I.e. skip the
insert since T1 committed that row.

Using Repeatable Read has this effect:

[lelle@dustbite T1]$ db2 +c "merge into t as a using ( values (1) ) b(x)
on a.x = b.x when not matched then insert (x) values (b.x) with rr"
DB20000I The SQL command completed successfully.

[lelle@dustbite T2]$ db2 +c "merge into t as a using ( values (1) ) b(x)
on a.x = b.x when not matched then insert (x) values (b.x) with rr"

[lelle@dustbite T1]$ db2 commit
DB20000I The SQL command completed successfully.

Now T2 skips the insert since T1 committed the row.

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of
a query is an empty table. SQLSTATE=02000


The behaviour appears to be correct according to what is guaranteed by
READ STABILITY, but I was a bit surprised anyhow. Any thoughts anyone?


/Lennart

[Frederik Engelen]

> This causes SQL0803N primary key violation for T2
>
> I would have expected T2 to try to acquire the lock on T and when that
>
> succeeded, evaluate the predicate for the merge statement. I.e. skip the
>
> insert since T1 committed that row.
>

Nice observation. DB2 will still Skip Inserted Rows on your scan when using RS. That's reasonable given the fact that Phantom Reads are allowed. If one would want to avoid DB2 to wait in the middle of executing a query because of locks, I guess one would have to resort to some really heavy synchronisation that would bring any database to a grinding halt. Still weird from a client perspective, though.

--
Frederik Engelen

[Lennart Jonsson]

I have not tested this on newer versions, but I suspect it to be the
same. I think DB2_SKIPINSERTED=ON is the default behaviour for later
versions. Anyhow:

[db2inst1@nya-10 ~/T2]$ db2level

DB21085I Instance "db2inst1" uses "64" bits and DB2 code release

"SQL0905A"
with level identifier "060B0107".
Informational tokens are "DB2 v9.5.0.10", "s120809", "IP23364", and Fix
Pack
"10".
Product is installed at "/opt/IBM/db2/V9.5".

[db2inst1@nya-10 ~/T1]$ db2set DB2_SKIPINSERTED=OFF
[db2inst1@nya-10 ~/T1]$ db2stop force && db2start
SQL1064N DB2STOP processing was successful.
SQL1063N DB2START processing was successful.

[db2inst1@nya-10 ~/T1]$ db2 +c "merge into t as a using ( values (1) ) b(x)

on a.x = b.x when not matched then insert (x) values (b.x) with rs"
DB20000I The SQL command completed successfully.

[db2inst1@nya-10 ~/T2]$ db2 +c "merge into t as a using ( values (1) ) b(x)

> on a.x = b.x when not matched then insert (x) values (b.x) with rs"

[ lock wait ]

[db2inst1@nya-10 ~/T1]$ db2 commit

[db2inst1@nya-10 ~/T2]$ [...]

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of
a query is an empty table. SQLSTATE=02000

/Lennart

[Lennart Jonsson]

From the docs:

http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0012389.html

The DB2_SKIPINSERTED registry variable controls whether or not
uncommitted data insertions can be ignored for statements that use the
cursor stability (CS) or the read stability (RS) isolation level.

Uncommitted insertions are handled in one of two ways, depending on the
value of the DB2_SKIPINSERTED registry variable.
When the value is ON, the DB2� server ignores uncommitted insertions,
which in many cases can improve concurrency and is the preferred
behavior for most applications. Uncommitted insertions are treated as
though they had not yet occurred.
When the value is OFF (the default), the DB2 server waits until the
insert operation completes (commits or rolls back) and then processes
the data accordingly. This is appropriate in certain cases. For example:
Suppose that two applications use a table to pass data between
themselves, with the first application inserting data into the table and
the second one reading it. The data must be processed by the second
application in the order presented, such that if the next row to be read
is being inserted by the first application, the second application must
wait until the insert operation commits.
An application avoids UPDATE statements by deleting data and then
inserting a new image of the data.


So SKIPINSERTED should be off. But the semantics is different for 10.1
compared with 9.5. For 10.1 it does not help turning DB2_SKIPINSERTED=OFF

[lelle@dustbite T1]$ db2set DB2_SKIPINSERTED=OFF
[lelle@dustbite T1]$ db2stop force && db2start

SQL1064N DB2STOP processing was successful.
SQL1063N DB2START processing was successful.

[lelle@dustbite T1]$
[lelle@dustbite T1]$ db2 connect to sample

Database Connection Information

Database server = DB2/LINUXX8664 10.1.0
SQL authorization ID = LELLE
Local database alias = SAMPLE

[lelle@dustbite T1]$ db2 +c "merge into t as a using ( values (1) ) b(x)

> on a.x = b.x when not matched then insert (x) values (b.x) with rs"
DB20000I The SQL command completed successfully.

[lelle@dustbite T2]$ db2 +c "merge into t as a using ( values (1) ) b(x)

> on a.x = b.x when not matched then insert (x) values (b.x) with rs"

[lelle@dustbite T1]$ db2 commit

DB20000I The SQL command completed successfully.

[lelle@dustbite T2]$ ...
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "LELLE.T" from having duplicate values for the index key.
SQLSTATE=23505


As mentioned before, this is just observations but it would still be
interesting to find out what caused this change in behaviour between 9.5
and 10.1. Any thoughts anyone?


/Lennart

[Mark A]

On Wed, 15 May 2013 20:31:45 +0200, Lennart Jonsson wrote:

> As mentioned before, this is just observations but it would still be
> interesting to find out what caused this change in behaviour between 9.5
> and 10.1. Any thoughts anyone?

What caused it is a serious inferiority complex by IBM and their desire to be just like Oracle.

[Mladen Gogala]

On Wed, 15 May 2013 20:46:03 +0000, Mark A wrote:


> What caused it is a serious inferiority complex by IBM and their desire
> to be just like Oracle.
>

I have to say, as an old Oracle hack, I don't see any such "inferiority
complex". Serge Rielau, very well known person in the IBM world, has
participated in the Oracle forums for years and admirably held his
ground. DB2 has a lot to be proud of, very useful transactional DDL,
among other things. DB2 is also the one database that can beat Oracle
when it comes both to TPC-C and TPC-H. Nobody else can do that.

There certainly is a desire to take away some of the Oracle's market
share and as a consultant for an independent software vendor who works
with both IBM and Oracle, I certainly do see an increased number of DB2
installations on Linux/Windows. IBM did a great job as far as software is
concerned and they lowered the prices significantly below Oracle's level.
Free partitioning, even with Express-C is also a huge boon.
Unfortunately, for some reason IBM is just not very good at selling the
product to Linux/Windows folks. Again, I just don't see the complex. What
I see is the desire to re-conquer the LUW world, but not a serious
marketing effort which should follow the technical effort. I, for one,
wish IBM was a bit better at selling its product.



--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

[Mark A]

On Thu, 16 May 2013 03:35:56 +0000, Mladen Gogala wrote:

> I have to say, as an old Oracle hack, I don't see any such "inferiority
> complex". Serge Rielau, very well known person in the IBM world, has
> participated in the Oracle forums for years and admirably held his
> ground. DB2 has a lot to be proud of, very useful transactional DDL,
> among other things. DB2 is also the one database that can beat Oracle
> when it comes both to TPC-C and TPC-H. Nobody else can do that.
>
> There certainly is a desire to take away some of the Oracle's market
> share and as a consultant for an independent software vendor who works
> with both IBM and Oracle, I certainly do see an increased number of DB2
> installations on Linux/Windows. IBM did a great job as far as software
> is concerned and they lowered the prices significantly below Oracle's
> level. Free partitioning, even with Express-C is also a huge boon.
> Unfortunately, for some reason IBM is just not very good at selling the
> product to Linux/Windows folks. Again, I just don't see the complex.
> What I see is the desire to re-conquer the LUW world, but not a serious
> marketing effort which should follow the technical effort. I, for one,
> wish IBM was a bit better at selling its product.

I never said (or even implied) that Oracle was better than DB2. But the inferiority complex is clear, as
is evidenced by DB2 trying to emulate everything Oracle does, from both a product feature set, and
marketing practices.

DB2 won't continue to be faster than Oracle if it tries to emulate Oracle locking semantics. As DB2
goes deeper and deeper into the logs on disk for Currently Committed to retrieve pre-locked pages,
then performance will suffer. DB2 performance doesn’t look bad now, because existing DB2
applications are well-behaved by definition (otherwise they wouldn’t run at all), but as application
coding gets worse, and programmers can get away with garbage (as they always have been able to
with Oracle), DB2 is going to be slowing down with Currently Committed and other similar locking
strategies.

Oracle marketing basically consists of offering a lot of features that don't work so well (and amazingly
sometimes not at all) so they can sell their products to managers and executives who don’t have
enough intelligence to figure out whether those features actually work. There is not much emphasis on
quality and reliability at Oracle, and most Oracle shops would not even consider using the current
major Oracle release. Unfortunately, I do see IBM "starting" to emulate that behavior, and I personally
am not too happy about it.

[Peter H. Coffin]

Inferiority or smart marketing by making your product very easy to port
to, so that things like "less expensive" start factoring into a buying
decision?

> DB2 won't continue to be faster than Oracle if it tries to emulate
> Oracle locking semantics. As DB2 goes deeper and deeper into the logs
> on disk for Currently Committed to retrieve pre-locked pages, then

> performance will suffer. DB2 performance doesn???t look bad now,

> because existing DB2 applications are well-behaved by definition

> (otherwise they wouldn???t run at all), but as application coding

> gets worse, and programmers can get away with garbage (as they always
> have been able to with Oracle), DB2 is going to be slowing down with
> Currently Committed and other similar locking strategies.

So long as the Oracle-like features are options and can be turned off,
then conversion becomes a staged process. Take working Oracle-based
application, repoint it at DB2 with all the Oracle-like bells enabled.
Then start turning off features, test, fix, test again, on to the next
performance-inhibiting feature. When you reach something you're happy
with, declare victory. It's WAY more flexible than rewriting the whole
thing.

--
The pluses in my current job include laughing in the face of Nobel
laureates who have just lost the only copy of their data. (Hey,
I'm still a BOFH).
-- Bob Dowling

[Ian]

On Thursday, May 16, 2013 10:39:52 PM UTC-7, Mark A wrote:
> On Thu, 16 May 2013 03:35:56 +0000, Mladen Gogala wrote:
>
> > I have to say, as an old Oracle hack, I don't see any such
> > "inferiority complex". Serge Rielau, very well known person in the
> > IBM world, has participated in the Oracle forums for years and
> > admirably held his ground. DB2 has a lot to be proud of, very
> > useful transactional DDL, among other things. DB2 is also the one
> > database that can beat Oracle when it comes both to TPC-C and
> > TPC-H. Nobody else can do that.
> >
>

> I never said (or even implied) that Oracle was better than DB2. But
> the inferiority complex is clear, as is evidenced by DB2 trying to
> emulate everything Oracle does, from both a product feature set, and
> marketing practices.

You may see it as an inferiority complex, but I see it as pragmatic.

It represents recognition that IBM had not been successful enough in
marketing to convince ISVs or customers to want to pay for the cost of
migrating their code from Oracle to DB2. I've been through more than
a couple of those migration exercises, and they weren't generally
simple or inexpensive.

> DB2 won't continue to be faster than Oracle if it tries to emulate
> Oracle locking semantics. As DB2 goes deeper and deeper into the
> logs on disk for Currently Committed to retrieve pre-locked pages,
> then performance will suffer. DB2 performance doesn't look bad now,
> because existing DB2 applications are well-behaved by definition
> (otherwise they wouldn't run at all), but as application coding gets
> worse, and programmers can get away with garbage (as they always
> have been able to with Oracle), DB2 is going to be slowing down with
> Currently Committed and other similar locking strategies.

All valid points. But again, I point to pragmatism. Better to have a
solution that customers can migrate to than have a "perfect" solution
that has a very small market share.

> Oracle marketing basically consists of offering a lot of features
> that don't work so well (and amazingly sometimes not at all) so they
> can sell their products to managers and executives who don't have
> enough intelligence to figure out whether those features actually
> work. There is not much emphasis on quality and reliability at
> Oracle, and most Oracle shops would not even consider using the
> current major Oracle release. Unfortunately, I do see IBM "starting"
> to emulate that behavior, and I personally am not too happy about
> it.

I'm not sure that is really the case. IMO Toronto tends to
over-engineer features rather than just cobble half-baked solutions
together. Obviously there are some features on the periphery that
aren't or weren't quite there (like GUI tools, the original autoloader
script, etc.), but stuff in the core engine has been generally well
done.

If anything, Toronto takes too long to new release features... look
how long it took them to come out with HADR or pureScale - when other
competitors had working solutions for years.

Ian

[Larry]

As someone else pointed out, IBM has enhanced DB2 with many of these
capabilities in order to facilitate migrations - in many instances now,
Oracle to DB2 is an "enablement" and not even a migration. As far as the
locking semantics are concerned, if performance does suffer, I'm sure a
way will be found to improve upon it. Even if it does, there are
distinct advantages to not having to manage rollback segments. And with
databases, there are often tradeoffs in the features of one vs. another.

Larry Edelstein

--
Larry E.

--- news://freenews.netfront.net/ - complaints: ne...@netfront.net ---

[Mark A]

On Fri, 17 May 2013 16:19:50 -0700, Ian wrote:

> You may see it as an inferiority complex, but I see it as pragmatic.
>
> It represents recognition that IBM had not been successful enough in
> marketing to convince ISVs or customers to want to pay for the cost of
> migrating their code from Oracle to DB2. I've been through more than a
> couple of those migration exercises, and they weren't generally simple
> or inexpensive.

I understand the intent, but I think they should have not spent the significant amounts of development
money and just lowered to price, which would help them comptete against SQL Server and Oracle.

> I'm not sure that is really the case. IMO Toronto tends to
> over-engineer features rather than just cobble half-baked solutions
> together. Obviously there are some features on the periphery that
> aren't or weren't quite there (like GUI tools, the original autoloader
> script, etc.), but stuff in the core engine has been generally well
> done.
>
> If anything, Toronto takes too long to new release features... look how
> long it took them to come out with HADR or pureScale - when other
> competitors had working solutions for years.
>
> Ian

I completely agree that Toronto over-engineers stuff. But not sure I agree with you about HADR and
pureScale.

HADR has been around for a number of years and one of the best features of DB2. Although Oracle has
an equivalent, SQL Server does not really. The unfortunate thing is how long it took IBM to have read-
only on Standby, and even now it does not support pureXML and LOB columns (except INLINE'd
portion). IBM should have implemented multiple standbys a long time ago (not just starting from
V10.1).

From what I can see, IBM is now relegating HADR to second class status and not devoting the
development money it deserves to make it even better. Dale McGinnis knows exactly what to do to
have HADR support DB2 major release rolling upgrades similar to how it now supports rolling DB2
fixpack upgrades, but his boss will not give him the resources to get it done because pureScale is
where IBM wants all the customers to go. pureScale is over-engineered, very complex, very expensive,
not even close to 100% compatible with existing apps and DB2 utilities, and a bit buggy IMO.

Regarding Oracle having RAC around for quite a few years: yes that is technically true, but RAC doesn't
work so well and not that many Oracle customers use it because it is prone to crash.

[Mark A]

On Fri, 17 May 2013 20:54:07 -0400, Larry wrote:

> As someone else pointed out, IBM has enhanced DB2 with many of these
> capabilities in order to facilitate migrations - in many instances now,
> Oracle to DB2 is an "enablement" and not even a migration.

Yes, I understand the theory, but I don't agree with it. Better to compete with lower DB2 prices rather
than to sink many millions into trying to make it 100% compatible for migrations. That is my opinion,
having done both DB2 to Oracle migrations, and Oracle to DB2 migrations.

> As far as the
> locking semantics are concerned, if performance does suffer, I'm sure a
> way will be found to improve upon it. Even if it does, there are
> distinct advantages to not having to manage rollback segments. And with
> databases, there are often tradeoffs in the features of one vs. another.

There is no free lunch. In order to have a fast running application using DB2 or Oracle, it takes good
programming techniques, such as frequent commits and frequent closing of transactions. Trying to
have the database get around poor coding techniques will lead to even worse coding techniques, and
performance will spiral downward like it already has with typical Oracle applications. Of course, when
Oracle does benchmarks, they use well-written code, but almost all Oracle customers have garbage
code these days.