1. INSERT requires a WRITE lock (the request for ACCESS lock will be ignored)
2. INSERT VALUES should still be a RowHash lock; INSERT SELECT will be a Table lock. (Table locks will show up in the EXPLAIN.)
Yes, lock contention is possible (even with RowHash locks, though less likely in that case).
Thanks for your reply. Just to summarize my understanding, even if I am using a VIEW with LOCKING ROW FOR ACCESS,
1. If it is Update with PI value, it will be Row-hash Write lock
2. If it is Update with other column, it will be Table Write lock
3. If it is Insert Values, it will be Row-hash Write lock
4. If it is Insert-Select, it will be Table Write lock
5. This strategy will be same for bteq and Informatica PDO
Please let me know your response.