SEO

Looking for opinions on a problem we recently experienced when a
long-running alter table to add a primary key *appeared* to be the
cause of application lock timeouts due to catalog locks.
Unfortunately diagnostic information is very sparse, but I'm trying to
at least understand whether/how this might happen in theory.

The basic known facts:

* DB2 UDB v7.2 for Solaris

* The table being altered is never accessed by the application (and
table has its own tablespaces for that matter).

* The application is built around J2EE architecture. It does not read
the catalog tables. Its JDBC use is limited to standard DML
operations plus stored procedure calls.

So I'm wondering whether, under some circumstances, DB2's own internal
catalog checking for semantic/authorisation purposes can get locked
out? I would have thought the answer is that there's absolutely no
way that should happen, but what do others think? Or are there any
bugs relating to this?


Jeremy Rickard

"Jeremy Rickard" <jrickard@unisystems.biz> wrote in message
news:d36116ef.0402101045.7c34d754@posting.google.c om...
> Looking for opinions on a problem we recently experienced when a
> long-running alter table to add a primary key *appeared* to be the
> cause of application lock timeouts due to catalog locks.
> Unfortunately diagnostic information is very sparse, but I'm trying to
> at least understand whether/how this might happen in theory.
>
> The basic known facts:
>
> * DB2 UDB v7.2 for Solaris
>
> * The table being altered is never accessed by the application (and
> table has its own tablespaces for that matter).
>
> * The application is built around J2EE architecture. It does not read
> the catalog tables. Its JDBC use is limited to standard DML
> operations plus stored procedure calls.
>
> So I'm wondering whether, under some circumstances, DB2's own internal
> catalog checking for semantic/authorisation purposes can get locked
> out? I would have thought the answer is that there's absolutely no
> way that should happen, but what do others think? Or are there any
> bugs relating to this?
>
>
> Jeremy Rickard

When you create a primary key, DB2 creates a unique index for the key unless
a unique index already exists on those columns.

The index may be created in the same tablespace as the table unless a
different index is specified for indexes (when the was table created). Even
if the index is created in a different tablespace, it will be created in the
same index as other indexes for that table.

Of course, just creating the index might take some time, aside from locking
considerations.

"Mark A" <ma@switchboard.net> wrote in message
news:MM9Wb.316$yg2.38623@news.uswest.net...
>
> When you create a primary key, DB2 creates a unique index for the key
unless
> a unique index already exists on those columns.
>
> The index may be created in the same tablespace as the table unless a
> different index is specified for indexes (when the was table created).
Even
> if the index is created in a different tablespace, it will be created in
the
> same index as other indexes for that table.
>
> Of course, just creating the index might take some time, aside from
locking
> considerations.
>
>

Correction to my post above:

Even if the index is created in a different tablespace, it will be created
in the
same tablespace as other indexes for that table.

"Mark A" <ma@switchboard.net> wrote in message news:<MM9Wb.316$yg2.38623@news.uswest.net>...

> The index may be created in the same tablespace as the table unless a
> different index is specified for indexes (when the was table created). Even
> if the index is created in a different tablespace, it will be created in the
> same index as other indexes for that table.
>
> Of course, just creating the index might take some time, aside from locking
> considerations.

Mark, thanks for the comments but this definitely isn't about locks in
the tablespaces for the table being indexed. That table has its own
tablespaces and it is *never* accessed by the application that
experienced the timeouts. However, the index creation is quite
long-running.

The question was about the possibility of timeouts on catalog access,
even though the application itself does not reference any catalog
tables, i.e. I'm really asking more about possible locking issues
caused to the DBMS itself.

DB2 obviously needs to use catalog data to check permissions etc, but
I've always believed that it maintains its own private, internal
version of the catalog (in some mysterious form) and is not be subject
to the same potential locking issues that, say, the application would
be if it scanned systables (and so eventually hit an exclusive row
lock on the table being indexed). To me it's inconceivable that
indexing one totally independent table should cause DB2 similar issues
and so lock out DML against another, totally unrelated user table.
But I'm now questioning whether the inconceivable can/should ever be
possible? Either in theory, or due to some known bug.


