Transactions

This chapter assumes a basic understanding of the "Transactions and Concurrency" topic as it applies to ADO.NET and SQL Server. (If you need to brush up your knowledge use favorite search engine - there is plenty of information on the subject.)

The following links may be helpful:

Chapter 9 of Microsoft SQL Server 2012 T-SQL Fundamentals.
Transactions and Concurrency in ADO.NET.
Microsoft Virtual Academy has relevant courses
- Developing Microsoft SQL Server Databases. Chapter 04 - Managing Transactions.
- Querying with Transact-SQL. Chapter 11 - Error Handling and Transactions.

Explicit Transactions

Command types generated by both the SqlCommandProvider and the SqlProgrammabilityProvider have a constructor which accepts connection instance and optionally transaction information. This conforms to familiar ADO.NET conventions for command constructors.

 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:

open System
open System.Data.SqlClient

type CurrencyCode = 
    SqlEnumProvider<"SELECT Name, CurrencyCode FROM Sales.Currency", connectionString>

type InsertCurrencyRate = SqlCommandProvider<"
        INSERT INTO Sales.CurrencyRate 
        VALUES (@currencyRateDate, @fromCurrencyCode, @toCurrencyCode, 
                @averageRate, @endOfDayRate, DEFAULT) 
    ", connectionString>
do
    //Don't forget `use` binding to properly scope transactoin.
    //It guarantees rollback in case of unhandled exception. 

    use conn = new SqlConnection(connectionString)
    conn.Open()
    use tran = conn.BeginTransaction()
    
    //Implicit assumption that
    assert (tran.Connection = conn)
    //Supply connection and transaction 
    use cmd = new InsertCurrencyRate(conn, tran)

    let recordsInserted = 
        cmd.Execute(
            currencyRateDate = DateTime.Today, 
            fromCurrencyCode = "USD", 
            toCurrencyCode = "GBP", 
            averageRate = 0.63219M, 
            endOfDayRate = 0.63219M) 

    assert (recordsInserted = 1)

    // Invoke Commit otherwise transaction will be disposed (roll-backed) when out of scope
    tran.Commit()

Note, that Connection property of the transaction instance has to match the connection supplied up at the first position.

Most often transactions are used in combination with data modification commands (INSERT, UPDATE, DELETE, MERGE). Commands based on SELECT statements or calls to a stored procedure (function) can join a transaction as well, but generally do not do so if they stand alone.

 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: 
50: 
51: 
52: 
53: 
54:

type AdventureWorks = SqlProgrammabilityProvider<connectionString>

do
    use conn = new SqlConnection(connectionString)
    conn.Open()
    //bump up isolation level to serializable
    use tran = conn.BeginTransaction(Data.IsolationLevel.Serializable)
    let jamesKramerId = 42

    let businessEntityID, jobTitle, hireDate = 
        //Include SELECT in transaction
        //Note that inline definition requires both design time connection string
        // and runtime connection object
        use cmd = new SqlCommandProvider<"
            SELECT 
	            BusinessEntityID
	            ,JobTitle
	            ,HireDate
            FROM 
                HumanResources.Employee 
            WHERE 
                BusinessEntityID = @id
            ", connectionString, ResultType.Tuples, SingleRow = true>(conn, tran)

        jamesKramerId |> cmd.Execute |> Option.get

    assert("Production Technician - WC60" = jobTitle)
    
    let newJobTitle = "Uber " + jobTitle

    use updatedJobTitle = new AdventureWorks.HumanResources.uspUpdateEmployeeHireInfo(conn, tran)
    let recordsAffrected = 
        updatedJobTitle.Execute(
            businessEntityID, 
            newJobTitle, 
            hireDate, 
            RateChangeDate = DateTime.Now, 
            Rate = 12M, 
            PayFrequency = 1uy, 
            CurrentFlag = true 
        )
    
    let updatedJobTitle = 
        // Static Create factory method can also be used to pass connection and/or transaction
        // It provides better intellisense. See a link below
        // https://github.com/Microsoft/visualfsharp/issues/449
        use cmd = new AdventureWorks.dbo.ufnGetContactInformation(conn, tran)
        //Use ExecuteSingle if you're sure it return 0 or 1 rows
        let result = cmd.ExecuteSingle(PersonID = jamesKramerId) 
        result.Value.JobTitle.Value

    assert(newJobTitle = updatedJobTitle)

    tran.Commit()

