Database locks have two dimensions: level and severity. The level of a lock refers to its scope or granularity: the type and, by inference, the size of the object locked. For example, a database lock is a higher, less finely grained level lock than a rowkey-level lock.
The selection of lock granularity is always a trade-off between the conflicting demands of concurrency and overhead. Concurrency increases as the choice of locking level becomes increasingly granular. Exerting a rowhash- or rowkey-level lock permits more users to access a given table than exerting a table-level lock on the same table. This is why Vantage provides multiple levels of locking granularity.
The severity of a lock refers to its degree of restrictiveness or exclusivity, such as a WRITE lock being more restrictive than an ACCESS lock, or an EXCLUSIVE lock being more restrictive than a READ lock. See also Default Lock Assignments and Lock Upgradeability.
Locking Levels
- Database
- Table
See Proxy Locks for a description of a special category of table-level locking.
- View
- Partition
- RowHash
- RowKey (Partition and RowHash)
| Lock Level | What is Locked |
|---|---|
| Database | all rows of all tables in the specified database and their associated secondary index subtables. |
| Table | all rows in the specified base table and in any secondary index and fallback subtables associated with it. |
| View | all underlying tables accessed by the specified view. |
| Partition | the primary and fallback copy of
rows in a partition for the specified table or single-table view.
The table must be row partitioned. This lock permits other users to access the data in the table that are not in the same partition. |
| PartitionRange | the primary and fallback copy of
rows in a range of partitions for the specified table or
single-table view. The table must be row partitioned. This lock permits other users to access the data in the table that are outside the specified partition range. |
| RowHash | the specified primary or fallback copy of rows sharing the same row hash value for the specified table or single-table view. For a row-partitioned table, this lock level applies to the row hash value for all partitions. The rowhash-level lock permits other users to access the data in the table that do not have the same rowhash. The rowhash-level lock applies to a set of rows that shares the same hash code. This lock does not necessarily lock only a single row, because multiple rows may have the same rowhash.
|
| RowHash in a PartitionRange | the specified primary or fallback copy of rows sharing the same row hash value for the specified table or single-table view in a range of partitions. The table must be row partitioned. This lock permits other users to access other data in the table that do not have the same rowhash or are outside the specified partition range. The rowhash-level lock applies to a set of rows that shares the same hash code. This lock does not necessarily lock only one row since multiple rows may have the same rowhash in the same partition or in more than one partition in the partition range. This lock level is not used on rows in a USI or NUSI index subtable, as these subtables are never partitioned. |
| RowKey | the specified primary or fallback copy of rows sharing the same rowkey (partition and row hash value) for the specified table or single-table view. The table must be row partitioned. A rowkey-level lock permits other users to access other data in the table that do not have the same rowhash or partition value. A rowkey-level lock applies to a set of rows that shares the same partition and rowhash. This lock does not necessarily lock only one row since there could be multiple rows with the same rowhash in a partition.
|
The locking level determines whether other users can access the target object.
Locking severities and locking levels combine to exert locking granularities. The less granular the combination, the greater the impact on concurrency and system performance, and the greater the delay in processing time.
Locking Severity
The available lock severities, from most restrictive to least restrictive, are described in the following table.
| Lock Severity | Description |
|---|---|
| Most Restrictive | |
| EXCLUSIVE | EXCLUSIVE locks are placed only on a database or table when the object is undergoing structural changes (for example, a column is being created or dropped). You can also place an EXCLUSIVE lock explicitly using the LOCKING request modifier (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146). |
| WRITE | Placed in response to an INSERT, UPDATE, or DELETE request. A WRITE lock restricts access by other users (except for applications that are not concerned with data consistency and choose to override the automatically applied WRITE lock by specifying a less restrictive ACCESS lock). You can also place this lock explicitly using the LOCKING request modifier (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146). |
| READ | A READ lock is placed in response to a SELECT request and restricts access by users who require EXCLUSIVE or WRITE locks. Several users can hold READ locks on a resource, during which the system permits no modification of that resource. READ locks ensure consistency during READ operations such as those that occur during a SELECT statement. You can also place the READ lock explicitly using the LOCKING request modifier (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146). |
| The CHECKSUM and ACCESS locking severities are all at the same level in the restrictiveness hierarchy. | |
| CHECKSUM | Placed in response to a user-defined LOCKING FOR CHECKSUM modifier (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146) when using cursors in embedded SQL. CHECKSUM locking is identical to ACCESS locking except that it adds checksums to the rows of a spool to allow a test of whether a row in the cursor has been modified by another user or session at the time an update is being made through the cursor. See also Cursor Locking Modes, Teradata Vantage™ - SQL Data Manipulation Language, B035-1146, and Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148. |
| ACCESS | Placed in response to a user-defined LOCKING FOR ACCESS modifier (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146), or by setting the session default isolation level to READ UNCOMMITTED using the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL statement (see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144). Permits a user to have a form of read access to an object that might already be locked for READ or WRITE. An ACCESS lock does not restrict access by another user except when an EXCLUSIVE lock is required; therefore it is sometimes referred to as a dirty READ lock. The global application of ACCESS locking for read operations when SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL is set to READ UNCOMMITED depends on the setting of the DBS Control field AccessLockForUncomRead. When the parameter is set FALSE, SELECT operations within INSERT, DELETE, MERGE, and UPDATE requests set READ locks, while when the parameter is set TRUE, the same SELECT operations set ACCESS locks. A user requesting an ACCESS lock disregards all data consistency issues. Because ACCESS and WRITE locks are compatible, the data might be undergoing updates while the user who requested the access is reading it. Therefore, any query that requests an ACCESS lock might return incorrect or inconsistent results. An ACCESS lock is also placed in response to a user-defined LOAD COMMITTED locking modifier (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146). For information about designating tables for ISOLATED LOADING, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. For a load-isolated table, this modifier allows users to read from committed rows in tables, even while the table is being loaded with data. If the table that is being read is not a load-isolated table, this severity results in an ACCESS lock. For information about load isolation, see Load Isolation. |
| Least Restrictive | |
Compatibility among Locking Severities
- ACCESS.
- CHECKSUM.
- READ.
- WRITE.
- EXCLUSIVE.
The following notation is used to describe the locking severity compatibilities.
| Notation | Definition |
|---|---|
| Q | A locking queue associated with a database object. |
| L | A list of locks currently held. |
| lock (object, lock_requested) | A database object-locking severity requested pair. |
Each database object has an associated locking queue Q and list of currently held locks L. All requests perform a locking operation before accessing any database objects.
| lock (object, lock_requested) | Result |
|---|---|
| Queued for any lock in L | The transaction is placed in Q and waits there while lock (object, lock_requested) is queued. A request in this state is said to be blocked (see Blocked Requests). |
| Granted | lock (object, lock_requested) is added to L with lock_requested and the transaction resumes processing. |
After the transaction finishes with an object by either committing or rolling back, its lock is removed from L.
The table on the following page summarizes the action taken when a requested locking severity competes with an existing locking severity.
| Severity of Requested Lock | Severity of Held Lock | ||||
|---|---|---|---|---|---|
| None | ACCESS CHECKSUM | READ | WRITE | EXCLUSIVE | |
| ACCESS CHECKSUM | Lock Granted | Lock Granted | Lock Granted | Lock Granted | Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction ends if blocked, instead of queueing. |
| READ | Lock Granted | Lock Granted | Lock Granted | Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction ends if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction ends if blocked, instead of queueing. |
| WRITE | Lock Granted | Lock Granted | Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction ends if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction ends if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction ends if blocked, instead of queueing. |
| EXCLUSIVE | Lock Granted | Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction ends if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction ends if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction ends if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction ends if blocked, instead of queueing. |
- Basic Teradata® Query Reference, B035-2414
- Teradata® FastExport Reference, B035-2410
- Teradata® FastLoad Reference, B035-2411
- Teradata® MultiLoad Reference, B035-2409
- Teradata® Parallel Data Pump Reference, B035-3021
- Teradata® Parallel Transporter Reference, B035-2436
A queued request is in an I/O wait state and is said to be blocked (see Blocked Requests).
A WRITE or EXCLUSIVE lock on a database, table, or view restricts all requests or transactions except the one holding the lock from accessing data within the domain of that object.
Because a lock on an entire database can restrict access to a large quantity of data, the Parser ensures that default database locks are applied at the lowest possible level and severity required to secure the integrity of the database while simultaneously maximizing concurrency.
Table-level WRITE locks on dictionary tables prevent contending tasks from accessing the dictionary, so the Parser attempts to lock dictionary tables at the rowhash or rowkey (partition and rowhash) level whenever possible.
For information about how load isolation affects compatibility among locking severities, see Load Isolation.
Using the NOWAIT Option for the SQL LOCKING Request Modifier
When you specify the NOWAIT option for the SQL LOCKING request modifier, Vantage aborts a transaction that makes a lock request that cannot be fulfilled immediately. For details on how to use the NOWAIT option with the LOCKING request modifier, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
Vantage uses a slight variation of this code internally to avoid blocking on DDL operations. Instead of aborting the request, the system instead downgrades rowhash lock severities from READ to ACCESS. See DDL and DCL Requests, Dictionary Access, and Locks.
AMP-Based Utilities and Logging
- Lock Viewer Viewpoint portlet
This portlet produces a report of miscellaneous database lock delay information that you can use to detect blocked transactions and global deadlocks. Vantage extracts the data reported by the Lock Viewer portlet from DBQL transaction logs.