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 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 | 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 | 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 | 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. Multiple rows may have the same rowhash.
|
| RowHash in a PartitionRange | 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. 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 | 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. Multiple rows in a partition can have the same rowhash.
|
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 LOCKING Request Modifier). |
| 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 LOCKING Request Modifier). |
| READ | A READ lock is placed in response to a SELECT request and restricts access by users who require EXCLUSIVE or WRITE locks. Multiple users can hold READ locks on a resource, during which the system permits no modification of that resource. READ locks make sure of 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 LOCKING Request Modifier). |
| 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 LOCKING Request Modifier) when using cursors in embedded SQL. CHECKSUM locking is identical to ACCESS locking except for adding 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 and Positioned Cursors. |
| ACCESS | Placed in response to a user-defined LOCKING FOR ACCESS modifier (see LOCKING Request Modifier), or by setting the session default isolation level to READ UNCOMMITTED using the SET SESSION TRANSACTION ISOLATION LEVEL statement. Permits a user to have a form of read access to an object that may already be locked for READ or WRITE. An ACCESS lock does not restrict access by another user except when an EXCLUSIVE lock is required, and is therefore called a dirty READ lock. 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 may be undergoing updates while the user who requested the access is reading. Therefore, any query that requests an ACCESS lock may return incorrect or inconsistent results. |
| 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 held. |
lock(object, lock requested) |
A database object-locking severity requested pair. |
Each database object has an associated locking queue Q and list of held locks L. All requests perform a locking operation before accessing any database objects.
| IF lock(object, lock_requested) | Action |
|---|---|
| Queued for any lock in L | 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 stops 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 stops if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction stops if blocked, instead of queueing. |
| WRITE | Lock Granted | Lock Granted | Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction stops if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction stops if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction stops if blocked, instead of queueing. |
| EXCLUSIVE | Lock Granted | Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction stops if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction stops if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction stops if blocked, instead of queueing. |
Request Queued If you specify a LOCKING FOR NOWAIT request modifier, the transaction stops 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 makes sure 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.
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 LOCKING Request Modifier.
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.