Lock Manager | Transaction Processing | Teradata Vantage - Lock Manager - Analytics Database - Teradata Vantage

Any number of users and applications can simultaneously access data stored in a database.

The Lock Manager imposes concurrency control on Vantage by managing the locks on the database objects being accessed by each transaction and releasing those locks when the transaction commits or rolls back its work. This control ensures that the data remains consistent for all users. With the exceptions of global deadlock prevention and detection, locks are not managed globally, but locally by each individual AMP.

For information about global deadlock detection, see Deadlock.

While the Parser defines the locks for a request automatically, you can upgrade, and may be able to downgrade locks explicitly by using the SQL LOCKING request modifier. See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

Locking Considerations

Vantage always makes an effort to lock database objects at the least restrictive level and severity possible to ensure database integrity while at the same time maintaining maximum concurrency.

Users have no control over when locks are released because of the way two-phase locking works (see Database Locks, Two-Phase Locking, and Serializability).

In each case, the request is queued until the conflicting lock is released.

Lock Manager resources can be exhausted. The Transaction Manager aborts any transaction that requests a lock when Lock Manager resources are exhausted. In such cases, you can disable rowhash-level locking for DDL requests. There is also a per AMP limit on the number of locks that can be placed at a time.

If an application begins a transaction and performs a large number of single-row updates without closing that transaction, at least one AMP lock table fills to its maximum capacity. This typically occurs for transactions in ANSI session mode.

Such an AMP lock table overflow also affects other transactions because no request can begin without first acquiring one or more locks on its underlying database objects. After a while, transactions that are unable to acquire any locks fail, and can even cause the system to stop responding.

You can control the maximum number of rowhash-level locks that the Lock Manager allows for each transaction using the DBS Control field MaxRowHashBlocksPercent (for more information, see Teradata Vantage™ - Database Utilities, B035-1102).

The default for this field is 50 percent of the total number of control blocks that an AMP lock table can support. You can set the value for MaxRowHashBlocksPercent to up to 100% to accommodate the number of rowhash-level locks that your system workloads require.

Vantage automatically ends a transaction when the number of rowhash-level locks the transaction acquires exceeds the threshold defined by MaxRowHashBlocksPercent. No other transactions are affected when this occurs. This control over the number of rowhash-level locks held by a transaction applies only to ANSI session mode and explicit Teradata session mode transactions. Implicit Teradata mode transactions are not affected because each implicit Teradata mode request is a transaction in itself, and the system releases its locks immediately after committing or rolling back the transaction.

You can review all the active locks and determine which other user locks are blocking your transactions using the Lock Display (lokdisp) utility (see Teradata Vantage™ - Database Utilities, B035-1102) or the Lock Viewer Viewpoint portlet.

Placed Locks Are Not Released until a Transaction Completes

ABORT/ROLLBACK statements, asynchronous aborts, failures, time outs, log offs, and system restarts can cause a transaction to abort.

This lock release occurs regardless of when you receive the response to a request because the spool may exist after the end of the transaction. Each of these actions also drops the Transient Journal and closes any open cursors.

During system restart, only update transactions that were in progress when the system stopped responding need to be ended and rolled back. Until those transactions are rolled back, their WRITE and EXCLUSIVE locks remain in place.

Using Multistatement Requests to Minimize How Long Locks Are Held

When possible, group individual requests that access the same set of tables or views together using multistatement requests. For example, suppose you want to insert a row into a table, update another row in the same table, and then delete a third row from that table.

You can do this using the following individual requests that insert a row into cust_rate, update an existing row in cust_rate, delete a third row from cust_rate, run a macro that does something to cust_rate, and then update the cust_rate row that was just touched by the init_cust macro:

INSERT INTO cust_rate
VALUES (123, “GOOD’);

UPDATE cust_rate
SET cust_rating=’FAIR’
WHERE cust_id=456;

DELETE FROM cust_rate
WHERE cust_id=789;

EXECUTE init_cust (9999);

UPDATE cust_rate
SET cust_rating=’FAIR’
WHERE cust_id=9999;

In this case, you are submitting only one SQL statement per request, which is inefficient for minimizing request blocking.

If you submit a multistatement request that runs the identical SQL, Vantage can impose the same row-hash-level WRITE lock on cust_rate twice, freeing the table for faster access by other concurrently running transactions. The multistatement requests used to do that are optimally sequenced in the following order:

INSERT INTO cust_rate
VALUES (123, ‘GOOD’)
; UPDATE cust_rate
  SET cust_rating=’FAIR’
  WHERE cust_id=456
; DELETE FROM cust_rate
  WHERE cust_id=789;

  EXECUTE init_cust (9999)
; UPDATE cust_rate
  SET cust_rating=’FAIR’
  WHERE cust_id=9999;

BEGIN TRANSACTION
; INSERT INTO cust_rate
  VALUES (123, ‘GOOD’)
; UPDATE cust_rate
  SET cust_rating=’FAIR’
  WHERE cust_id=456
; DELETE FROM cust_rate
  WHERE cust_id=789;

  EXECUTE init_cust (9999)
; UPDATE cust_rate
  SET cust_rating=’FAIR’
  WHERE cust_id=9999;
; END TRANSACTION;