Browse Prior Art Database

Support of Optional FOR UPDATE Of Clause in SQL

IP.com Disclosure Number: IPCOM000108319D
Original Publication Date: 1992-May-01
Included in the Prior Art Database: 2005-Mar-22
Document File: 3 page(s) / 140K

Publishing Venue

IBM

Related People

Boykin, JR: AUTHOR [+2]

Abstract

Disclosed is a method for supporting an optional FOR UPDATE OF clause in an SQL implementation.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 46% of the total text.

Support of Optional FOR UPDATE Of Clause in SQL

       Disclosed is a method for supporting an optional FOR
UPDATE OF clause in an SQL implementation.

      The OS/2* Extended Services Database Manager supports MIA
(Multi- vendor Integration Architecture) compliance. One of the
requirements of MIA is the ability to allow updates through a cursor
which was not defined with a FOR UPDATE OF clause.

      In order to prevent fetching and updating a row more than once,
the FOR UPDATE OF clause was invented to inform a database management
system's optimizer which indexes to avoid.  Previous releases of
Database Manager required that the FOR UPDATE OF clause be specified
in order for a column in the result table to be updated in a
subsequent positioned UPDATE statement.  This clause identifies the
columns allowed to be later updated so that the optimizer knows which
indexes to avoid choosing for data access.

      For example:
          Given a table with an index defined as follows:
              CREATE TABLE t (a INT)
              CREATE INDEX t ON t (a)
          Given a cursor declaration and positioned update as
follows:
              DECLARE c1 CURSOR FOR SELECT a FROM t WHERE a >= 2
              UPDATE t SET a = a + 2 WHERE CURRENT OF c1
           If the table is populated with values 1, 2, and 3, and the
result table defined by the cursor is being satisfied though an index
scan (in order to utilize the predicate a >= 2), a positioned update
against that cursor could potentially update a row of the table more
than once.  Scenario is as follows:
              OPEN cursor c1
              FETCH first row of c1 returns the value 2
              UPDATE t SET a = a + 2 WHERE CURRENT OF c1
                      sets the value of a to 4 and moves the row in
the index to maintain ordering
              FETCH second row of c1 returns the value 3
              FETCH third row of c1 returns the value 4 which was
just updated through the above positioned update. This row can now be
updated again.

      In this example, the index is not a viable choice for
retrieving the data as it does not guarantee each row of the cursor
will be FETCHed only once.  Embellishing the cursor declaration with
a FOR UPDATE OF clause specifying the column 'a' would prevent the
index from being the chosen access path.  However, an alternative
method is needed in order to satisfy MIA requirements.

      Other IBM relational database products provide some support for
positioned updates on cursors with no FOR UPDATE OF clause.  However,
this support is restricted to handling only static SQL cursors with
static SQL positioned UPDATE statements.  This is because when both
the cursor definition and the UPDATE is static, determination of
exactly which columns will be updated is po...