Lock Situation with a special Sql-Statement

[Michael Welp]

Hi,

I have a problem with a special sql-statement. Perhaps somebody could help
me or explain the behaviour of the DB2.

My configuration : IBM DB2 V8.1.5 / Windows 2000 Server

The SQL-Statement :
Select * from PROF.WH_EINZELPROFIL
where lagerstrangid in
(select lagerstrangid from prof.wh_kr_l20_ag where
AGAKTUELL in(3190,3199) AND ZIELNAME NOT IN ('PBF','WBF','ASGF','APMA'))
FOR UPDATE WITH RS

I some situations the result-set of the internal Select-Statement (select
lagerstrangid ...) is empty and in order to that also the result-set of the
whole sql-statement is empty. But the sql-statement locks all records in the
table WH_Einzelprofil with an Update-Lock. I can't understand that behavior
?

The relation WH_Einzelprofil is keyed by an EinzelprofilID, but have also an
index on LagerstrangID.

Can anybody help me ?

Michael

[Mark A]

"Michael Welp" <we**@meyerwerft.de> wrote in message
news:co**********@news.dtag.de...

Hi,

I have a problem with a special sql-statement. Perhaps somebody could help
me or explain the behaviour of the DB2.

My configuration : IBM DB2 V8.1.5 / Windows 2000 Server

The SQL-Statement :
Select * from PROF.WH_EINZELPROFIL
where lagerstrangid in
(select lagerstrangid from prof.wh_kr_l20_ag where
AGAKTUELL in(3190,3199) AND ZIELNAME NOT IN ('PBF','WBF','ASGF','APMA'))
FOR UPDATE WITH RS

I some situations the result-set of the internal Select-Statement (select
lagerstrangid ...) is empty and in order to that also the result-set of the whole sql-statement is empty. But the sql-statement locks all records in the table WH_Einzelprofil with an Update-Lock. I can't understand that behavior ?

The relation WH_Einzelprofil is keyed by an EinzelprofilID, but have also an index on LagerstrangID.

Can anybody help me ?

Michael

If the subselect does a tablespace scan it locks all the rows. You could try
CS isolation level, instead of RS. There is also a registry (db2set) setting
to only lock the rows that qualify in the predicate, but I don't remember
what it is.