Pessimistic and optimistic locking

Transactional isolation is usually implemented by locking whatever is accessed in a transaction. There are two different approaches to transactional locking: Pessimistic locking and optimistic locking.

The disadvantage of pessimistic locking is that a resource is locked from the time it is first accessed in a transaction until the transaction is finished, making it inaccessible to other transactions during that time. If most transactions simply look at the resource and never change it, an exclusive lock may be overkill as it may cause lock contention, and optimistic locking may be a better approach. With pessimistic locking, locks are applied in a fail-safe way. In the banking application example, an account is locked as soon as it is accessed in a transaction. Attempts to use the account in other transactions while it is locked will either result in the other process being delayed until the account lock is released, or that the process transaction will be rolled back. The lock exists until the transaction has either been committed or rolled back.

Pessimistic locking in ADO.NET

One way to deal with optimistic concurrency issues in ADO.NET is to lock records as they are retrieved from the data source. It doesn't take much code, but it does involve some risks. Find out when you should use this procedure and how it works.

Six steps to implement Pessimistic Locking in ADO.NET

Create a transaction by using IsolationLevel of RepeatableRead.

Set the DataAdapter’s SelectCommand property to use the transaction we have created in step #1.

Make the changes to the data.

Set DataAdapter’s Insert, Update, and Delete command properties to use the transaction created in step #1.

Call the DataAdapter’s Update method.

Commit the transaction.

Below is an example using pessimistic locking by using ADO.NET, developer just need to create object of the class mentioned below, select data in DataSet, update it with required changes, then call submitChanges method to save it into database, by using pessimistic locking procedure.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.Common;

using System.Data;

namespace Locking_example

{

    public class DAL

    {

        private String CommandText;

        private SqlConnection ObjSqlConnection;

        private SqlDataAdapter ObjSqlDataAdapter;

        private String connectionString;

        private DataSet Ds;

        private SqlTransaction ObjSqlTransaction;

       

        //Constructor

        public DAL(String ConnectionString)

        {

            this.connectionString = ConnectionString;

            ObjSqlConnection = new SqlConnection(this.connectionString);

            ObjSqlDataAdapter = new SqlDataAdapter();

        }

        public DataSet select(String commandText)

        {

            ObjSqlConnection.Open();

            ObjSqlTransaction = ObjSqlConnection.BeginTransaction(IsolationLevel.RepeatableRead);

            ObjSqlDataAdapter.SelectCommand.CommandText=this.CommandText;

            ObjSqlDataAdapter.SelectCommand.Transaction = ObjSqlTransaction;

            ObjSqlDataAdapter.Fill(Ds);

            return Ds;

        }

        public bool SubmitChanges(DataSet Ds)

        {

            try

            {

                ObjSqlDataAdapter.InsertCommand.Transaction = ObjSqlTransaction;

                ObjSqlDataAdapter.UpdateCommand.Transaction = ObjSqlTransaction;

                ObjSqlDataAdapter.DeleteCommand.Transaction = ObjSqlTransaction;

                ObjSqlDataAdapter.Update(Ds);

                ObjSqlTransaction.Commit();

                ObjSqlConnection.Close();

                return true;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

    }

}

Pessimistic locking in LINQ to SQL

Pessimistic locking is pre-implemented in LINQ to SQL, however we need to user TransactionScope class in order to use pessimistic locking.

If two different instances of same program trying to update a data rows set (collection of LINQ  objects) or even different programs, first program who ever update the selected data will be saved in database, reset of others will get following exception :

Unhandled Exception: System.Data.Linq.ChangeConflictException: Row not found or changed.

Because of the fact that before updating LINQ to SQL first tried to select the same row using a where clause with all old values.

Below is an example of using pessimistic locking using LINQ to SQL and TransactionScope

using (TransactionScope ts = new TransactionScope())

                {

                    Customer newCustomer = new Customer

                    {

                        FirstName = "To be Updated",

                        LastName = "To be Updated",

                        State = "To be Updated",

                        ZipCode = "To be Updated",

                        City = "To be Updated",

                        Address1 = "To be Updated",

                        Address2 = "To be Updated",

                    };

                    customersTable.InsertOnSubmit(newCustomer);

                    try

                    {

                        this.SubmitChanges();

                    }

                    catch (Exception ex)

                    {

                        return null;

                    }

                    CustomerStoreConfiguration newCustomerStoreConfiguration = addSignupConfigurations(newCustomer, storeID, cellPhoneNumber, pin);

                    if (newCustomerStoreConfiguration == null)

                    {

                        ts.Dispose();

                        return null;

                    }

                    ts.Complete();

                    return newCustomerStoreConfiguration;

                }

            }

        }

       

        public void SubmitChanges()

        {

            customersTable.Context.SubmitChanges();

        }

public CustomerStoreConfiguration addSignupConfigurations(Customer newCustomer, int storeID, string cellphoneNumber, string pin)

        {

            Table<CustomerStoreConfiguration> CustomerStoreConfigurationTable = customersTable.Context.GetTable<CustomerStoreConfiguration>();

            CustomerStoreConfiguration newCustomerStoreConfiguration = new CustomerStoreConfiguration

            {

                CellPhoneNumber = cellphoneNumber,

                StoreID = storeID,

                PIN = pin,

                CustomerID = newCustomer.CustomerID,

                EmailAddress = "To_Be_Updated@updateMe.now",

                EnableAllCatelogPromotions = false,

                EnableAllEmailPromotions = false,

                EnableAllRetailFlyerPromotions = false,

                EnableCatelogPromotions = false,

                EnableRetailFlyerPromotions = false,

                EnableSpatialEmailPromotions = false,

                MaxCatelogPromotions = 0,

                MaxEmailPromotions = 0,

                MaxRetailFlyerPromotions = 0,

                MaxMobilePromotions=0

            };

            CustomerStoreConfigurationTable.InsertOnSubmit(newCustomerStoreConfiguration);

            try

            {

                CustomerStoreConfigurationTable.Context.SubmitChanges();

                return newCustomerStoreConfiguration;

            }

            catch (Exception ex)

            {

                return null;

            }

           

        }

Incase of voilation of pessimistic locking user may encounter follwing exception:

Unhandled Exception: System.Data.SqlClient.SqlException: Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.