Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 18 Feb 2009 @ 20:13:22 GMT

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

Subj:   Re: Views and locking
From:   Sanchez, Idrike

  Can anyone confirm that the lock is upgraded to a read when issuing an insert select Vs a straight select?  

I can confirm that under certain cases the lock will be elevated to a read lock.

> > From a previous email to the masters forum from 5/9/08:

Before you add locking row for access on all the base views definition I think you should look at RFC85390 (I copied what I could from it below). Notice no solution planned in the short run - (I just checked and no update to the RFC status given below).

We added locking row for access years ago to one view in our enterprise layer. A user query later ended up blocking the tpump. The reason was that the user query was complex enough and/or contained multistatement requests (something like that). In some cases the lock will be elevated to a READ lock instead of ACCESS. I agree that the design is to escalate to a table level; the problem is when it escalates to more than an access lock....

Either way, since I have no way to control what queries users will run from this enterprise layer I had to remove it at this level.

We do use it for specific databases where views are created for applications. These are known queries and they are tested beforehand to ensure that the lock will not be elevated to a read lock.

Kiki Sanchez
Data Warehouse Group
Technology Division
Continental Airlines

This report was generated on 02/18/2009 at 10:29:23.

DR 85390
Returned records: 1

App Code Number Abstract Status Type Product Component Symptom Description Open Date Last Update Date RFC Status RFC Disposition Found In Release Target Release Fixed In Release Workaround Tech Alert DR 85390 Access lock on view is elevated to read lock DEFERRED RFC DBS PAR 2004/01/13 2009/02/06 Dispositioned: on 2005-05-13 Accept as a Future Requirement 000

The problem here is when LOCK ROW modifier is specified in a multi-statement request along with other statements, unless the LOCK ROW modifier is the last statement in the request, there is no effect of this locking modifier.

  <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: 27 Dec 2016