Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 07 Jan 2002 @ 20:31:54 GMT

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

Subj:   Re: Block everybody with ALTER TABLE
From:   Fred W Pluebell

Three part answer:

A) A DDL request is a single unit of work. Policy is to acquire all locks at the beginning of a unit of work to minimize deadlocks. Even if you deferred acquiring the lock, it must not be released before the unit of work ends.

B) You are correct that the AccessRights lock is table level instead of RowHash because access is not via PI. The PI is "User" while step 5.15 is qualifying on Database, Table, and Column.

C) Explain text for Step 6 is misleading. Not only are the table headers modified, but generally every row in the table must be reformatted as well (and Transient Journal written for each row for potential rollback). DROP COLUMN always reformats rows; ADD COLUMN will reformat rows except in the special case where the column is both nullable and compressed and spare presence bits exist in the row.

Even if it didn't lock out other work, ALTER TABLE might not be the best solution. INSERT SELECT into an empty table with the new structure is generally a better approach, if you have the space.

  <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: 15 Jun 2023