Partition level locking - Single vs All partitions

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

Partition level locking - Single vs All partitions

Hi,

 

I'm trying to make use of partition level locking to enable concurrent processing streams in our application.

The problem I'm facing is that MERGE and some INSERT FROM SELECT queries are locking All partitions instead of expected single partition. That unfortunately serialises the processes and what's more problematic - increases the possibility of a deadlock in our application.

 

e.g.

 

--table definition (the same for source and target)
CREATE MULTISET TABLE concurrency_test_partition
(
ID DECIMAL(10,0) NOT NULL,
BALANCE_DATE TIMESTAMP(6) NOT NULL,
DAILY_MOVEMENT DECIMAL(38,3) NOT NULL,
MTD_BALANCE DECIMAL(38,3) NOT NULL,
YTD_BALANCE DECIMAL(38,3) NOT NULL,
LTD_BALANCE DECIMAL(38,3) NOT NULL,
ENTITY VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
INPUT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
PRIMARY INDEX ( ID ,ENTITY )
PARTITION BY ( CASE_N(
ENTITY = 'ENTITY1',
ENTITY = 'ENTITY2',
ENTITY = 'ENTITY3',
ENTITY = 'ENTITY4',
ENTITY = 'ENTITY5',
NO CASE OR UNKNOWN),RANGE_N(CAST((BALANCE_DATE ) AS DATE AT TIME ZONE 0 ) BETWEEN DATE '2011-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' DAY ,
NO RANGE OR UNKNOWN));

--Statement
merge into concurrency_test_partition USING (select ID as in_ID, BALANCE_DATE in_BALANCE_DATE, DAILY_MOVEMENT in_DAILY_MOVEMENT, MTD_BALANCE in_MTD_BALANCE, YTD_BALANCE in_YTD_BALANCE, LTD_BALANCE in_LTD_BALANCE, 'ENTITY1' in_ENTITY, INPUT_TIME in_INPUT_TIME from concurrency_test_partition_in where ENTITY = 'ENTITY1' ) AS in_vals ON (ID = in_vals.in_ID AND ENTITY = in_vals.IN_ENTITY AND BALANCE_DATE = in_vals.IN_BALANCE_DATE ) WHEN MATCHED THEN UPDATE SET MTD_BALANCE = MTD_BALANCE + in_vals.IN_MTD_BALANCE WHEN NOT MATCHED THEN INSERT ( ID, BALANCE_DATE, DAILY_MOVEMENT, MTD_BALANCE, YTD_BALANCE, LTD_BALANCE, ENTITY) VALUES ( in_vals.in_ID, in_vals.IN_BALANCE_DATE, in_vals.IN_DAILY_MOVEMENT, in_vals.IN_MTD_BALANCE, in_vals.IN_YTD_BALANCE, in_vals.IN_LTD_BALANCE, in_vals.IN_ENTITY);

 

output from lokdisp blockers when running two concurrent sessions for different partitions ('ENTITY1' and 'ENTITY2'):

---------------- AMP 1 REPORTS 1 LOCK ENTRIES -------------
Number of Blocked Trans displayed :      1
=========================================
Blocked Trans : 30719 00094BE4
   Number of blockers displays :       1
   Number of blockers exists   :       1
   Blocker Trans : 30718 00040848
           lock mode       : Write
           lock status     : Granted
           lock objectType : RowRange
           lock PLLKind    : RowHash + All partitions
           lock objectID   : DBID      : 0000040B
                           : DBNAME    : SLR
                           : TableID   : 00002776,0400
                           : TableName : CONCURRENCY_TEST_PARTITION
                           : Partition1:                   0
                           : RowHash1  :            FFFFFFFF
                           : Partition2:                   0
                           : RowHash2  :            FFFFFFFF

Begining of the explain plan:

1) First, we lock SLR.concurrency_test_partition_in in TD_MAP1 for
     read on a reserved RowHash in all partitions to prevent global
     deadlock.
  2) Next, we lock SLR.concurrency_test_partition in TD_MAP1 for write
     on a reserved RowHash in all partitions to prevent global deadlock.
  3) We lock SLR.concurrency_test_partition_in in TD_MAP1 for read, and
     we lock SLR.concurrency_test_partition in TD_MAP1 for write.
  ....

 

 

