Switch to standard view 
  Sybase logo
 
 
 



 
 
 
 
 
 
 
 
 
 
Cursor Update and Error 311
An Adaptive Server Enterprise White Paper

 
 
 
 
 
 
 
 
 
 
 

Jiwen Qiu
Three Sybase Drive
Dublin, CA 94568
email: jqiu@sybase.com
(925) 236-6223



 
 

Summary

This white paper is intended to address two common cursor-related questions, namely:

Error 311 reports "The optimizer could not find a unique index which it could use to scan table '%.*s' for cursor '%.*s' ." If the server raises Error 311 when doing a cursor update, this can be due to the following:

  1. The updated table does not have a unique index, or
  2. A cursor declared for update tries to update one or more columns that make up the index keys of a unique index.

These are not server issues but application problems.

Contents

    1. Basic Cursor Concepts
    2. Update/Delete Using a Cursor
    3. When is Error 311 raised?
    4. Is Error 311 a Server Bug?
    5. Examples of Cursor Update and the Halloween Problem
 
 

1. Basic Cursor Concepts

1.1 Cursor Definitions

Cursor: A cursor is a symbolic name associated with a SELECT statement that produces a set of rows. The cursor enables you to access the results of a SELECT statement one row at a time.

Cursor Result Set: All of the rows returned from executing a SELECT statement

Cursor Position: A pointer to one row within the cursor result set, which indicates the current row of the cursor in the result set.

Positioned UPDATE/DELETE: Update/delete the row at the current position of a cursor by using "WHERE CURRENT OF <cursor name>" in an UPDATE/DELETE statement.

1.2 Using the Cursor

Chapter 17 "Cursor: Accessing Data Row by Row" in the Transact-SQL User's Guide includes general information about cursors.

A cursor allows you to select, modify or delete row by row, or by groups of rows. A cursor is a symbolic name that is associated with a SELECT statement. It enables you to access the results of the SELECT statement one row at a time. Cursor usage is a set of operations, done in the following order:

   DECLARE <cursor-name> CURSOR FOR <select-statement> [FOR ...]

   OPEN <cursor-name>

   FETCH <cursor-name> [into fetch-target-list]

   UPDATE <table-name> SET ... WHERE CURRENT OF <cursor-name>
   DELETE <table-name> WHERE CURRENT OF <cursor-name>
      and/or cursor-result-handling

   CLOSE <cursor-name>

   DEALLOCATE CURSOR <cursor-name>

2. Update/Delete Using a Cursor

2.1 Updatable Cursor Declaration syntax

The syntax for an updatable cursor is as follows:

    DECLARE <cursor-name> CURSOR FOR
    SELECT-statement
    [ FOR UPDATE [ OF column-name-list ]]

For example:

   DECLARE recon5_cur CURSOR
   FOR
   SELECT a, b FROM ABCD
   FOR UPDATE OF a,b

If there is a column-list following "FOR UPDATE", the columns in the list are updatable.

If there is no column name list in the "FOR UPDATE" clause, the result set of the SELECT statement is updatable.

However, if you do not explicitly specify the cursor as read only or update, the cursor is implicitly updatable provided that the SELECT statement does not contain any of the following structures:

distinct option
group by option
Aggregate function
Subquery
union operator
at isolation read uncommitted clause

You cannot specify the for update clause if a cursor's SELECT statement contains one of the above structures. If the SELECT statement in a cursor declaration contains one of the structures above, the cursor is read-only.

2.2 Positioned UPDATE/DELETE

The  positioned UPDATE statement syntax is as follows:

   UPDATE <table name>
   SET <set clause: positioned> ...
   WHERE CURRENT OF <cursor name>

The  positioned DELETE statement syntax is as follows:

   DELETE FROM <table name>
   WHERE CURRENT OF <cursor name>

To use a cursor to update or delete a row, you must specify one of the three DECLARE CURSOR statements, and the cursor must be opened and considered updatable. Cursor update/delete will update/delete the row pointed to by the current cursor position. You must get the cursor position first,  then issue the update or delete. To do this, open the cursor and fetch a row first; then do the update or delete on the current cursor position.

After the delete is done, the server places the cursor at the next row. If you want to do a subsequent update or delete, you must first issue a FETCH.

After the update is done, the server keeps the cursor in the same position. A fetch returns the next qualifying position. You can then do the next update or delete.

2.3 Why does a "cursor update" need a unique index?

