16.10 - SERIALIZE ON - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
16.10
Published
July 2017
Content Type
Programming Reference
Publication ID
B035-2436-077K
Language
English (United States)

If the Serialize option is set to ON, operations on a row occur serially in the order submitted.

The sequencing key of SERIALIZE ON is specified as one or more column names occurring in the input data SCHEMA definition. These SCHEMA columns are collectively referred to as the key. Usually the key is the primary index of the table being updated, but it can be a different column or set of columns. For example:

APPLY
      ('UPDATE emp  SET dept_name = :dept_name
      WHERE empno = :empno;')
      SERIALIZE ON (empno)
TO TARGET_TABLE[1]

This APPLY statement guarantees that all data rows with the same key (empno) are applied to the database in the same order received they are received from the producer operator. In this case, the column empno is the primary index of the Emp table.

Note that SERIALIZE ON is local to a specific DML statement. In the following example, a group DML is specified, but only the first statement uses the Serialize option:

APPLY
      ('UPDATE emp SET dept_num = :dept_num
          WHERE empno = :empno; ')
          SERIALIZE ON (empno)
      ('UPDATE dept SET dept_name = :dept_name
          WHERE deptno = :deptno; ')
TO TARGET_TABLE[1]

Following are some of the advantages to using the Serialize option, and might improve performance:

  • SERIALIZE ON can eliminate the lock delays or potential deadlocks caused by primary index collisions coming from multiple sessions.
  • SERIALIZE ON can also reduce deadlocks when rows with non-unique primary index values are processed.