Implicit a.k.a Ambient Transactions

It can become tedious to pass around a connection or transaction pair over and over again. Fortunately, the .NET BCL class TransactionScope was designed to address such tediousness. The basic idea is that all database connections opened within specific scope are included in that transaction. Thus, the example above can be re-written as follows:

 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:

open System.Transactions
do
    use tran = new TransactionScope()

    let jamesKramerId = 42

    let businessEntityID, jobTitle, hireDate = 
        use cmd = new SqlCommandProvider<"
            SELECT 
	            BusinessEntityID
	            ,JobTitle
	            ,HireDate
            FROM 
                HumanResources.Employee 
            WHERE 
                BusinessEntityID = @id
            ", connectionString, ResultType.Tuples, SingleRow = true>(connectionString)

        jamesKramerId |> cmd.Execute |> Option.get

    assert("Production Technician - WC60" = jobTitle)
    
    let newJobTitle = "Uber " + jobTitle

    use updatedJobTitle = new AdventureWorks.HumanResources.uspUpdateEmployeeHireInfo(connectionString)
    let recordsAffrected = 
        updatedJobTitle.Execute(
            businessEntityID, 
            newJobTitle, 
            hireDate, 
            RateChangeDate = DateTime.Now, 
            Rate = 12M, 
            PayFrequency = 1uy, 
            CurrentFlag = true 
        )
    
    let updatedJobTitle = 
        use cmd = new AdventureWorks.dbo.ufnGetContactInformation(connectionString)
        //Use ExecuteSingle on sproc/function generated types
        //if you're sure it return 0 or 1 rows
        let result = cmd.ExecuteSingle(PersonID = jamesKramerId) 
        result.Value.JobTitle.Value

    assert(newJobTitle = updatedJobTitle)

    tran.Complete()

Although very convenient, TransactionScope has some pitfalls and therefore requires a good understanding of what happens behind the scenes. Make sure to read the General Usage Guidelines to avoid common mistakes.

There are two kind of issues you might run into when using TransactionScope:

Unexpectedly Distributed Transactions

Distributed Transactions spell all kind of trouble. They are rarely required and should be avoided in most cases. Strictly speaking this problem is not specific to TransactionScope, but it can be exacerbated by automatic Transaction Management Escalation, which thus makes it annoying easy to fall into the trap.

If a local transaction was accidently promoted to distributed it should be considered a design problem. It's generally best to have a simple check in your code right before commit to reveal the issue:

1: 
2: 
3: 
4: 
5: 
6: 
7:

do
    use tran = new TransactionScope()
    //your transaction logic here
    let isDistributed = Transaction.Current.TransactionInformation.DistributedIdentifier <> Guid.Empty
    if isDistributed 
    then invalidOp "Unexpected distributed transaction."
    else tran.Complete()

TIP SQL Server can use multiple SQLConnections in a TransactionScope without escalating, provided the connections are not open at the same time (which would result in multiple "physical" TCP connections and thus require escalation).

TIP The value of the Enlist key from SqlConnection.ConnectionString property determines the auto-enlistment behavior of connection instance.

TransactionScope + AsyncExecute

Another tricky problem involves combining TransactionScope with asynchronous execution. TransactoinScope has thread affinity. To propagate the transaction context to another thread, .NET 4.5.1 introduced TransactionScopeAsyncFlowOption.Enabled which needs to be passed into the TransactionScope constructor. Unfortunately if you are stuck with .NET Framework prior to version 4.5.1 the only way to combine TransactionScope with AsyncExecute is explicit transactions.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24:

