The goal: The usp_prepare_data SQL Server stored procedure should check whether the requested data is prepared or not. If the data is ready to be used, the usp_prepare_data should return quickly. If the data is not prepared (there is the condition like valid to), then the data should be prepared (it takes like 60 seconds), and after that the usp should return.
The problem is that the usp_prepare_data is called from a web application, and it can happen that more than one request is created at the time of preparing the data. So, there should be something like mutual exclusion before entering the processing part. Then the condition should be checked, and then the processing should occur if necessary. The second and other requests should wait until they are allowed to check the condition. Using this, the processing part would be done only once.
Without the mutual exclusion the other requests find the "data not ready", and launch the process again, in parallel, causing possible problems when mutually locking the processed data.
With the mutual exclusion, the other processes would wait for the result about the same time, but the data processing will be done only once, without collisions with the same kind of processing at the same time.
How to do the mutual exclusion inside the stored procedure? The implementation uses SQL Server (the Azure SQL).
Update: ... to clarify. Basically, the procedure does 1) check whether new content should be generated, 2) DELETEs the obsolete data, and 3) INSERTs new data. When called twice in the sequence, it is likely to produce the same result (the input sources change slowly). I am aware of existence of transactions, and that I can wrap the 1), 2), and 3) together in one transaction. Correct me, if I am wrong. The transaction does the actions (consumes the processor time) independently on whether the result will be COMMITed or ROLLBACKed. This is not what I need. I do not want only to avoid corruption of the data table by running the same code in parallel. I want to save the processor time -- to avoid the computation if another one is already running.
Meanwhile, I have found the sp_getapplock. Trying...
More information: The usp_prepare_data actually gets the argument to generate certain portion of the data (think about the short string code that is unique). So, the collision could happen only when the code is the same. All the three phases (check, DELETE, INSERT) are related to the identified portion. The source tables are read WITH (NOLOCK), and the whole destination table should not be locked as a whole.
How to do the mutual exclusion inside the stored procedure?
Use sp_getapplock. The simplest way is to use the default "Transaction" ownership of the app lock. So start a transaction in the proc, call sp_getapplock, and then do whatever else in the transaction, eg:
begin tran
exec sp_getapplock 'data prep lock', 'Exclusive'
declare @data_prep_needed bit = 1
-- check if data is prepared
if @data_prep_needed = 1
begin
exec usp_prepare_data
end
exec sp_releaseapplock 'data prep lock'
commit tran
-- select data