Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 02 Jul 2012 @ 14:19:43 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Recommendation on Table Lock
 
From:   Dave Wellman

Hi,

For most of the time during the 'create index' work there is a WRITE lock on the target table. So if users execute SELECT statements using a LOCKING FOR ACCESS modifer then their requests will run. At some point, the WRITE lock will change to an EXCLUSIVE lock during which time no other access will be permitted. This is shown in the explain plan shown below (which is from 13.10, but most current releases of TD will probably show the same plan).

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct workdb."pseudo table" for write on a RowHash to prevent global deadlock for workdb.table100a.  
  2)Next, we lock workdb.table100a for write.  
  3)We create the index subtable on workdb.table100a.  
  4)We lock a distinct workdb."pseudo table" for exclusive use on a RowHash to prevent global deadlock for workdb.table100a.  
  5)We lock workdb.table100a for exclusive use.  
  6)We lock DBC.Indexes for write on a RowHash, and we lock DBC.TVM for write on a RowHash.  
  7)We execute the following steps in parallel.  
   
  1) We do an INSERT into DBC.Indexes.
 
   
  2) We do a single-AMP UPDATE from DBC.TVM by way of the unique primary index with no residual conditions.
 
  8)We modify the table header workdb.table100a and update the table's version number.  
  9)We spoil the parser's dictionary cache for the table.  
  10)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->No rows are returned to the user as the result of statement 1.  

I don't think there is anything that you can do to change this. If you use a LOCKING modifer in your 'create index' statemnent then it will only be honoured if you upgrade the lock, so I don't see that it will help.


Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019