do
    use tran = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)
    
    use cmd = new SqlCommandProvider<"
        INSERT INTO Sales.CurrencyRate 
        VALUES (@currencyRateDate, @fromCurrencyCode, @toCurrencyCode, 
                @averageRate, @endOfDayRate, @modifiedDate) 
    ", connectionString>(connectionString)

    let today = DateTime.Now.Date

    let recordsInserted = 
        cmd.AsyncExecute(
            currencyRateDate = today, 
            fromCurrencyCode = CurrencyCode.``US Dollar``, 
            toCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            averageRate = 0.63219M, 
            endOfDayRate = 0.63219M, 
            modifiedDate = today) 
        |> Async.RunSynchronously

    assert (recordsInserted = 1)

    tran.Complete()

DataTable Updates/Bulk Load

Statically typed data tables generated either by SqlProgrammabilityProvider or by SqlCommandProvider with ResultType.DataTable have two helper methods Update and BulkCopy to send changes back into the database. Both methods accept connection and transaction to support explicit transactions.

The example above which inserts a new USD/GBP currency rate can be re-written as

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21:

do
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    let today = DateTime.Now
    currencyRates.AddRow(
            CurrencyRateDate = today, 
            FromCurrencyCode = CurrencyCode.``US Dollar``, 
            ToCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            AverageRate = 0.63219M, 
            EndOfDayRate = 0.63219M)

    use conn = new SqlConnection(connectionString)
    conn.Open()
    use tran = conn.BeginTransaction()

    let recordsAffected = currencyRates.Update(conn, tran)
    assert (recordsAffected = 1)
    
    //or Bulk Load
    //currencyRates.BulkCopy(conn, transaction = tran)

    tran.Commit()

Same as above with implicit transaction.

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

do
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    let today = DateTime.Now
    currencyRates.AddRow(
            CurrencyRateDate = today, 
            FromCurrencyCode = CurrencyCode.``US Dollar``, 
            ToCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            AverageRate = 0.63219M, 
            EndOfDayRate = 0.63219M)

    use tran = new TransactionScope()

    let recordsAffected = currencyRates.Update()
    assert (recordsAffected = 1)
    
    //or Bulk Load
    //currencyRates.BulkCopy()

    tran.Complete()

namespace System

namespace System.Data

namespace System.Data.SqlClient

type CurrencyCode = obj

Full name: Transactions.CurrencyCode

val connectionString : string

Full name: Transactions.connectionString

type InsertCurrencyRate = obj

Full name: Transactions.InsertCurrencyRate

val conn : SqlConnection

Multiple items
type SqlConnection =
  inherit DbConnection
  new : unit -> SqlConnection + 1 overload
  member BeginTransaction : unit -> SqlTransaction + 3 overloads
  member ChangeDatabase : database:string -> unit
  member Close : unit -> unit
  member ConnectionString : string with get, set
  member ConnectionTimeout : int
  member CreateCommand : unit -> SqlCommand
  member DataSource : string
  member Database : string
  member EnlistDistributedTransaction : transaction:ITransaction -> unit
  ...

Full name: System.Data.SqlClient.SqlConnection

--------------------
SqlConnection() : unit
SqlConnection(connectionString: string) : unit

SqlConnection.Open() : unit

val tran : SqlTransaction

SqlConnection.BeginTransaction() : SqlTransaction
SqlConnection.BeginTransaction(transactionName: string) : SqlTransaction
SqlConnection.BeginTransaction(iso: Data.IsolationLevel) : SqlTransaction
SqlConnection.BeginTransaction(iso: Data.IsolationLevel, transactionName: string) : SqlTransaction

property SqlTransaction.Connection: SqlConnection

val cmd : InsertCurrencyRate

val recordsInserted : int

