While studying for Teradata lock, I became a bit confused regarding level of locking. Instead of givingh specific examples I will directly ask my query here. From DML, how optimiser decides it is a table level lock or row hash level? I mean just if I look into my SQL, how do I understand which level of lock is going to be placed?
If I specify "LOCK TABLE EMP FOR READ" and then if I write a query with UPI in where clause, will my user chosen table level lock be downgraded to hash level?
Vice versa i.e. "LOCK ROW FOR WRITE", then query says about a UPDATE TAB SET FIELD= ... there is no where condition. Will my row level lock be upgraded to a table level by optimiser?
You can consult the following document and you will get all the answers to your query:
Also, yes the optimizer has the capbility to upgrade downgrade locks depending upon the requirement.