15.00 - Between-Transaction Integrity Issues - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Between‑Transaction Integrity Issues

By default, updatable cursors use READ locks, which are implemented as row hash locks internally. READ locks are adequate for preserving database integrity because they prevent access to the database by users who attempt to either change the definitions of database structures or to update table data. As a result, stored data cannot be changed while an open cursor is also manipulating the same data in such a way that it updates the same fields of the same rows. This is sometimes referred to as repeatable read mode. Programmers can also upgrade the locks used by updatable cursors to WRITE or even EXCLUSIVE locks if they so desire.

The potentially most severe problems for database integrity regarding updatable cursors are presented by the use of ACCESS or CHECKSUM locks, because both of these locks permit rows to be updated by other users while an open cursor is manipulating them, thus providing greater concurrency (note that CHECKSUM locks are ACCESS locks). The difference is that CHECKSUM locks compute a checksum value for updated rows and compare it with the checksum value computed for the row at the time the cursor accessed it, while ACCESS locks provide no such integrity check.

CHECKSUM locks provide better integrity than simple ACCESS locks because they compute a checksum value for each row updated by an open cursor (see the chapter “Transaction Processing” in SQL Request and Transaction Processing for details). When the current transaction commits and an updated row is written to disk, the checksum value for that row is compared with the value computed for the row at the time it was accessed and if the values differ, the update is not permitted.

There is a finite probability that pre- and post‑updated rows will produce an identical checksum value, so this option is only advisable for those situations where performance concerns significantly override integrity concerns. The most important fact to understand about the various locking options for positioned updates via updatable cursors is that there is no integrity risk with READ and more restrictive locks, but there is a finite integrity risk with CHECKSUM locks, and an almost certain integrity risk with ACCESS locks.