00C90096 (max locks) z/OS V11

John Kliewe

00C90096 (max locks) z/OS V11

My tablespace is LOCKSIZE ANY and LOCKMAX SYSTEM

I am surprised that a dynamic "insert into" is causing a 00C90096.  I would have expected it to start with a page lock, then escalate to tablespace.  I understand that this can cause other failures, but I am surprised to see the 00C90096.  Am I wrong to believe that locksize ANY lockmax SYSTEM should never result in a 00c90096?

Brian Laube

RE: 00C90096 (max locks) z/OS V11
(in response to John Kliewe)

 

The reason code is saying that your single agent exceeded max NUMLKUS. 

 

Perhaps you have locks on multiple tablespaces or partitions… none individually escalated but all of them together exceeded NUMLKUS.  (I don’t know… just thinking out loud)

 

Regards,

Brian Laube

John Kliewe

RE: 00C90096 (max locks) z/OS V11
(in response to Brian Laube)

In this particular case, the failing SQL is :

INSERT INTO TABLE1 SELECT * FROM TABLE2

Both tables are in tablespaces with LOCKSIZE ANY LOCKMAX SYSTEM, and neither table has any RI or Triggers on it.

Manoj Jadwani

RE: 00C90096 (max locks) z/OS V11
(in response to John Kliewe)

How many records are being inserted ?
It could be because of number of records inserted are too high which is causing this max locks condition.

Regards,

Manoj K Jadwani

John Kliewe

RE: 00C90096 (max locks) z/OS V11
(in response to Manoj Jadwani)

The number of records does exceed the maximum, yes.  But I would have expected Db2 to escalate to a tablespace lock at that point, instead of failing with the c90096.

Manoj Jadwani

RE: 00C90096 (max locks) z/OS V11
(in response to John Kliewe)

What is your site's setting for NUMLKTS and NUMLKUS parameters ?

Regards,

Manoj K Jadwani

John Kliewe

RE: 00C90096 (max locks) z/OS V11
(in response to Manoj Jadwani)

TS is 5000
US is 50000

Manoj Jadwani

RE: 00C90096 (max locks) z/OS V11
(in response to John Kliewe)

Found this old thread discussed .This could be relevant .
https://www.idug.org/p/fo/st/post=166499#p166499

Regards,

Manoj K Jadwani

Michael Hannan

RE: 00C90096 (max locks) z/OS V11
(in response to John Kliewe)

In Reply to John Kliewe:

In this particular case, the failing SQL is :

INSERT INTO TABLE1 SELECT * FROM TABLE2

Both tables are in tablespaces with LOCKSIZE ANY LOCKMAX SYSTEM, and neither table has any RI or Triggers on it.

I am a bit puzzled too if your max user locks is ten times the TS locks. Outside of the failing SQL, what other Inserts/Updates/Deletes happened in the same UOW, since the X page locks from changed pages are retained till Commit? (See Brian Laube's comment).

Once Max User locks is hit, that is a fail rather than escalate further (a little difficult for IBM to implement any other approach).

TABLE2 should not have a lot of locks, unless package bound with Repeatable Read or something silly like that.

TABLE1 could have as many pages locked as rows inserted, if they are inserted in scattered cluster sequence. Inserts also experience conditional locks where the page attempted lock is not successful due to another process already locking it. In this case the Inserter looks for another nearby page that is not already locked. Failed conditional locks probably don't count towards the max lock thresholds I would hope. I have seen very heavy CPU for large numbers of failed conditional locks.


Do you have any monitoring tools where you can look at the maximum number of locks that got accumulated? Many sites do, or even SMF accounting data for thread might be able to help if the thread detail data has just this transaction unsummarized. 

If you don't mind locking up the whole table, LOCK TABLE before the Inserts might help. That will minimize count towards max User Locks. It is usually avoided due to lack of sharing TABLE1 with other processes for a long UOW.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 11, 2020 - 12:28 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 11, 2020 - 12:31 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 11, 2020 - 12:33 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 11, 2020 - 12:36 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 11, 2020 - 12:37 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 11, 2020 - 12:39 AM (Europe/Berlin)