How to resolve blocking of sessions

Database

How to resolve blocking of sessions

Hi

Good Day to ALL..!

Senario:

An Incident raised by ETL Team that their job is failing due to bolcking/deadlock. 

We found out that WebMethod team is accessing the same table for DML opeartion. That is why ETL jobs are failing. 

We repeatedly ask ETL team that we cant do so much untill Webmethod job is getting completed.

But they(ETL Team) are asking for automated notification with the job failed error message mail like " The table is blocked by other User (Username)/ session ID please try after some time"

How We can get this done.Please suggest.

4 REPLIES
Enthusiast

Re: How to resolve blocking of sessions

Plz try like dis.....

Lock Table UDAMLDEV.GDW_STG_ACCT_CLIST for ACCESS nowait

Select * from UDAMLDEV.GDW_STG_ACCT_CLIST;

if u get below msz Then the table is locked.

7423, "Object already locked and NOWAIT. Transaction Aborted."

Enthusiast

Re: How to resolve blocking of sessions

You can also write also write a small unix script checking the status $?  after logging to database with the db script nowait.....If status is not successful or 0 then  ....echo "Table is being loaded" | mailx -s "subject" sayansaheb@xyz.com

  ..... Check your linux/unix flavor, format.

This is okay if the box is configured for mails, else it won't work.

Enthusiast

Re: How to resolve blocking of sessions

Are both of them accessing the table directly or through view. Are both of them DML operations or one of them is DRL. In any case the best pratice is to give access to the table through a view which has locking row for access. If it is DML performed, then there is no option than to wait as in that case the table gets locked till one of the operation is complete. What tool is used by the ETL team. if it is something like informatica we have session failure messaging that can be configured if the session is failing. There are options like session retry and sleep which will try to reattempt for a certain period of time if it is blocked. Also please see from the session log or DBQL if it is due to a deadlock or session blocked for a long time that the ETL is filing. in the case of deadlock it could be because of dbc.accessrights table not able to receive a read lock. in case of blocking, wait needs to be configured. 

Re: How to resolve blocking of sessions

Thanks to All for the promt responce..

@ReddyRaj: I will try and let you know...

@Raja_KT : The box is not configured for mails

@Krishanesh : They both are using DML and through view...  Will check more on dbc.accessrights

Thanks Once again