Multiple items
type DateTime =
  struct
    new : ticks:int64 -> DateTime + 10 overloads
    member Add : value:TimeSpan -> DateTime
    member AddDays : value:float -> DateTime
    member AddHours : value:float -> DateTime
    member AddMilliseconds : value:float -> DateTime
    member AddMinutes : value:float -> DateTime
    member AddMonths : months:int -> DateTime
    member AddSeconds : value:float -> DateTime
    member AddTicks : value:int64 -> DateTime
    member AddYears : value:int -> DateTime
    ...
  end

Full name: System.DateTime

--------------------
DateTime()
   (+0 other overloads)
DateTime(ticks: int64) : unit
   (+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: DateTimeKind) : unit
   (+0 other overloads)

property DateTime.Today: DateTime

SqlTransaction.Commit() : unit

type AdventureWorks = obj

Full name: Transactions.AdventureWorks

Multiple items
namespace System.Data

--------------------
namespace Microsoft.FSharp.Data

field Data.IsolationLevel.Serializable = 1048576

val jamesKramerId : int

val businessEntityID : obj

val jobTitle : string

val hireDate : obj

val cmd : IDisposable

type ResultType =
  | Records = 0
  | Tuples = 1
  | DataTable = 2
  | DataReader = 3

Full name: FSharp.Data.ResultType

ResultType.Tuples: ResultType = 1

module Option

from Microsoft.FSharp.Core

val get : option:'T option -> 'T

Full name: Microsoft.FSharp.Core.Option.get

val newJobTitle : string

val updatedJobTitle : IDisposable

val recordsAffrected : obj

property DateTime.Now: DateTime

val updatedJobTitle : string

val result : obj

namespace System.Transactions

val tran : TransactionScope

Multiple items
type TransactionScope =
  new : unit -> TransactionScope + 7 overloads
  member Complete : unit -> unit
  member Dispose : unit -> unit

Full name: System.Transactions.TransactionScope

--------------------
TransactionScope() : unit
TransactionScope(scopeOption: TransactionScopeOption) : unit
TransactionScope(transactionToUse: Transaction) : unit
TransactionScope(scopeOption: TransactionScopeOption, scopeTimeout: TimeSpan) : unit
TransactionScope(scopeOption: TransactionScopeOption, transactionOptions: TransactionOptions) : unit
TransactionScope(transactionToUse: Transaction, scopeTimeout: TimeSpan) : unit
TransactionScope(scopeOption: TransactionScopeOption, transactionOptions: TransactionOptions, interopOption: EnterpriseServicesInteropOption) : unit
TransactionScope(transactionToUse: Transaction, scopeTimeout: TimeSpan, interopOption: EnterpriseServicesInteropOption) : unit

TransactionScope.Complete() : unit

val isDistributed : bool

type Transaction =
  member Clone : unit -> Transaction
  member DependentClone : cloneOption:DependentCloneOption -> DependentTransaction
  member Dispose : unit -> unit
  member EnlistDurable : resourceManagerIdentifier:Guid * enlistmentNotification:IEnlistmentNotification * enlistmentOptions:EnlistmentOptions -> Enlistment + 1 overload
  member EnlistPromotableSinglePhase : promotableSinglePhaseNotification:IPromotableSinglePhaseNotification -> bool
  member EnlistVolatile : enlistmentNotification:IEnlistmentNotification * enlistmentOptions:EnlistmentOptions -> Enlistment + 1 overload
  member Equals : obj:obj -> bool
  member GetHashCode : unit -> int
  member IsolationLevel : IsolationLevel
  member Rollback : unit -> unit + 1 overload
  ...

Full name: System.Transactions.Transaction

property Transaction.Current: Transaction

property Transaction.TransactionInformation: TransactionInformation

property TransactionInformation.DistributedIdentifier: Guid