Adaptive Server performs the update with a cursor and you must get the qualifying row position first with a fetch. ASE attempts to use unique indexes for updateable cursors when scanning the base table. An index containing an IDENTITY column is considered unique.

ASE documentation clearly addresses the problem of an updated row moving: this is known as the "Halloween Problem," a problem with cursors that requires special logic on the part of Adaptive Server to accurately reposition the cursor after a row is updated and the row moves.  This logic is invoked automatically by the server and requires a unique index on the table.

A unique index used in the cursor scans for the positioned update or positioned delete will prevent update anomalies such as the Halloween Problem.

2.4 What is the Halloween Problem?

If cursor updates are allowed on a column or columns that make up the index, the cursor updates could change the columns in a clustered index or non-clustered index where the index row is updated so that the row moves forward. The row could then be picked up again by a subsequent cursor fetch. This is the Halloween Problem, and it can manifest itself in one of two ways:

Whether it is a clustered index or non-clustered index, it is the update to the index column that will cause the Halloween Problem.

2.5 What is a valid unique index for a cursor update?

A common error is to create a unique index on a table and then use cursors to do an update or delete on one or more columns in the index.  The server then raises error 311: "The optimizer could not find a unique index which it could use to scan table 'table-name' for cursor 'cursor-name'". You cannot use a column which must be updated in the unique index that you use to create the cursor ? attempting this could cause the Halloween Problem, and the server prevents you from doing so.

What is a valid unique index for a cursor update?

A valid unique index is one that cannot cause an update anomaly such as the Halloween Problem.  The cursor update cannot change the row position when the cursor fetch locates the row position with the unique index. For more information see Chapter 17 Cursor: Accessing Data Row by Row; subheading "Determining Which Columns can be Updated" in the Transact-SQL User's Guide

3. When is Error 311 raised?

Error 311 is as follows:

Msg 311, Level 16, State 1:
Server 'REPRO_SERVER', Line 4:
The optimizer could not find a unique index which it could use to scan table 'table-name' for cursor 'cursor-name'.

When will error 311 be raised?

For example, create a table ABCD including the columns of cola, colb, colc, cold and so on. There is a unique index abcdidx (cola, colb). A cursor is defined for update of cola, colb. The original values (1, 1) will be changed into (10, 10) that means the updated index row has been moved within the index. The row should move forward. The cursor fetch can read the row again and update it again. This situation must not be allowed. So the optimizer recognizes the invalid unique index, which contains those columns updated by a cursor and rejects it as a unique index in this case. The optimizer tries to find another valid one. The server will raise the error 311 when the optimizer cannot find a qualified unique index. The server ensures that an index whose key consists of the columns in the FOR UPDATE clause will not be used to search a base table.

In the above example there is a unique index in the table 'ABCD'. So why did the optimizer not find the index for a cursor update? Because the unique index is NOT a valid index in this context. If the cursor updates the columns that are the index keys of the unique index, the optimizer cannot use the index. Error 311 is raised when the optimizer can not find other unique indexes from your system.

In conclusion, what is the best way to avoid raising Error 311 for the cursor update? Use an index containing an IDENTITY column as the unique index.

4. Is Error 311 Due to a Server Bug?

The answer to this question is no - this is not a server bug or issue, rather it is a user issue. If you declare a cursor with FOR UPDATE of column-list, you must create a unique index whose keys do not appear in the column-list.

5. Examples of Cursor Update and the Halloween Problem

The following table is used in the examples:

1> CREATE TABLE Tb (a int, b int, c int)
2> go
1> INSERT Tb VALUES(1, 2, 3)
2> INSERT Tb VALUES(4, 5, 6)
3> INSERT Tb VALUES(7, 8, 9)
4> INSERT Tb VALUES(4, 5, 16)
4> go

5.1 Cursor Declaration

Next we declare a cursor without for update. By default the cursor is updatable There is no index on the table at this time.

1> DECLARE curs CURSOR FOR SELECT a, b FROM Tb
2> go
1> OPEN curs
2> go
1> UPDATE Tb
2> SET a = 10 WHERE CURRENT OF curs
3> go
Msg 560, Level 16, State 1:
Server 'REPRO_SERVER', Line 1:
The UPDATE/DELETE WHERE CURRENT OF failed for the cursor 'curs' because it is
not positioned on a row.

1> FETCH curs
2> go
 a           b
----------- -----------
         1           2

(1 row affected)

