we are facing an issue regarding multiple sessions are inserting data in a table that’s why other sessions are getting block because they also try to insert error record in the same table. This is a error table and each job insert data in it when it find any error records. Jobs are getting delay because jobs need to wait till it gets write lock on error table.
Any suggestion to resolve this issue
Thanks Raja for your reply.
well its a job that trigger using datastage. Each job check records from this error table if it find any records then it move it into this table.
lets say three job are running then all jobs will check data in error table first if job data also exist in error table then those recors that are common will move to error table. If all jobs find some common records the all will try to insert in same table that results to blocking.
Without seeing the code in DS, can't say much. However, you can think of doing a lock, to avoid deadlock or use different DB if possible for error, log.....
You can achive write concurrency on a table provided that the row hash locks do not overlap otherwise you will be blocked which is probably what is happening in your case.