 |
 |
Archives of the TeradataForum
Message Posted: Thu, 24 Apr 2014 @ 12:19:37 GMT
| Subj: | | Re: Inserts Locks and Queue Tables |
| |
| From: | | Dieter Noeth |
inline...
David Clough wrote:
| | 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? | |
Of course it places a Row Hash lock, if it was a table lock Explain would show it.
| | 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 ? | |
Yep.
| | 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: | |
| | 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 ? | |
Yep. But there might be another PI value which happens to hash to the same Row Hash value, probability ~1:2**32
| | If so, what is this type of activity essentially the same as if I were doing it in Oracle or SqlServer, for example ? | |
Yep.
| | 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 ? | |
When the access is not based on PI/USI there will be a table lock, which is shown in Explain.
Dieter
| |