Multiple items
type Guid =
  struct
    new : b:byte[] -> Guid + 4 overloads
    member CompareTo : value:obj -> int + 1 overload
    member Equals : o:obj -> bool + 1 overload
    member GetHashCode : unit -> int
    member ToByteArray : unit -> byte[]
    member ToString : unit -> string + 2 overloads
    static val Empty : Guid
    static member NewGuid : unit -> Guid
    static member Parse : input:string -> Guid
    static member ParseExact : input:string * format:string -> Guid
    ...
  end

Full name: System.Guid

--------------------
Guid()
Guid(b: byte []) : unit
Guid(g: string) : unit
Guid(a: int, b: int16, c: int16, d: byte []) : unit
Guid(a: uint32, b: uint16, c: uint16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : unit
Guid(a: int, b: int16, c: int16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : unit

field Guid.Empty

val invalidOp : message:string -> 'T

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

val today : DateTime

property DateTime.Date: DateTime

Multiple items
type Async
static member AsBeginEnd : computation:('Arg -> Async<'T>) -> ('Arg * AsyncCallback * obj -> IAsyncResult) * (IAsyncResult -> 'T) * (IAsyncResult -> unit)
static member AwaitEvent : event:IEvent<'Del,'T> * ?cancelAction:(unit -> unit) -> Async<'T> (requires delegate and 'Del :> Delegate)
static member AwaitIAsyncResult : iar:IAsyncResult * ?millisecondsTimeout:int -> Async<bool>
static member AwaitTask : task:Task<'T> -> Async<'T>
static member AwaitWaitHandle : waitHandle:WaitHandle * ?millisecondsTimeout:int -> Async<bool>
static member CancelDefaultToken : unit -> unit
static member Catch : computation:Async<'T> -> Async<Choice<'T,exn>>
static member FromBeginEnd : beginAction:(AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg:'Arg1 * beginAction:('Arg1 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * beginAction:('Arg1 * 'Arg2 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * arg3:'Arg3 * beginAction:('Arg1 * 'Arg2 * 'Arg3 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromContinuations : callback:(('T -> unit) * (exn -> unit) * (OperationCanceledException -> unit) -> unit) -> Async<'T>
static member Ignore : computation:Async<'T> -> Async<unit>
static member OnCancel : interruption:(unit -> unit) -> Async<IDisposable>
static member Parallel : computations:seq<Async<'T>> -> Async<'T []>
static member RunSynchronously : computation:Async<'T> * ?timeout:int * ?cancellationToken:CancellationToken -> 'T
static member Sleep : millisecondsDueTime:int -> Async<unit>
static member Start : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions * ?cancellationToken:CancellationToken -> Task<'T>
static member StartChild : computation:Async<'T> * ?millisecondsTimeout:int -> Async<Async<'T>>
static member StartChildAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions -> Async<Task<'T>>
static member StartImmediate : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartWithContinuations : computation:Async<'T> * continuation:('T -> unit) * exceptionContinuation:(exn -> unit) * cancellationContinuation:(OperationCanceledException -> unit) * ?cancellationToken:CancellationToken -> unit
static member SwitchToContext : syncContext:SynchronizationContext -> Async<unit>
static member SwitchToNewThread : unit -> Async<unit>
static member SwitchToThreadPool : unit -> Async<unit>
static member TryCancelled : computation:Async<'T> * compensation:(OperationCanceledException -> unit) -> Async<'T>
static member CancellationToken : Async<CancellationToken>
static member DefaultCancellationToken : CancellationToken

Full name: Microsoft.FSharp.Control.Async

--------------------
type Async<'T>

Full name: Microsoft.FSharp.Control.Async<_>

static member Async.RunSynchronously : computation:Async<'T> * ?timeout:int * ?cancellationToken:Threading.CancellationToken -> 'T

val currencyRates : obj

val recordsAffected : int

FSharp.Data.SqlClient

Transactions

Explicit Transactions

Implicit a.k.a Ambient Transactions

Unexpectedly Distributed Transactions

TransactionScope + AsyncExecute

DataTable Updates/Bulk Load