Using Locks - MultiLoad

Teradata MultiLoad Reference
Product
MultiLoad
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-07-11
dita:mapPath
cgb1488824663145.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities

Teradata MultiLoad uses two kinds of long-term locks on the tables involved in a Teradata MultiLoad task:

  • The set of transaction locks acquired against the tables involved in the tasks
  • Flags in the table headers that reject certain concurrent access

The locks placed on target tables by Teradata MultiLoad are write locks, or less, in most situations. The locking levels are imposed by the utility for:

  • Teradata MultiLoad import or delete tasks, including all Teradata MultiLoad utility commands except RELEASE MLOAD
  • End phase target table rebuilds in AMPs marked as down for Teradata MultiLoad tasks
  • Rebuild logic triggered by target table I/O errors

The most restrictive exclusive lock is used only when a RELEASE MLOAD statement is executed after a Teradata MultiLoad task has been suspended or aborted.

The following table describes the of locks imposed on tables by Teradata MultiLoad tasks.

Types of Table Locks Imposed by Teradata MultiLoad 
Lock Type Description
Access An access lock allows selection of data from a table that may be locked for write access.

Teradata MultiLoad maintains access locks against the target tables during the acquisition phase.

There is concurrent access lock select access to all affected target tables in both import and delete tasks.
Exclusive Teradata MultiLoad uses an exclusive lock to support the manual recovery procedure when a RELEASE MLOAD statement is executed after a Teradata MultiLoad task has been suspended or aborted.

The RELEASE MLOAD function:

  • Cannot run until all access locked queries are either completed or aborted
  • Does not complete until it obtains an exclusive lock on the target tables
Teradata MultiLoad Acquisition A Teradata MultiLoad acquisition lock is a flag in the table header that effectively rejects certain types of Teradata SQL access statements.

An acquisition lock:

  • Allows all concurrent DML access and the DROP DDL statement
  • Rejects DDL statements other than DROP

Teradata MultiLoad maintains acquisition locks against each:

  • Target table from the beginning of the preliminary phase through the sort at the end of the acquisition phase
  • Error table throughout the entire Teradata MultiLoad job
Teradata MultiLoad Application A Teradata MultiLoad application lock is a flag in the table header that effectively rejects certain types of Teradata SQL access statements.

An application lock:

  • Allows all concurrent access lock select access and the DROP DDL statement
  • Rejects all other DML and DDL statements

Teradata MultiLoad maintains application locks against each:

  • Target table through the application phase
  • Work table throughout the entire Teradata MultiLoad job
Write A write lock enables a single user to modify the table.

Teradata MultiLoad maintains write locks against each:

  • Target table during the application phase
  • Work tables and error tables for each task transaction
Use access lock select access when querying the Teradata MultiLoad error tables to prevent lock time-outs that could cause Teradata MultiLoad task aborts.