Jeremy Rickard

"Jeremy Rickard" <jrickard@unisystems.biz> wrote in message
news:d36116ef.0402101637.74af72da@posting.google.c om...
>
> Mark, thanks for the comments but this definitely isn't about locks in
> the tablespaces for the table being indexed. That table has its own
> tablespaces and it is *never* accessed by the application that
> experienced the timeouts. However, the index creation is quite
> long-running.
>
> The question was about the possibility of timeouts on catalog access,
> even though the application itself does not reference any catalog
> tables, i.e. I'm really asking more about possible locking issues
> caused to the DBMS itself.
>
> DB2 obviously needs to use catalog data to check permissions etc, but
> I've always believed that it maintains its own private, internal
> version of the catalog (in some mysterious form) and is not be subject
> to the same potential locking issues that, say, the application would
> be if it scanned systables (and so eventually hit an exclusive row
> lock on the table being indexed). To me it's inconceivable that
> indexing one totally independent table should cause DB2 similar issues
> and so lock out DML against another, totally unrelated user table.
> But I'm now questioning whether the inconceivable can/should ever be
> possible? Either in theory, or due to some known bug.
>
> Jeremy Rickard

You can cause locking problems in the catalog with DDL. I don't know if it
happened in the is case, but it is certainly not inconceivable. I don't know
about any internal version of the catalog (in some mysterious form) that is
not be subject to the same potential locking issues.

Personally, I think it always better to create a unique index manually
before defining the primary key. That might help your problem.

"Jeremy Rickard" <jrickard@unisystems.biz> wrote in message
news:d36116ef.0402101045.7c34d754@posting.google.c om...
> Looking for opinions on a problem we recently experienced when a
> long-running alter table to add a primary key *appeared* to be the
> cause of application lock timeouts due to catalog locks.
> Unfortunately diagnostic information is very sparse, but I'm trying to
> at least understand whether/how this might happen in theory.
>
> The basic known facts:
>
> * DB2 UDB v7.2 for Solaris
>
> * The table being altered is never accessed by the application (and
> table has its own tablespaces for that matter).
>
> * The application is built around J2EE architecture. It does not read
> the catalog tables. Its JDBC use is limited to standard DML
> operations plus stored procedure calls.
>
> So I'm wondering whether, under some circumstances, DB2's own internal
> catalog checking for semantic/authorisation purposes can get locked
> out? I would have thought the answer is that there's absolutely no
> way that should happen, but what do others think? Or are there any
> bugs relating to this?
====
This is not a bug.
When you create the user tables, there will be one row in the
SYSIBM.SYSTABLES to describe each user table. When you try to drop a table,
db2 will Row(X) lock the row in SYSIBM.SYSTABLES which desribe the table you
want to drop, and also row (X) lock the row whcih describe the table
corresponding the next key of index (full table name). This will prevent
another session try to create a table with the same table name but your
working session try to drop.

When you add a primary key to a user table, db2 will Z lock the whole user
table, and X-row lock the SYSIBM.SYSTABLES.
Let's say you have four user tables: tbl1, tbl2, tbl3, tbl4
DB2 internal build an index on (creator+tabname):
index key will include (tbl1, xxx), (tbl2, xxx), (tbl3, xxx), (tbl4,xxx) ...
When you alter table tbl3 to add the primary key, if the other session try
to drop table tbl2, this session will get LOCKTIMEOUT. -

"Mark A" <ma@switchboard.net> wrote in message news:<L3fWb.382$yg2.68313@news.uswest.net>...

> You can cause locking problems in the catalog with DDL. I don't know if it
> happened in the is case, but it is certainly not inconceivable. I don't know
> about any internal version of the catalog (in some mysterious form) that is
> not be subject to the same potential locking issues.

