What is PUNC test in lock avoidance?

Ramachandran Subramanian

What is PUNC test in lock avoidance?
I understand that DB2 does a LRSN test to try lock avoidance and then if
that test fails , it does a PUNC test. If that test also fails it goes to
the IRLM.

I kind of understand the LRSN test, or so I think . I have no clue about
how PUNC test works.

Can some one explain to me how the lock avoidance mechanisim works?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Turner

Re: What is PUNC test in lock avoidance?
(in response to Ramachandran Subramanian)
Below are some definitions and a description of lock avoidance taken from my
teaching notes. Page locking is assumed, but the logic is similar for row
locking.

C_LSN:

A pageset Commit Log Sequence Number (C_LSN) is maintained by DB2 for each
tablespace or partition.

The pageset C_LSN is the Log RBA (or LRSN for Data Sharing) of the first
change to the tablespace or partition made by an uncommitted Unit of
Recovery (UR).

If the page Log_RBA of the last update to the page is < the C_LSN for the
pageset, then all data in the page is committed.

When the C_LSN check fails, then it is possible that uncommitted data exists
on the page. PUNC checking is then required.

PUNC:

A bit flag in each row’s record header, or in each RID in a Type 2 index
leaf page, signals Possible Uncommit (PUNC).

The flag is set when the row is modified.

It is reset by any process that happens to scan the row or RID if the C_LSN
check has proved that all data on the page is committed, or if DB2 acquires
a lock that proves all updates are committed.

It is NOT reset by a Commit, which is why it is only a Possible Uncommit
indicator.

For a data page, the page header keeps count of how many rows have the PUNC
bit set. If this number exceeds some internal limit, then all PUNC bits will
be reset.

Lock Avoidance:

There are three types of SQL processing to be considered:
· Repeatable Read.
· Cursor Stability Type 1.
· Cursor Stability Type 2.

Cursor Stability Type 2 processes are processes with CURRENTDATA(NO) which
are also read-only.

A process is read-only if:
· Its cursor specifies FOR FETCH ONLY (explicit read-only), or
· It includes a Join, ORDER BY, GROUP BY, DISTINCT, UNION, or a column
function (implicit read-only).

Cursor Stability Type 1 processes are any Cursor Stability processes which
are not Type 2.

Repeatable Read processes work the same as always. IRLM page S-locks are
acquired as pages are read and are not released until Commit.

For CS Type 1 processes, an S-latch is taken on the current page. C_LSN is
checked against the page Log RBA to see if any uncommitted data may be held
on the page. If uncommitted data is possible, then PUNC bit checking will be
performed on each row (data) or RID (index) as it is scanned. If C_LSN
indicates all data committed, then no PUNC bit checking is done. The records
on the page are scanned until one is found that satisfies the query
predicates (a qualifying row). At this point the S-latch is released and a
page S-lock is requested. Once the S-lock is acquired, the qualifying row is
returned. The S-latch is reacquired and the C_LSN is checked again when the
scan continues. If no qualifying row is found, the S latch persists until
all rows in the page have been scanned.

If the C_LSN test fails, then the page may contain uncommitted data. PUNC
bit checking is performed. If any row or RID (qualifying or not) is found
with the PUNC bit set (indicating possibly uncommitted data), then a page
S-lock is requested. Once a page S-lock has been acquired, it persists until
all rows on the page have been scanned. The PUNC bit is also turned off for
the row that triggered the lock.

The records that are scanned will be rows for a data page and index key
entries for an index leaf page. If an index-only scan is being performed, an
S-lock is still acquired when a qualifying key entry is found, or if a PUNC
bit is set when the C_LSN test has failed. The S-lock is taken on the
underlying data page or row pointed to by the RID(s) in the key entry.

CS Type 2 processing is similar to Type 1, except that an S-lock is not
requested when a qualifying row is found, only when the C_LSN test has
failed and a PUNC bit is set. The stability of qualifying rows in the page
is NOT guaranteed. They may be changed as soon as they have been returned to
the application.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk

-----Original Message-----
From: [login to unmask email]
[mailto:[login to unmask email]On Behalf Of Ramachandran
Subramanian
Sent: Tuesday, 21 December 2004 07:32
To: [login to unmask email]
Subject: What is PUNC test in lock avoidance?


I understand that DB2 does a LRSN test to try lock avoidance and then if
that test fails , it does a PUNC test. If that test also fails it goes to
the IRLM.

I kind of understand the LRSN test, or so I think . I have no clue about
how PUNC test works.

Can some one explain to me how the lock avoidance mechanisim works?

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

William Favero

Re: What is PUNC test in lock avoidance?
(in response to Mike Turner)
If a plan or package is bound with the options ISOLATION(CS) and CURRENTDATA(NO), and the SQL statement being executed is read only, DB2 will consider using lock avoidance. However, it can only use a
latch to read committed data. DB2 has two checks it uses to determine if the data row being read is committed. The first check is the Commit Log Sequence Number (CLSN) test. If the first check is unable to determine if the page is committed, a second check,
the Possibly UNCommitted (PUNC) test, is performed. If both checks are unsuccessful, DB2 must acquire a lock on the page before any rows can be accessed. The PUNC test is used to determine if the row
being read is a committed row. What happens in DB2 when a row is inserted or updated? The sixth bit (counting from zero) in the first byte, PGSFLAGS, of the six byte row prefix is set to on (or set to
one). This indicator bit is called the PUNC bit. DB2 will turn the bit off (set the bit back to zero) on a subsequent access of the row after the page has been committed. This is why it is referred to as the possibly uncommitted bit. The
page can be committed and still have the PUNC bit set to on. DB2 will test the PUNC bit of the row it is about to read. If the bit is still set off, DB2 knows that this row has not been changed since the last time this page was committed. DB2 can then assume this row is still
committed and allow the row to be read. If the bit is set on, then DB2 must acquire a lock to access this page. If an application has an X-lock on a page because data is being modified on that page, a second application can
still read the rows that have not been changed using a latch.


Willie



Ramachandran Subramanian wrote:

>I understand that DB2 does a LRSN test to try lock avoidance and then if
>that test fails , it does a PUNC test. If that test also fails it goes to
>the IRLM.
>
> I kind of understand the LRSN test, or so I think . I have no clue about
>how PUNC test works.
>
> Can some one explain to me how the lock avoidance mechanisim works?
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>
>
>

--
Willie
http://www.Red-Corvettes.com
62 Maroon, 98 Carmine, 99 MagRed (MTI 422), 03 Anniversary
I just love dark red.. LOL


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm