[DB2-L] DB2 zOS - Baffled by an Update Lock

Walter Janißen

[DB2-L] DB2 zOS - Baffled by an Update Lock
Hi Adam

My understanding always was, that DB2 first acquires an U-lock to read the data and an X-lock, when it tries to update that data. So there seems to be other packages, which hold at least U-locks on that table.

Mit freundlichen Grüßen
Walter

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996


-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Adam Baldwin
Gesendet: Dienstag, 9. Februar 2010 12:43
An: [login to unmask email]
Betreff: [DB2-L] DB2 zOS - Baffled by an Update Lock

Thanks in advance - especially as I'm sure that I'm missing something obvious.

I have a package that performs an Index Only update of a table. The access path is via both columns of the 2 column non-unique clustering index. (I'm not sure why the index is non-unique as there is only ever one row for each index
key.)

I am seeing a lot of lock waits in this particular package and the wait is always for a "U" Lock at data page level.

Given that any page identified by the index is definitely going to be updated, I don't understand why the lock being requested isn't an "X" Lock. Why go for a "U" Lock first?

If the access wasn't index only I would expect to see "U" Locks being requested/taken.

The tablespace has locksize ANY and the package is bound with CS.

And this is a V7 sub system.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
** If you are going to attend only one conference this year, this is it!

_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
** If you are going to attend only one conference this year, this is it!

_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Adam Baldwin

Re: AW: [DB2-L] DB2 zOS - Baffled by an Update Lock
(in response to Walter Janißen)
Hi Walter. In the case of an index only update, the data page to be updated
has already been fully qualified / identified from the index. The only touch on
the data page should, therefore, be the actual update with an X lock.

A U lock on a page that may or may not be updated buys concurrency with
readers. A U lock on a page that is definitely going to be updated doesn't
seems to gain anything. That's why I'm puzzled.

Cheers, Adam

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
** If you are going to attend only one conference this year, this is it!

_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L