Cursor Locking Modes | Transaction Processing | Teradata Vantage - 17.10 - Cursor Locking Modes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Rules for Cursor Locking

Positioned cursors do not support specific locking levels; however, they expect the following rules to be observed:
  • 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.