Distributed Locking via SQL Server

Sproc.Lock gives a quick and easy way to take and release locks across multiple servers, using SQL Server as a back end.

Building Sproc.Lock will attempt to deploy it's schema as one of the build steps - by default it will try to create/connect to a database called Lock on (local) using the credentials of the user running the build scripts; setting an environment variable called LockConnString with an alternative connection string will override this and both deploy and test Sproc.Lock on the server of your choice.

Once a server is deployed, using the library is easy. Let's assume that we have a third party service called NastyAPI which we only have one account for.

We'd like to write a service that only calls NastyAPI if no one else is, to avoid punitive charges. (They're nasty, okay?)

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16:

#r "Sproc.Lock.dll"
open System
open Sproc.Lock.Fun

let lserver = "SQL Server connection string here"

let GetNastyData () =
    use lock =
        GetGlobalLock lserver (TimeSpan.FromMinutes 5.) "NastyAPI"
    match lock with
    | Locked l ->
        NastyAPI.DoOp()
    | Unavailable ->
        () // Do nothing
    | Error i ->
        () // Sproc.Lock internal error occurred

/// <summary>
/// Wrapper class; all examples below are static members
/// within it.
///
/// These examples use the Sproc.Lock.OO namespace
/// </summary>
public class DoWork
{
    static LockProvider provider = new LockProvider("sql connection string");
    static public void GetNastyData()
    {
        try
        {
            using (var myLock = provider.GlobalLock("NastyAPI", TimeSpan.FromMinutes(5.0)))
            {
                NastyAPI.DoOp();
            } // Lock released when Disposed
        }
        catch (LockUnavailableException)
        {
            // Do nothing
        }
        catch (LockRequestErrorException)
        {
            // Sproc.Lock internal error occurred
        }
    }
}

What does this do? Well, it gets the lock called NastyAPI from the Sproc.Lock server if (and only if) it's available; if it is, it calls NastyAPI.

If not it does nothing. All Locks and LockResults are IDisposable, so the lock will be dropped when the function completes regardless of which execution route is taken.

Regardless of what else happens, the lock server will drop the lock after 5 minutes; the maximum duration specified in the get lock call should be well in excess of the time you expect NastyAPI to take. Why is this? Well - if your service were to crash, or (worse) hang indefinitely, that lock would be unavailable forever. Conversely, if you pick too short a maximum duration your operation may not be finished before the lock expires.

Be conservative with maximum durations; an order of magnitude more than the normal time required is probably about right.

This is all very clean; but normally you don't want to just "do nothing" if the lock is unavailable. Let's wait for it to become free instead.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11:

let WaitForNastyData () =
    use lock =
        fun () -> GetGlobalLock lserver (TimeSpan.FromMinutes 5.) "NastyAPI"
        |> AwaitLock (TimeSpan.FromMinutes 5.)
    match lock with
    | Locked l ->
        NastyAPI.DoOp()
    | Unavailable ->
        () // Do nothing
    | Error i ->
        () // Sproc.Lock internal error occurred

static public void WaitForNastyData()
{
    try
    {
        using (var myLock = provider.AwaitGlobalLock("NastyAPI",
            TimeSpan.FromMinutes(5.0), 
            TimeSpan.FromMinutes(5.0)))
        {
            NastyAPI.DoOp();
        } // Lock released when Disposed
    }
    catch (LockUnavailableException)
    {
        // Do nothing
    }
    catch (LockRequestErrorException)
    {
        // Sproc.Lock internal error occurred
    }
}

This code is very similar to the code above, except that if the lock is not immediately available, it will check repeatedly for the next 5 minutes until it is.

Only after the 5 minutes is up will it then report the lock unavailable; if it acquires it on any of the attempts inbetween it will call NastyAPI.

How often does it poll? Initially very fast, backing off if the lock is not immediately available.

If we have multiple NastyAPI accounts, we can also take advantage of that to make a number of concurrent requests limited to the number of available accounts.

First, we need to create a seq of lock IDs which are unique to each account:

1:	let lockIds = ["NastyAPI1";"NastyAPI2"]

static List<String> lockIds = new List<string> { "NastyAPI1", "NastyAPI2" };

Then we can try to see if any of them are available:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12:

let GetAnyNastyData () =
    use lock =
        lockIds
        |> OneOfLocks (fun id -> GetGlobalLock lserver (TimeSpan.FromMinutes 5.) id)
    match lock with
    | Locked l ->
        // We know which lock we obtained here
        NastyAPI.DoAccountOp l.LockId
    | Unavailable ->
        () // Do nothing - no locks available
    | Error i ->
        () // Sproc.Lock internal error occurred

static public void GetAnyNastyData()
{
    try
    {
        using (var myLock = provider.OneOf(
                id => provider.GlobalLock(id, TimeSpan.FromMinutes(5.0)),
                lockIds
            ))
        {
            NastyAPI.DoAccountOp(myLock.LockId);
        } // Lock released when Disposed
    }
    catch (LockUnavailableException)
    {
    }
    catch (LockRequestErrorException)
    {
    }
}

Or, again, we can await one of the collection of locks:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14:

