Teradata locking issues when using PMON for monitoring
My users will frequently report system hang problem and we will monitor the situation using PMON.
We will be able to see some ids blocking other ids and notice that the AMP or PE state to be idle Q1) What is the meaning of 'IDLE'? Q2) Under what situation does an 'IDLE' client can block other users?
Then when we look at the sql statement that the particular id is blocking some other ids, he is issuing a locking statement.
Q3) Why is it that while a user is inserting to a table, and another is viewing it by issuing a 'LOCK XXX FOR ACCESS', the other user can be blocked? Is the command 'LOCK XXX FOR ACCESS' working to our expectation? Or is it that we should be locking the table's full qualification rather than the view itself? Q4) Under what situation will issuing a 'LOCK' statement causes the DBMS to hang at that step?