Rules for Cursor Locking
- All actions involving a cursor must be done within a single transaction.
- Terminating a transaction closes any open cursors.
Locking Levels and Positioned Cursors
A SELECT request performed when its associated cursor is opened causes Vantage to use either a table-level or rowhash-level lock by default, depending on the constraint clause of the SELECT request.
You can explicitly change this locking level by using the LOCKING request modifier. The LOCKING request modifier is supported for updatable cursors, and the CHECKSUM locking severity is designed especially for use with LOCKING.
Interactions between Cursors and Locks
When opening a cursor, Vantage generates a response spool. This spool identifies each data row that is a source for the response data in the spool row.
Vantage uses this identifier to UPDATE or DELETE the data row when the application specifies such an action against WHERE CURRENT OF cursor_name.
| Locking Modifier | Action |
|---|---|
| ACCESS | Vantage does not check that the data row to be updated or deleted has not been modified since the response data was generated for the spool. The target data row may be a new row if another application deleted the original source row and inserted a new row in its place. |
| CHECKSUM | Vantage inserts a checksum into each row of the spool. This provides a mechanism for ensuring that the rows in the spool have not been modified by another user or session at the time an update is being made through the cursor. The CHECKSUM option may not detect all conflicts. A row created by update may have an identical checksum to the original, unmodified row. The CHECKSUM option uses ACCESS severity locks. CHECKSUM locking also provides the checksums of the spool rows. |
Simple Example of Cursor Locking
This example uses LOCKING with CHECKSUM on table_1.
EXEC SQL REPLACE macro macro_2 AS (LOCKING TABLE table_1 FOR CHECKSUM SELECT i, text FROM table_1);
See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148 for more complex examples of locking and cursors.