let AwaitAnyNastyData () =
    use lock =
        fun () ->
            lockIds
            |> OneOfLocks (fun id -> GetGlobalLock lserver (TimeSpan.FromMinutes 5.) id)
        |> AwaitLock (TimeSpan.FromMinutes 5.)
    match lock with
    | Locked l ->
        // We know which lock we obtained here
        NastyAPI.DoAccountOp l.LockId
    | Unavailable ->
        () // Do nothing - no locks available
    | Error i ->
        () // Sproc.Lock internal error occurred

static public void AwaitAnyNastyData()
{
    try
    {
        using (var myLock = provider.AwaitOneOf(
                id => provider.GlobalLock(id, TimeSpan.FromMinutes(5.0)),
                lockIds,
                TimeSpan.FromMinutes(5.0)
            ))
        {
            NastyAPI.DoAccountOp(myLock.LockId);
        } // Lock released when Disposed
    }
    catch (LockUnavailableException)
    {
    }
    catch (LockRequestErrorException)
    {
    }
}

Using "scoped" locks works in a similar fashion. Let's look at an API that a "Organisation" can only access with a single shared connection:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13:

let OrganisationNastyData orgName =
    use lock =
        GetOrganisationLock lserver orgName (TimeSpan.FromMinutes 5.) "NastyAPI"
    match lock with
    | Locked _ ->
        // Any number of organisations can do this at the same time
        NastyAPI.DoOp ()
    | Unavailable ->
        // Only reached if the same organisation has already acquired the lock
        ()
    | Error i ->
        // Sproc.Lock hit an error
        ()

static public void OrganisationNastyData(string orgName)
{
    try
    {
        using (var myLock = provider.OrganisationLock(orgName, "NastyAPI", TimeSpan.FromMinutes(5.0)))
        {
            NastyAPI.DoOp();
        } // Lock released when Disposed
    }
    catch (LockUnavailableException)
    {
    }
    catch (LockRequestErrorException)
    {
    }
}

And possibly more frequently, there might be a collection of accounts available within production environments for a client, and a separate collection for non-production testing.

For our final example, let's assume we have multiple accounts available in production, a single testing account, and we're willing to wait up to 2 minutes for a lock to become available.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
49:

type NastyAccounts =
    {
        OrganisationName : string
        Environment : string
        AccountNames : string list
    }

let flyAwayProd =
    {
        OrganisationName = "FlyAwayAir"
        Environment = "PRD"
        AccountNames = ["Nasty1";"Nasty2"]
    }

let flyAwayTest =
    {
        OrganisationName = "FlyAwayAir"
        Environment = "TST"
        AccountNames = ["Nasty1"]
    }

let otherTest =
    {
        OrganisationName = "OtherAir"
        Environment = "PRD"
        AccountNames = ["Nasty1"]
    }

let GetNastyLock accounts =
    use lock =
        fun () ->
            accounts.AccountNames
            |> OneOfLocks
                (fun lid ->
                    GetEnvironmentLock
                        lserver
                        accounts.OrganisationName
                        accounts.Environment 
                        (TimeSpan.FromMinutes 5.) lid)
        |> AwaitLock (TimeSpan.FromMinutes 2.)
    match lock with
    | Locked lockId ->
        // All three "Nasty1" locks might be in use concurrently here;
        // but only one from each organisation/environment
        NastyAPI.DoAccountOp lockId
    | Unavailable ->
        ()
    | Error _ ->
        ()

public class NastyAccounts
{
    public string OrganisationName { get; set; }
    public List<string> AccountNames { get; set; }
    public string Environment { get; set; }

    public NastyAccounts(string orgName, string env, List<string> accounts)
    {
        this.OrganisationName = orgName;
        this.Environment = env;
        this.AccountNames = accounts;
    }
}

public static NastyAccounts flyAwayProd =
    new NastyAccounts("FlyAwayAir",
        "PRD", new List<string> { "Nasty1", "Nasty2" });

public static NastyAccounts flyAwayTest =
    new NastyAccounts("FlyAwayAir",
        "TST", new List<string> { "Nasty1" });

public static NastyAccounts otherTest =
    new NastyAccounts("OtherAir",
        "PRD", new List<string> { "Nasty1" });

public static void GetNastyLock(NastyAccounts accounts)
{
    try
    {
        using (var myLock = provider.AwaitOneOf(
            id => provider.EnvironmentLock(
                id, accounts.OrganisationName, 
                accounts.Environment, TimeSpan.FromMinutes(5.0)),
            accounts.AccountNames,
            TimeSpan.FromMinutes(2.0)))
        {
            NastyAPI.DoAccountOp(myLock.LockId);
        }
    }
    catch (LockUnavailableException)
    {
    }
    catch (LockRequestErrorException)
    {
    }
}

val DoOp : config:'a -> unit

Full name: Tutorial.NastyAPI.DoOp

val config : 'a

val DoAccountOp : str:'a -> unit

Full name: Tutorial.NastyAPI.DoAccountOp

val str : 'a

namespace System

namespace Sproc

namespace Sproc.Lock

module Fun

from Sproc.Lock

val lserver : string

