Resource is read-locked by another user

[Longimanus]

Hi everybody!

I have to a simple thing like adding a column to an excisting (huge) table. Just a PROC SQL with an ALTER TABLE statement.

The table I'm trying to add the column to is read locked as you can see in the log below.

I have been working on IBM's iSeries and here one could quickly see who or what had a lock on a given table/file.

Here is more complicated ... I think.

Is there anybody out there who can teach me to figure out WHAT or WHO locks this "resource". It's residing in SPDS which makes it (maybe?) even more complicated since the file is "chopped up" in smaller pieces.

Any input is welcome!

Cheers!

Menno

NOTE: Remote submit to NYA commencing.

18   proc sql;

19                            ALTER TABLE dds_spd.CREDIT_CARD_TRANSACTIONS

20                            ADD TRANSACTION_CURRENCY_CD char(3) label='Trans. curr.cd.'

21                       ;

ERROR: Resource is read-locked by another user.  File

=/opt/app/sas92/configdir/Lev2/SASApp/Data/bisdata/dds/dds_spd_meta1/credit_card_transactions.mdf.0.0.0.spds9.  System Error Code = 0.

ERROR: The file is already locked by another user.

ERROR: Requested lock is not available for data set.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

21 !                      quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.02 seconds

      cpu time            0.00 seconds

NOTE: Remote submit to NYA complete

[Kurt_Bremser]

Since it is a UNIX system, you can use the fuser command to get the id of the process that has an open file handle to the file. Given that you work with SAS/CONNECT, you should be able to telnet to the server.

[Longimanus]

Hi Kurt!  To be honest I have no clue if we use SAS/CONNECT. I acces the Unix Servers where our SAS systems reside, through my client (Windows). I run SAS locally too. Most of the times I use rsubmit. The verd "to telnet" is also not known to me. I'm a develloper I count on everything is run the way I like to (so the people I work for get the stuff they want).  If I would use the fuser command direcktly on Unix, how would I enter this in the right format? Would that be:

fuser /opt/app/sas92/configdir/Lev2/SASApp/Data/bisdata/dds/dds_spd_meta1/credit_card_transactions.mdf.0.0.0.spds9

Just guessing ...

[Kurt_Bremser · Accepted solution]

rsubmit is used when you want to run code on the server from a local SAS session over SAS/CONNECT. That's what led me to believe you are using CONNECT. If you have options comamid tcp; and signon servername; somewhere in your code (or in the autoexec.sas), then you are using SAS/CONNECT. When the server part of a SAS/CONNECT setup is on UNIX, the connection is established via the telnet protocol (see wikipedia about that). You can use the same credentials to manually establish a telnet session by using "telnet servername" in the "start - run" dialog of windows (or just enter telnet servername in a cmd window).

Your example for the fuser command looks right.

fuser gives you process number(s); ps -fp processnumber will show you the program name and the user who started the process. To kill another user's process, though, you will need root (superuser) privileges.

[Longimanus]

Hej Kurt! I have found the "sinner" ... I think I will KILL it. I used the ps command with -fp

• As you can see I first got the process no that obviously interacts with that partition I have problems with.
• Then I tried the ps command - failed because invalid argument
• Then with the right argument and tada: The UID, PID, etc. I think I can use this to solve it ... Think. 😉 Let's see if I can do what I want if I kill this process (if I can kill it ...). I will be back to let y'all know.

[Longimanus]

Killed the "sinner" and I am running what I want now. Thanks a lot to both of you for your input !!! :smileygrin:

[SudhakarN]

Hi All,

I am also facing the similar type of issue. Here issue is read lock created on XML file and it is created same program it self.

If re - submit the same job then its working fine. Please help me what would be the root cause for this issue.

ERROR: Resource is read-locked by another user. File =/ADM_D3/tst/PROD/EFF/PY2015/201510/ACA/ACA W
PPS_DPO_UST_2015_201510_EXT.xml. System Error Code = 0.
MPRINT(CLAIMER_SHEET): ods tagsets.ExcelXP file="/ADM_D3/tst/PROD/EFF/PY2015/201510/ACA/ACA W
PPS_DPO_UST_2015_201510_EXT.xml" style=styles.XXXX options (embedded_titles='yes' embedded_footnotes='no' print_footer=
'&XXXX Confidential and Proprietary. May not be released to any third party without prior consent.

 &CPage: &P of &N ' orientation='landscape' fittopage='yes' pages_fitwidth='1' pages_fitheight='1'
autofit_height='yes' absolute_column_width='8,50,4,9,9,9,9,9,9' sheet_name='Claimer' sheet_interval='none' );
NOTE: Writing TAGSETS.EXCELXP Body file: /ADM_D3/tst/PROD/EFF/PY2015/201510/ACA/ACA W PPS_DPO_UST_2015_201510_EXT.xml
ERROR: File is in use, /ADM_D3/tst/PROD/EFF/PY2015/201510/ACA/ACA W PPS_DPO_UST_2015_201510_EXT.xml.
ERROR: No body file. TAGSETS.EXCELXP output will not be created.

Please let me know if you need any more info on this. I am expecting root cause and solution for this type of issues.

Thanks IN Advance.

:

[Kurt_Bremser]

PS:

You could even try to automate that from within SAS, but it requires shell scripting on the UNIX side.

You can use the X statement in an rsubmit block to execute a shell script that does the fuser/ps evaluation and then writes the result to a file. A data step can then be used to read that file and put the contents into the SAS log, or use call system to do the kills, if you want to be that nasty.

[Longimanus]

I tried the fuser command. Just to see what is gave me back. The system returned with 28471o. Let's see what I can do with that.

• Yesterday I did restart all the SAS services in hope that the lock would be realeased. But no. It's still there.
• What also is tragi-comic is that I cannnot find the partition with '.mdf.' when I look in the SPD folder. Lots of partitions with '.dpf.' extentions for the given table but no '.mdf.'.

I still think (coming from iSeries - already when it was called AS/400) that there should be a command that easily tells you: WHAT or WHO has a lock on ANY given object (and what kind of lock is it).  I loved the command WRKOBJLCK (Work with Object Lock) for giving me all that information.

[LinusH]

From the search path it seems like your updating a SPD Server table. There are ways to see user activity in SPD Server. One is by using the SPD Server plugin in the Management Console.

You can also use PROC SPDO to query about logged in users etc.

A third alternative is to open SPD Server audit logs. If I recall right you can see there who is accessing which table.

There are a slight possibility that you are locking the table your self. Be sure that you don't have any properties window or similar active.

[Longimanus]

Hi LinuH!

I will check this things out. New areas for me but that is only enriching. I was really hoping I could write some command to get the exact sinner (yes, could even be myself) and then asked this person to get out ... or kill a process that was holding. It's not often I run into read locks. Mostly write locks.  Thanks for your feedback!

[sowmya]

You can use fuser -u recource-name to find which process is using it up.

You can also fuser -k command to kill the process thats locking up the resource.