Why Default to ACCESS Locks For All SELECT Operations Embedded Within DELETE, INSERT, and UPDATE Requests in a Session? - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Analytics Database
Teradata Vantage
Release Number
June 2022
English (United States)
Last Update
Product Category
Teradata Vantage™

Unlike business-critical applications such as financial, order entry, warehouse management, and others that require absolute certainty in the results of their read-only operations on tables, some applications, such as Customer Relationship Management (CRM), sometimes do not require the same level of absolute certainty in the data they retrieve from the database because they are interested only in a snapshot, “statistical” picture of the current state of the database.

Because of this, CRM and other similar applications can tolerate dirty read operations, so they can afford to relinquish a degree of accuracy in their database read operations in exchange for enhanced transaction concurrency (see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 for details).

A dirty read is an operation that might read data written to the database by transactions that are not yet committed. There is always a chance that any transaction will fail for some reason, and if this occurs, the system rolls back any updates it might have made. If those updates include data that has been read by another concurrently running transaction, then that read is said to have been dirty, because as far as the database is concerned, it never really existed. See Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 for details about transaction integrity and some of the problems that relaxing it can bring.

It can be beneficial to the performance of applications that can tolerate dirty reads to be able to specify a default session-wide locking level that permits them rather than downgrading locks on a request-by-request basis, which can be burdensome. ACCESS-level locking achieves better performance than READ-level locking by enhancing transaction concurrency.

This is a very important consideration, and it should not be taken lightly. The overall qualitative workload of the session must be examined carefully before making the determination of whether to default to ACCESS-level locking for read-only operations or not. For example, consider a session in which a MultiLoad import job is running. Because of the way MultiLoad updates table rows during its acquisition phase (see Teradata® MultiLoad Reference, B035-2409), using ACCESS locks to query the target table of the MultiLoad job during an acquisition phase can produce extremely inaccurate result sets. In this case, the results probably would not provide even a reasonable impression of the table data.

The SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL statement provides a mechanism for doing that, allowing you to indirectly substitute ACCESS locks for READ locks for selected read-only SELECT operations in the current session (restricted to SELECT subqueries embedded within DELETE, INSERT, or UPDATE requests). The initial default for any session remains SERIALIZABLE (meaning READ level severity locking), but you can toggle the default read-only locking severity to READ UNCOMMITTED (meaning ACCESS level severity locking) either interactively or, more persistently, within your application code.