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 it opens 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.
| IF the locking modifier is … | THEN Vantage … |
|---|---|
| ACCESS | does not check to ensure 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 could be a completely new row if some other application has deleted the original source row and inserted a new row in its place. |
| CHECKSUM | 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. Note that the CHECKSUM option does not guarantee that all conflicts will be detected. There is a small, but finite, possibility that a row created by update might have an identical checksum to the original, unmodified row. The CHECKSUM option uses ACCESS severity locks. CHECKSUM locking differs in that it 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.