Archives of the TeradataForum

Message Posted: Thu, 24 Apr 2014 @ 12:18:50 GMT

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

Hi Dave,

Hopefully this information will help explain this area.

An INSERT VALUES will place a single amp, rowhash write lock, i.e. only lock rows that have the same rowhash as the row your inserting. Note that even with a UPI, that may involve multiple rows because multiple data value may hash to the same rowhash, and it is the rowhash (NOT the row) that is locked.

An INSERT/SELECT will place an all amp, table level write lock (all rows, all rowhashes in the table).

As far as I'm aware/concerned, the above is independent of SET / MULTISET / QUEUE / VOLATILE / PERMANENT / FALLBACK table attribute. It may be different for COLUMNAR tables, I'm honestly not sure.

So that's the theory, now to your examples:

     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.

ROWHASH lock, which is why it's not mentioned in the explain output. The explain output only shows table level locks that are not access locks.

     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.

ROWHASH lock because you have an equality condition on all columns (in this case only 1) of the PI.

Yes, if different transactions (from same application, users or different ones) place rowhash locks on different rowhashes then those transactions wil not block each other.

So assume that values 1 and 2 give different rowhash values.

Transaction#1

     BT;

     INSERT INTO MY_TRANS_TBL VALUES(1,'a comment');

     ET;

Transaction#2

     BT;

     INSERT INTO MY_TRANS_TBL VALUES(2,'another comment');

     ET;

These two transactions can run concurrently because they are dealing with differentbrowhash values.

Now change transaction#2 to be the following:

     BT;

     INSERT INTO MY_TRANS_TBL VALUES(2,'another comment');

     SELECT *
     FROM MY_TRANS_TBL
     WHERE TBL_KEY = 1;

     ET;

If the select runws between the insert and et from transaction#1 then it will wait. As soon as the et from transaction#1 completes then the select from transaction#2 will run.

Does that help you?

Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

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