0

Examining the behaviour of two transactions launched from two DbVisualizer instances pointing at an instance of DB2 database on z/OS, I have noticed the following behaviour during deletion of records from tables.

Suppose I have a table MYTABLE with primary key MYID, and suppose that executing

select MYID from MYTABLE

gives something like (numbers are arbitrary and are written down just make things concrete)

112
119
...
...
789
...

In trial A I execute, from the first transaction (using the first DBVisualizer instance) and without committing,

DELETE FROM MYTABLE WHERE MYID=112

Then I execute from the second transaction (using the second DBVisualizer instance)

DELETE FROM MYTABLE WHERE MYID=119

This however blocks the second transaction, and after some time produces the error

UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. 
REASON CODE 00C9008E, TYPE OF RESOURCE 00000302, AND...

In a similar trial, trial B, when I use MYIDs 112 and 789 instead (789 is "not near" 112), the second transaction does not block. Looking up the meaning of TYPE OF RESOURCE 00000302 one finds, on https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/codes/src/tpc/db2z_resourcetypes.html, "Table space page" (the link is for DB2 on z/OS).

So it looks like in trial A, the first DELETE "locked" some "page" to which both records with MYID 112 and 119 "belonged", and that this lock blocked then the second transaction. While in trial B the two records belong to different "pages" and the first DELETE does not block the second.

Taking a well known book on DB2 I read "Depending on the operations requested, the database manager can acquire locks on table rows, table blocks, tables, table spaces, buffer pools, and databases". It is then explained that there are various "locking modes".

My question is: does "locks on table blocks" in the citation above refers to the observed in trial A "lock on table space pages", or it is some other lock type not mentioned in the citation? And why at all the lock used is the "table space page lock" and not the row level lock which presumably would not cause the second transaction to block during trial A? (I read about lock escalation in DB2, but as far as I know there were no transactions going on at that time which involved MYTABLE)

The DB2 version involved is 10 in "compatibility mode" which downgrades it to something like DB2 version 8. I think that the configuration should otherwise be "default" or "standard".

(This question is a result of my effort to understand a problem described in an earlier question Can one delete statement which deletes multiple rows cause deadlock?)

EDIT

Just tried that on DB2 Express on my Windows notebook, and the behaviour I see is different; the locks are row locks just as I would expect (so the second DELETE blocks only if I try to delete the same row). So is this indeed about DB2 on z/OS? Or is it the DB2 old version? Or perhaps the observation hints at some special configuration of DB2?

Community
  • 1
  • 1
John Donn
  • 1,718
  • 2
  • 19
  • 45

1 Answers1

3

I believe the default setting for a DB2 z/OS tablespace is LOCKSIZE ANY, which effectively means LOCKSIZE PAGE, that is, the entire tablespace page is locked if any row on that page is updated. Documentation reference. This would explain the behaviour you're seeing.

Issue (or ask your DBA to issue) an ALTER TABLESPACE ... LOCKSIZE ROW statement for the tablespace holding the table in question. For DB2 on z/OS it is common practice to have one table per tablespace, so this change should not have much effect on other workloads (unless you manage to use up all lock memory in the database).

In DB2 for LUW only row- and table-level locks are possible, so that also explains the difference you're seeing with DB2 Express on Windows.

mustaccio
  • 18,234
  • 16
  • 48
  • 57