Transaction aborted due to dead lock on audit table
We have a scenario where multiple processes try to update the audit table "A" for completion status and counts. Since it needs only one row to update everytime we used primary index columns and made the process skip from Deadlock by using row hash lock. But we have a scenario where audit table "A" joins with "B" table and get the total counts and updates the table "A". For this above scenario we are having trouble that the complete Audit table is being locked rather than Row hash and ending up in deadlock. Can you please suggest how can we eliminate these scenarios?
UPDATE A FROM B
SET A.COUNT = B.COUNT
WHERE Primary index columns in "A"
This is locking the complete table. And this query is being written by Informatica PDO.