Archives of the TeradataForum

Message Posted: Thu, 24 Apr 2014 @ 11:00:36 GMT

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

I think there's something very basic and very fundamental that I'm not clear on - which is embarrassing. But, if I'm not so clear, maybe others will be unclear too ....

When an Insert Statement writes to a Table - be it a Queue Table or ordinary Multiset Table - and that Write statement uses a VALUES clause, with a new value for the Unique Primary Index, does that action then only place a Write Hash Lock on the Table ? Or, does it place a Write Lock on the whole Table ?

If it only places a Row Hash Lock then does this mean that the Table itself could be used almost like a Transactional Database ? That is, many Users Inserting and Updating rows from the same Table, without being locked out from other Users ?

We tend to use Locking Row Access in our Views, so that Batch Updates can be applied - which is fine - but I'm now wondering what differentiates a Teradata Table being Inserted to by something like this sequence of actions:

     CREATE MULTISET TABLE MY_TRANS_TBL (TBL_KEY INTEGER, MY_COMMENTS
     VARCHAR(256)) UNIQUE PRIMARY INDEX (TBL_KEY);

     INS INTO MY_TRANS_TBL VALUES (1 , 'My first Comment');

     1) First, we do an INSERT into MY_TRANS_TBL.  The
          estimated time for this step is 0.22 seconds.
       -> No rows are returned to the user as the result of statement 1.
          The total estimated time is 0.22 seconds.

     INS INTO MY_TRANS_TBL VALUES (2 , 'My Second Comment');

     INS INTO MY_TRANS_TBL VALUES (3 , 'My Fourth Comment');

     UPDATE MY_TRANS_TBL SET MY_COMMENTS = 'Oops, my Third Comment'
     WHERE TBL_KEY = 3;

       1) First, we do a single-AMP UPDATE from DEVG123AHE.MY_TRANS_TBL by
          way of the unique primary index "MY_TRANS_TBL.TBL_KEY =
          3" with no residual conditions.
       -> No rows are returned to the user as the result of statement 1.

Would I now understand it that other Users could be Inserting and Updating other values, without being affected by the above, as long as they're dealing with other Primary Index values ?

If so, what is this type of activity essentially the same as if I were doing it in Oracle or SqlServer, for example ?

Yes, I can see that a Batch suite could not simultaneously work against that Table, at least I guess not, or does that even depend upon which rows it's touching ?

Regards

David Clough
Senior BI Database Designer
BI Competency Centre

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