By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 2,139 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

DB2 Locking: Subquery to a Long Federated Query

P: n/a
I have a situation that is as follows:

First an Index of what is discussed below:

FED_A ->> Federated Objected/Table that has lots of rows

LOC_B ->> Local Table which is selected and updated.

My data flow works through a Informatica workflow and is therefore not
exactly like a single transaction.

This is the start of this Data Flow:

SELECT * FROM FED_A
WHERE FED_A. COL_A >= ( SELECT MAX(LOC_B.COL_A)
FROM LOC_B )

Question One is:

If the data from FED_A takes 5 minutes to fetch and get back which
means it will have a share lock on selected rows for at least that
long.

And if The Subquery on LOC_B took 1 second to execute.

Is there any lock being held at all on Table LOC_B.

My own opinion is that none should be held and not for long.

The LOC_B.COL_A has an index on it and it returns fast when
executed independently (less than a second).

However we are finding that when the above query is in execution
there is SHARE ROW LEVEL LOCK on Table LOC_B which is locking UPDATES
to LOC_B further down the workflow out.

My main question is what is DB2's behaviour for locking on Table
LOC_B in this situtation. Does it hold a lock on LOC_B until the Unit
of Work(UOW) denoted by the whole query is returned?

And if so why does it appear to hold a Share Row Level Lock? Which
Row it will lock?

Sridhar

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Zri Man wrote:
I have a situation that is as follows:

First an Index of what is discussed below:

FED_A ->> Federated Objected/Table that has lots of rows

LOC_B ->> Local Table which is selected and updated.

My data flow works through a Informatica workflow and is therefore not
exactly like a single transaction.

This is the start of this Data Flow:

SELECT * FROM FED_A
WHERE FED_A. COL_A >= ( SELECT MAX(LOC_B.COL_A)
FROM LOC_B )

Question One is:

If the data from FED_A takes 5 minutes to fetch and get back which
means it will have a share lock on selected rows for at least that
long.

And if The Subquery on LOC_B took 1 second to execute.

Is there any lock being held at all on Table LOC_B.

My own opinion is that none should be held and not for long.

The LOC_B.COL_A has an index on it and it returns fast when
executed independently (less than a second).

However we are finding that when the above query is in execution
there is SHARE ROW LEVEL LOCK on Table LOC_B which is locking UPDATES
to LOC_B further down the workflow out.

My main question is what is DB2's behaviour for locking on Table
LOC_B in this situtation. Does it hold a lock on LOC_B until the Unit
of Work(UOW) denoted by the whole query is returned?

And if so why does it appear to hold a Share Row Level Lock? Which
Row it will lock?

Sridhar

What's the isolation level? For CS I woudl not expect LOCB to hold the lock.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
It is CS

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.