Well I probably misworded, but was referring anyway to snippets on DB2
internals that don't tend to get widely advertised but we may
occasionally get hints about. There may or may not be any sort of
internal copy, additional structures/links unavailable to standard
SQL, hard-coded access paths or whatever in any one version of the
product - but I bet you there's always at least one of these and that
DB2 uses some such tricks and techniques to ensure that it does *not*
access the catalog data using standard SQL (as you seem to be
suggesting?).

> Personally, I think it always better to create a unique index manually
> before defining the primary key. That might help your problem.

If you are creating an index (or PK constraint) on one table, other
users can normally access data in all other user tables. However there
may be some cases when this doesn't hold (incremental bind???). I'm
trying to understand these. It would be useful if IBM could clarify
what should be possible, and what isn't.


Jeremy Rickard

"Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message news:<AHiWb.5368$PY.1606@newssvr26.news.prodigy.co m>...

> This is not a bug.
> When you create the user tables, there will be one row in the
> SYSIBM.SYSTABLES to describe each user table. When you try to drop a table,
> db2 will Row(X) lock the row in SYSIBM.SYSTABLES which desribe the table you
> want to drop, and also row (X) lock the row whcih describe the table
> corresponding the next key of index (full table name). This will prevent
> another session try to create a table with the same table name but your
> working session try to drop.

As previously stated, the application doesn't try to create tables.

> When you add a primary key to a user table, db2 will Z lock the whole user
> table, and X-row lock the SYSIBM.SYSTABLES.
> Let's say you have four user tables: tbl1, tbl2, tbl3, tbl4
> DB2 internal build an index on (creator+tabname):
> index key will include (tbl1, xxx), (tbl2, xxx), (tbl3, xxx), (tbl4,xxx) ...
> When you alter table tbl3 to add the primary key, if the other session try
> to drop table tbl2, this session will get LOCKTIMEOUT. -

There's no concurrent DDL. Just DDL on one table versus select,
insert, update, delete (and possibly stored procedure calls) operating
on other, unrelated tables.

I think we've gone round and round a bit here, so here's a specific
example. If you create a new table in SAMPLE, add some data and
commit; then alter the table to add a PK without committing... you
will find that (in a second session) you can select without trouble
from *every* other user table in the database, i.e the exclusive row
locks present in syscat.tables, columns etc. are avoided by DB2,
whether through index lookup or other special non-SQL avoidance
techniques. So clearly some such operations are (and jolly well
should be) compatible. I'm afraid I haven't yet bothered to generate
exhaustive insert/update/delete tests along the same line, but would
be surprised and disappointed if those were any different (incremental
binds OTOH...?)

In a 24x7 environment, saying "sorry we've got to kick everyone off
(or lock you out) because we need to alter a table that nobody is
accessing" might be a bit hard to explain. It's the sort of thing that
would get some of our Oracle newsgroup friends gloating.

So it would be nice if IBM could clarify which DML/DDL operations are
supposed to be be compatible. Publish a matrix maybe.


Jeremy Rickard

"Jeremy Rickard" <jrickard@unisystems.biz> wrote in message
news:d36116ef.0402110556.528c0234@posting.google.c om...
>
> As previously stated, the application doesn't try to create tables.

I didn't mean the example I was talking here is the case which happened on
your system. I just gave you some idea why the lock on the system tables
might cause the user session time out. Of course, you might already knew
this.
To know for sure which reason caused the timeout on your system, you'd
better capture the lock snapshot, find out why the "add primary key" block
some other sessions and caused the lock wait ...

> It's the sort of thing that would get some of our Oracle newsgroup friends
gloating.
It would be too early to say this... -
And there are some similiar policy between two products when lock the system
tables. Anyway I will not talk much about this, especially when we don't
know the root reason to cause this problem.