17.10 - Lock Modes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
You can specify the following lock modes to a database object:
  • Access
  • Read
  • Write
  • Exclusive

Lock-Mode Contention

The lock-mode contention among all outstanding lock requests determines which lock requests can be granted concurrently against a database object. This is illustrated by the contention matrix shown below.

Lock Mode Implicit Access Implicit Read Implicit Write Implicit Exclusive Access Read Write Exclusive
Implicit Access X X X X X X X  
Implicit Read X X X X X X    
Implicit Write X X X X X      
Implicit Exclusive X X X X        
Access X X X   X X X  
Read X X     X X    
Write X       X      

Explicit and Implicit Lock Modes

Explicit lock modes are the actual locks that secure access to a database object. Usually, they are acquired at the onset of the actual transaction processing.

Implicit lock modes are pre-emptive locks used by the Teradata Lock Manager in the process of acquiring an explicit lock. Use of pre-emptive locks suggests that an explicit lock is not acquired in a single step but rather in a sequence of steps.

For example, consider acquiring an explicit table-level lock. Prior to acquiring the explicit table-level lock, an implicit database-level lock and an implicit table-level lock must be acquired first.

The lock mode is noted in the mode field of the Lock Display output. An implicit lock is identified by the appearance of an asterisk (*). An explicit lock is implied when an asterisk is absent.

Lock Request Status

A lock request against a database object can be granted or blocked depending on the following:
  • The lock mode contention of all outstanding lock requests
  • The success of acquiring all locks implied by the lock granularity of the request

Lock Display output shows separate sections for Granted and Blocked lock requests. In the case of a blocked request, the level of the database object is shown by marking the associated field in the output with the hash symbol (#). For example, if the blocked request involves a table, then the table field is marked with the # symbol.