Full name: Tutorial.lserver

val GetNastyData : unit -> unit

Full name: Tutorial.GetNastyData

val lock : LockResult

val GetGlobalLock : connString:string -> maxDuration:TimeSpan -> lockIdentifier:string -> LockResult

Full name: Sproc.Lock.Fun.GetGlobalLock

Multiple items
type TimeSpan =
  struct
    new : ticks:int64 -> TimeSpan + 3 overloads
    member Add : ts:TimeSpan -> TimeSpan
    member CompareTo : value:obj -> int + 1 overload
    member Days : int
    member Duration : unit -> TimeSpan
    member Equals : value:obj -> bool + 1 overload
    member GetHashCode : unit -> int
    member Hours : int
    member Milliseconds : int
    member Minutes : int
    ...
  end

Full name: System.TimeSpan

--------------------
TimeSpan()
TimeSpan(ticks: int64) : unit
TimeSpan(hours: int, minutes: int, seconds: int) : unit
TimeSpan(days: int, hours: int, minutes: int, seconds: int) : unit
TimeSpan(days: int, hours: int, minutes: int, seconds: int, milliseconds: int) : unit

TimeSpan.FromMinutes(value: float) : TimeSpan

union case LockResult.Locked: Lock -> LockResult

val l : Lock

module NastyAPI

from Tutorial

union case LockResult.Unavailable: LockResult

union case LockResult.Error: int -> LockResult

val i : int

val WaitForNastyData : unit -> unit

Full name: Tutorial.WaitForNastyData

val AwaitLock : timeOut:TimeSpan -> getLock:(unit -> LockResult) -> LockResult

Full name: Sproc.Lock.Fun.AwaitLock

val lockIds : string list

Full name: Tutorial.lockIds

val GetAnyNastyData : unit -> unit

Full name: Tutorial.GetAnyNastyData

val OneOfLocks : getLock:('a -> LockResult) -> lockIds:seq<'a> -> LockResult

Full name: Sproc.Lock.Fun.OneOfLocks

val id : string

property Lock.LockId: string

val AwaitAnyNastyData : unit -> unit

Full name: Tutorial.AwaitAnyNastyData

val OrganisationNastyData : orgName:string -> unit

Full name: Tutorial.OrganisationNastyData

val orgName : string

val GetOrganisationLock : connString:string -> organisation:string -> maxDuration:TimeSpan -> lockIdentifier:string -> LockResult

Full name: Sproc.Lock.Fun.GetOrganisationLock

type NastyAccounts =
  {OrganisationName: string;
   Environment: string;
   AccountNames: string list;}

Full name: Tutorial.NastyAccounts

NastyAccounts.OrganisationName: string

Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = String

Full name: Microsoft.FSharp.Core.string

Multiple items
NastyAccounts.Environment: string

--------------------
type Environment =
  static member CommandLine : string
  static member CurrentDirectory : string with get, set
  static member Exit : exitCode:int -> unit
  static member ExitCode : int with get, set
  static member ExpandEnvironmentVariables : name:string -> string
  static member FailFast : message:string -> unit + 1 overload
  static member GetCommandLineArgs : unit -> string[]
  static member GetEnvironmentVariable : variable:string -> string + 1 overload
  static member GetEnvironmentVariables : unit -> IDictionary + 1 overload
  static member GetFolderPath : folder:SpecialFolder -> string + 1 overload
  ...
  nested type SpecialFolder
  nested type SpecialFolderOption

Full name: System.Environment

NastyAccounts.AccountNames: string list

type 'T list = List<'T>

Full name: Microsoft.FSharp.Collections.list<_>

val flyAwayProd : NastyAccounts

Full name: Tutorial.flyAwayProd

Multiple items
union case Lock.Environment: connString: string * lockId: string * hashed: Hashed * organisation: Hashed * environment: Hashed * instance: Guid -> Lock

--------------------
type Environment =
  static member CommandLine : string
  static member CurrentDirectory : string with get, set
  static member Exit : exitCode:int -> unit
  static member ExitCode : int with get, set
  static member ExpandEnvironmentVariables : name:string -> string
  static member FailFast : message:string -> unit + 1 overload
  static member GetCommandLineArgs : unit -> string[]
  static member GetEnvironmentVariable : variable:string -> string + 1 overload
  static member GetEnvironmentVariables : unit -> IDictionary + 1 overload
  static member GetFolderPath : folder:SpecialFolder -> string + 1 overload
  ...
  nested type SpecialFolder
  nested type SpecialFolderOption

Full name: System.Environment

val flyAwayTest : NastyAccounts

Full name: Tutorial.flyAwayTest

val otherTest : NastyAccounts

Full name: Tutorial.otherTest

val GetNastyLock : accounts:NastyAccounts -> unit

Full name: Tutorial.GetNastyLock

val accounts : NastyAccounts

val lid : string

val GetEnvironmentLock : connString:string -> organisation:string -> environment:string -> maxDuration:TimeSpan -> lockIdentifier:string -> LockResult

Full name: Sproc.Lock.Fun.GetEnvironmentLock

NastyAccounts.Environment: string

val lockId : Lock