1> UPDATE Tb
2> SET a = 10 WHERE CURRENT OF curs
3> go
(1 row affected)

5.2 Cursor Declaration with "for update"

This cursor is declared with the for update option without a column-list. There is no index on the table at this time.

1> DECLARE curs CURSOR FOR SELECT a, b FROM Tb
2> FOR UPDATE
3> go
1> OPEN curs
2> go
Msg 311, Level 16, State 1:
Server 'REPRO_SERVER', Line 1:
The optimizer could not find a unique index which it could use to scan table
'Tb' for cursor 'curs'.

5.3 Cursor Declaration with "for update" and Column-list

1> DECLARE curs CURSOR FOR SELECT a, b FROM Tb
2> FOR UPDATE OF a
3> go
1> OPEN curs
2> go
Msg 311, Level 16, State 1:
Server 'REPRO_SERVER', Line 1:
The optimizer could not find a unique index which it could use to scan table
'Tb' for cursor 'curs'.

5.4  Halloween Problem

A Halloween problem occurs if a unique index is not valid: when a unique index is declared for (b, c) and a cursor is declared for update on (a, c)

1> SELECT * FROM Tb
2> go
 a           b           c
 ----------- ----------- -----------
          1           2           3
          4           5           6
          7           8           9
          4           5          16

(4 rows affected)

1> CREATE UNIQUE INDEX unidx ON Tb (b, c)   <-------- UNIQUE INDEX
2> go
1> DECLARE curs CURSOR FOR SELECT a, c FROM Tb   <--- cursor without column-list
2> FOR UPDATE
3> go
1> OPEN curs   <------- open without any error
2> go
1> UPDATE Tb
2> SET a = 22
3> WHERE CURRENT OF curs
4> go
Msg 560, Level 16, State 1:   <---- No fetch after open the cursor
Server 'REPRO_SERVER', Line 1:
The UPDATE/DELETE WHERE CURRENT OF failed for the cursor 'curs' because it is
not positioned on a row.

======> must fetch cursor first and then update one or both of a and c
1> FETCH curs
2> go
 a           c
 ----------- -----------
          1           3

(1 row affected)

1> UPDATE Tb   <-------- update the first row
2> SET a = 22
3> WHERE CURRENT OF curs
4> go
(1 row affected)

1> FETCH curs   <-------- get the second row
2> go
 a           c
 ----------- -----------
          4           6

(1 row affected)

1> UPDATE Tb   <-------- update the second row
2> SET a = 100, c = 200
3> WHERE CURRENT OF curs
4> go
(1 row affected)

1> FETCH curs    <------- get the third row
2> go
 a           c
 ----------- -----------
          4          16

(1 row affected)

1> FETCH curs   <-------- get the fourth row, this is actually the second row
2> go
 a           c
 ----------- -----------
        100         200

(1 row affected)

1> UPDATE Tb   <-------- to update the fourth row
2> SET a = 10000, c = 20000
3> WHERE CURRENT OF curs
4> go
(1 row affected)

1> FETCH curs   <-------- get the fifth row
2> go
 a           c
 ----------- -----------
           7           9

(1 row affected)

1> FETCH curs   <---------- end of fetching the cursor
2> go
 a           c
 ----------- -----------

(0 rows affected)

1> SELECT * FROM Tb
2> go
 a           b           c
 ----------- ----------- -----------
         22           2           3
      10000           5       20000
          7           8           9
          4           5          16

(4 rows affected)   <--------- There are 4 rows in the table. We got 5 rows with cursor FETCH.
1>

5.5 Avoiding the Halloween Problem

We avoid the Halloween problem with for update of column-list.

=====> Please compare:
1> DECLARE curs2 CURSOR FOR SELECT a, c FROM Tb   <------ cursor with column-list
2> FOR UPDATE OF a, c
3> go

1> OPEN curs2   <-------- open gets error 311, although UNIQUE INDEX (b, c)
2> go
Msg 311, Level 16, State 1:
Server 'REPRO_SERVER', Line 1:
The optimizer could not find a unique index which it could use to scan table
'Tb' for cursor 'curs2'.
1>

We have created the unique index unqdx on table Tb. But the server still raises Error 311 because the unique index with keys (b, c) is not a valid index for update of cursor curs2 on  columns a and c. c is a key column of that unique index.

Opening the cursor curs2 only works if the unique index is created on column b only.
 
 
 



Back to Top
© Copyright 2010, Sybase Inc.