topic Dataset is locked by user preventing it from updating in SAS Data Management

Dataset is locked by user preventing it from updating

Lobstermaster — Thu, 28 Apr 2011 13:01:56 GMT

Hi, I use DI Studio to build jobs and run data loads. From time to time, I get error messages about the data set is locked by another process and preventing my jobs to update the data set.

My questions are,
1. Is there a way e.g. in the table loader transformation to have exclusive rights or lock over the data set so that I can load the data like in Teradata, there is a row access locking statement to implement?

2. If I create/load a table as a view in DI using a source data set and if some one is using the view, will it prevent me from updating the source data set?

Thanks,
Lobbie

Re: Dataset is locked by user preventing it from updating

Patrick — Fri, 29 Apr 2011 03:25:45 GMT

Hi

1. As much as I know concurrent read/write of Base SAS tables is only possible when using SAS/Share.

2. Terminology: If you're updating a table in DI then is this a target table (and not a source table). A view can be seen as encapsulated code. So if someone is executing this view then the table(s) accessed by this view get read and you can't write concurrently.

And last but not least:
I hope you're only using DIS to build jobs. For execution (other than unit testing) you should deploy the jobs and run them in batch (or eventually via EG if it has to be).

What you possibly could try:
Run your update jobs in an overnight process in batch.
Have policies and procedures in place that there is a time during the night where users are not allowed to access your data (eventually also by nightly restarting SAS servers or via script killing all SAS processes which access a table after a certain hour - i.e. all this "left-open" EG sessions).

HTH
Patrick

Re: Dataset is locked by user preventing it from updating

RMP — Fri, 29 Apr 2011 09:36:38 GMT

"Have policies and procedures in place that there is a time during the night where users are not allowed to access your data (eventually also by nightly restarting SAS servers or via script killing all SAS processes which access a table after a certain hour - i.e. all this "left-open" EG sessions)."

This last point is very important. Before your batch runs ensure that all sessions are killed. Many users open datasets in a session and leave for the day without closing Eguide. Very frustrating.

Re: Dataset is locked by user preventing it from updating

Lobstermaster — Thu, 19 May 2011 06:44:21 GMT

Hi Patrick and RMP,

thanks for your advice. Will look into this.

Regards,
Lobbie