Cursors use memory and require locks on tables, data pages, and index pages. When you open a cursor, memory is allocated to the cursor and to store the query plan that is generated. While the cursor is open, Adaptive Server holds intent table locks and sometimes row or page locks. Figure 8-3 shows the duration of locks during cursor operations.
Figure 8-3: Resource use by cursor statement
The memory resource descriptions in Figure 8-3 and Table 8-1 refer to ad hoc cursors for queries sent by isql or Client-Library™. For other kinds of cursors, the locks are the same, but the memory allocation and deallocation differ somewhat depending on the type of cursor being used, as described in “Memory use and execute cursors”.
Cursor command |
Resource use |
---|---|
declare cursor |
When a cursor is declared, Adaptive Server uses only enough memory to store the query text. |
open |
When a cursor is opened, Adaptive Server allocates memory to the cursor and to store the query plan that is generated. The server optimizes the query, traverses indexes, and sets up memory variables. The server does not access rows yet, unless it needs to build worktables. However, it does set up the required table-level locks (intent locks). Row and page locking behavior depends on the isolation level, server configuration, and query type. See “How isolation levels affect locking” on page 19 in Performance and Tuning Series: Locking and Concurrency Control for more information. |
fetch |
When a fetch is executed, Adaptive Server gets the row(s) required and reads specified values into the cursor variables or sends the row to the client. If the cursor needs to hold lock on rows or pages, the locks are held until a fetch moves the cursor off the row or page or until the cursor is closed. The lock is either a shared or an update lock, depending on how the cursor is written. |
close |
When a cursor is closed, Adaptive Server releases the locks and some of the memory allocation. You can open the cursor again, if necessary. |
deallocate cursor |
When a cursor is deallocated, Adaptive Server releases the rest of the memory resources used by the cursor. To reuse the cursor, declare it again. |