Ensuring CLR code is only used by one user at the time

Question

Ensuring CLR code is only used by one user at the time

chris-890471

SSC Rookie

Points: 46
More actions
September 25, 2008 at 6:13 am

#193207

I have written a similar post in the Developers forum but maybe this is a better place.
I need to produce a function that is passed a single parameter and selects the last record in a table prefixed with this parameter. The function then performs a complex calculation in order to generate a new record (with the same prefix) which it writes back to the table and returns the value to the user.
I need to ensure that this can only be used by a single user at the time to prevent duplication of the calculated records. Any other requests to use the function should be rejected.
I have been give advice on using sp_getapplock using TSQL but was hoping to do the whole thing in CLR as the calculation is beyond what I believe is possible in TSQL. (Also I already have the calculation code in VB from a previous application.)
Any suggestions?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

You need to implement the Singleton pattern as a part of your CLR Assemblies design, using a static readonly object member variable which still allows for the Assembly to be cataloged as safe. Then in the function you check for a lock on this object, and if it is locked wait for the lock to release before executing. When it can execute you lock the object and process then unlock at the end of the execution. I am pretty sure that you are going to have to make it a stored procedure though, because the function could terminate returning to TSQL the value, and then execute again before you issue the update/insert of the calculated value, and you can do datachanges on a context connection in a function. You can see an example of this pattern in Sample #2 on the following link:

http://www.yoda.arachsys.com/csharp/singleton.html"> http://www.yoda.arachsys.com/csharp/singleton.html