Is there any document describing the mechanics of partition level locking in details, or someone can share his knowledge on that topic?

I'd like to understand optimizer rules that drive the decision about locking All partitions vs a single partition.

 

Cheers,

Maciek

 

2 REPLIES
Teradata Employee

Re: Partition level locking - Single vs All partitions

Does the EXPLAIN change if you replace in_vals.IN_ENTITY with the literal value 'ENTITY1' in both the match condition and the INSERT VALUES list?

Re: Partition level locking - Single vs All partitions

Hi Fred,

 

still whole target table is locked:

Explain merge into concurrency_test_partition  
		        USING (select ID as in_ID,
					  BALANCE_DATE in_BALANCE_DATE,
					  DAILY_MOVEMENT in_DAILY_MOVEMENT,
					  MTD_BALANCE in_MTD_BALANCE,
					  YTD_BALANCE in_YTD_BALANCE,
					  LTD_BALANCE in_LTD_BALANCE,
					  'ENTITY1' in_ENTITY,
					  INPUT_TIME in_INPUT_TIME
					  from concurrency_test_partition_in
					  where ENTITY = 'ENTITY1'
                )
                AS in_vals
        ON (ID = in_vals.in_ID AND
			ENTITY = 'ENTITY1' AND
            BALANCE_DATE   = in_vals.IN_BALANCE_DATE
            )
        WHEN MATCHED THEN 
            UPDATE SET MTD_BALANCE = MTD_BALANCE + in_vals.IN_MTD_BALANCE
        WHEN NOT MATCHED THEN
            INSERT 
            ( ID,
		      BALANCE_DATE,
		      DAILY_MOVEMENT,
		      MTD_BALANCE,
		      YTD_BALANCE,
		      LTD_BALANCE,
		      ENTITY)
            VALUES
            ( in_vals.in_ID,
		      in_vals.IN_BALANCE_DATE,
		      in_vals.IN_DAILY_MOVEMENT,
		      in_vals.IN_MTD_BALANCE,
		      in_vals.IN_YTD_BALANCE,
		      in_vals.IN_LTD_BALANCE,
		      'ENTITY1');

and the explain plan:

1) First, we lock SLR.concurrency_test_partition_in in TD_MAP1 for
     read on a reserved RowHash in all partitions to prevent global
     deadlock.
  2) Next, we lock SLR.concurrency_test_partition in TD_MAP1 for write
     on a reserved RowHash in all partitions to prevent global deadlock.
  3) We lock SLR.concurrency_test_partition_in in TD_MAP1 for read, and
     we lock SLR.concurrency_test_partition in TD_MAP1 for write.
  4) We do an all-AMPs RETRIEVE step in TD_MAP1 from 5480 partitions of
     SLR.concurrency_test_partition_in with a condition of (
     "SLR.concurrency_test_partition_in.ENTITY = 'ENTITY1'") into Spool
     1 (used to materialize view, derived table, table function or
     table operator in_vals) (all_amps), which is redistributed by the
     rowkey of (SLR.concurrency_test_partition_in.ID,
     SLR.concurrency_test_partition_in.BALANCE_DATE, 'ENTITY1') to all
     AMPs in TD_Map1.  Then we do a SORT to partition Spool 1 by rowkey.
     The size of Spool 1 is estimated with high confidence to be 21
     rows (2,331 bytes).  The estimated time for this step is 0.01
     seconds.
  5) We do an all-AMPs merge with matched updates and unmatched inserts
     into SLR.concurrency_test_partition from Spool 1 (Last Use) with a
     condition of ("(SLR.concurrency_test_partition.ID = IN_ID) AND
     ((SLR.concurrency_test_partition.ENTITY = Field_8) AND
     (SLR.concurrency_test_partition.BALANCE_DATE = IN_BALANCE_DATE ))").
     The number of rows merged is estimated with high confidence to be
     21 rows.
  -> No rows are returned to the user as the result of statement 1.