Archives of the TeradataForum

Message Posted: Tue, 29 Jan 2008 @ 16:43:59 GMT


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

Subj:		Re: Locking Row for Access Stmt

From:		Ballinger, Carrie

Howard,

In response to this question....

As the data warehouse has matured there are now more single amp operations performed, would converting all views to row hash locking as standard have any adverse or undesired effect on the all amp workload ?

First, there is no measurable performance hit on the all-AMP queries by setting a row level access lock and having it converted to an all AMP access lock.

And even if access locking has been requested at the row level, if the plan requires an all-AMP operation, the requested row-hash lock will be converted automatically by the optimizer to a table-level lock. Always spot check the explains to verify that row or, when appropriate, table- level access locks are being issued for those queries.

Below is the plan for a query requiring all AMPs. The plan shows the table-level access lock being applied, even though the SQL requests a row-hash-level access lock. The optimizer escalates the access lock to the table level, even though table-level access locking was not requested.

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT c_name,
           c_acctbal
     FROM customer
     WHERE c_nationkey = 15;

Explanation

`Explanation --------------------------------------------------`
	`1)`	`First, we lock ADW.customer for access.`
	`2)`	`Next, we do an all-AMPs RETRIEVE step from ADW.customer by way of an all-rows scan with a condition of ("ADW.customer.C_NATIONKEY = 15") into Spool 1, which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The estimated time for this step is 2 minutes and 8 seconds.`
	`3)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`

As of V2R6 the optimizer will escalate the row hash level access lock appropriately. Prior to that release, there were some exceptions.

There is only one exception still remaining that I am aware of. That is the case where you have a macro or a multi-statement-request with mixed row-level modifiers (some access, some read). If the row level access lock statement is not last in the MSR or macro and the final statement takes the default read lock modifier, then the row level access lock will be converted to a row level read lock.

So if you use row level access locking in your views (which is recommended when you have a mix of single and all AMP queries using the view), make sure they are consistently applied across all views, rather than just a subset of views.

Thanks, -Carrie


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

Attachments

Library

Quick Reference