 |
 |
Archives of the TeradataForum
Message Posted: Mon, 02 Jul 2012 @ 14:19:43 GMT
| 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)
| |
