Dynamic Locking on SPD Server not working

[macrobo]

Hallo SAS Community,

I want to use Dynamic Locking on our SAS-SPD-Server to enable concurrent access to tables. I added DYNLOCK=YES to the existing domain definition in the libnames.parm file:

libname=datahilf pathname=/daten_q/spds/index/datahilf owner=sas
        roptions="metapath=('/daten_q/spds/index/datahilf')
        datapath=('/daten_q/spds/data1/datahilf')
        indexpath=('/daten_q/spds/index/datahilf')"
        LIBACLINHERIT=YES
        DYNLOCK=YES;

I stopped und re-started the SPD-Server and ran the following programm in parallel from two instances of SAS-EG:

data datahilf.test_concurr;
 do i = 1 to 60;
   a= 1; output;
   call sleep(1,1);
 end;
run;

From one of the instances I still get the following ERROR in the Log:

19 data datahilf.test_concurr;
20 do i = 1 to 60;
21 a= 1; output;
22 call sleep(1,1);
23 end;
24 run;

ERROR: MEMBER lock is not available for DATAHILF.TEST_CONCURR.DATA, lock held by process 40042846.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

So it seems as if the configuration change in libnames.parm was not successful. I also checked whether the SPD Start-Script uses the correct libnames.parm file.
The SPD version is (unfortunately still) SAS Scalable Performance Data Server 5.4 (HF 1) Build(Tue Aug 14 11:45:18 EDT 2018) SPDS SNET Server

My questions:
- What is wrong in the domain definition? (I also ensured that record level locking ist not set.)
- Is there anything else to configure?

Thanks in advance
Robert

[Kurt_Bremser · Accepted solution]

A DATA step will always create the dataset, and destroy any pre-existing dataset of the same name. That's why it issues a non-breakable member lock.

You need to try this with PROC SQL and INSERT statements, once the dataset has been created. You can concurrently run a DATA step which reads from the dataset and calls SLEEP, so that it keeps the dataset open for some time.

[LinusH]

Just to clarify, this will work when a statement updates the table in place.

So data step with modify, or SQL UPDATE are also valid scenarios too, I believe.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/spdsag/p00j3zfgahpx73n1nhs5bjena764.htm

[macrobo]

Thank you very much for your replies.
I played around with the modify statement because it allows to insert a call sleep and to lock the table for a defined time.
I ran in parallel from two EG instances the following code with table datahilf.test_concurr from above:

data datahilf.test_concurr;
 modify datahilf.test_concurr;
 a= a + 1;
 call sleep(1,1);
run;

The result was that datahilf.test_concurr was modified sequentially. The first instance updated a to 2 in around 60 seconds (call sleep for 60 observations) and the second instance modified a to 3 after another 60 seconds.
That means the parallel running modifying jobs have been queued.

In a second test I ran the modifying job again in one EG instance and after 30 seconds I ran a reading job in parallel (without call sleep) in the other instance:

data work.test_concurr;
 set datahilf.test_concurr;
run;

The result was that the jobs ran in parallel: In work.test_concurr 20 of the 60 Obs already had the value 4 instead of 3.

I was happy with these results and I tried to activate dynamic locking for another library, but it didn't work.
Also when I tried to de-activate dynamic locking for datahilf, this didn't work. It was still active after several restarts of the SPDS.

Do you have any ideas for this behaviour?

[LinusH]

I don't have access to SPDS at the moment, so I can't try myself.

When you say doesn't work (activate and de-activate), how have you verified that?

My guess is that the process of the libnames.parm file is shown in the spdsserv.log file...?

[macrobo]

I found the problem that prevented the SPD server from accepting changes in the libnames.parm file. The server could shut down correctly before the next startup. 

So thank you very much for helping me, the test problem above is solved.

Best regards,

Robert