We have a scenario where a load table - load_test is being continously loaded using TPT STREAM OPERATOR (Vigil on). We have a delete as well running on that load_table so we flush out the processed records since its a continous load. We encounter deadlock issue intermittently on the load table while deleting the data.
1. I assume that row level hash lock is placed on table when we do TPT Stream operation
2. A table level lock is placed when we do a delete.
Is the solution for avoiding the deadlocks is to have row level lock placed on load table while deleting or to create a view on load table with locking row for access or is there any better approach to avoid these kin of deadlocks?