22.1 Creating Transactions

A transaction is a sequence of operations performed on a single unit. There are several properties possed by the transactions. The properties are known as Atomicity, Consistency, Durability and Isolations. They are abbreviated as ACID properties of transaction.

Atomicity: It means that either all the modifications are performed or none are performed.

Consistency: It means that all the data is present in the consistent state when the transaction is completed.

Isolation: It means that any data modification made through the concurrent transactions can be isolated from each other. The transaction can be in either in the initial state or committed state. It cannot be present in the intermediate state.

Durability: It means that any changes made to the data are permanent in the system. The changed data remains in the system even after the system failure.

The following features are provided by the SQL Server for using the ACID properties.

1) Transaction management: It ensures the atomicity and consistency of all the transactions.

2) Locking: It prevents the transaction durability and isolation

The following ways are used to implement the transactions in SQL Server.

1) Autocommit transaction

2) Implicit transaction

3) Explicit transaction

1) Autocommit transaction

It is the default transaction management mode of the SQL Server. The transactions are automatically committed or rolled back. A statement is committed if it is successful, rollback if aborted.

2) Implicit transaction

An implicit transaction does not require user to define the start of the transaction. User only needs to commit or rollback the transaction. The implicit transaction is turned ON when user needs to implement it.

The following statements defined the on and off mode of the implicit transaction.     

SET IMPLICIT_TRANSACTIONS ON;

SET IMPLICIT_TRANSACTIONS OFF;

Consider an example to demonstrate the use of implicit transaction.            

           SET IMPLICIT TRANSACTION ON;
           Insert into empdata ( EmpName, EmpRole )
           values ( ‘Sam’, ‘Manager’ );

          COMMIT TRANSACTION;

          INSERT INTO empdata values ( ‘Peter’, ‘Lead’ );
          SELECT * from empdata;

          COMMIT TRANSACTION;

3) Explicit Transaction

An explicit transaction contains both start and end defined explicitly by the user. The transaction consists of the following statement.

BEGIN TRANSACTION: It is used as the start point of the transaction.

COMMIT TRANSACTION: It is used to save the changes permanently to the database.

ROLLBACK TRANSACTION: It is used to undo the transactions

SAVE TRANSACTION: It is used to create save point making it possible for the user to rollback the transaction.

Consider the following example to demonstrate the explicit transaction.

         BEGIN TRAN tran1
         UPDATE Employee
         SET salary = 20000
         WHERE emprole=’Manager’

         UPDATE Employee
         SET salary = 30000
         WHERE emprole=’Lead’
        COMMIT TRAN tran1

In the above statement, the transaction named as tran1 is created. It is used to update the salaries of the employees.

22.2 Reverting Transactions

There are certain times when all the transactions do not execute successfully by the user. The transaction is in the invalid state. User needs to revert the statement that has been successfully executed to maintain consistency.

The ROLLBACK transaction is used to rollback the explicit and implicit transaction. The syntax for the ROLLBACK statement is as shown below:

      ROLLBACK [ TRANSACTION ] [ transaction_name | @tran_name_variable

      | savepoint_name | @savepoint_variable ] ]

Where

transaction_name is the name assigned to the transaction

@tran_name is the name of the user defined variable

savepoint_variable is the name of the user defined variable

@savepoint_variable is the name of the user defined variable containing the savepoint name

Consider the following example to view the rollback transaction in SQL.

        BEGIN TRANSACTION tr1
        BEGIN TRY
        UPDATE Department
        SET DeptName=’Production’
        WHERE DeptID=101

        UPDATE EmpAddress SET AddressID=1111
        WHERE EmpID=201
        COMMIT TRANSACTION tr1

        SELECT ‘Transaction executed’
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION tr1
            SELECT ‘Transaction Rollback’
        END CATCH

In the above statement, the tr1 transaction is used to update the DeptName of the employee. It also updates the address. The first statement is executed while the second statement is creating an error, hence rollback.

22.3 Implementing Transaction Integrity

When multiple users want to access the database server using the UPDATE or SELECT statement, there is data redundancy or incorrectness in the database.

The concept of locking is used to ensure the transactional integrity. The locking prevents user to change the same data at the same time. The current user of the system has constant view over the resource, from beginning to the end of the operation.

Need for Locking

If there is no locking applied to the data, problems may occur if one transaction uses the same data from the database at the same time. The problems that can occur are explained below:

1) Lost Updates

A lost update occurs when two or more transactions try to modify the same row. Every transaction is not aware of each other. The last update overwrites the previous updates of the transactions. There is a loss of data manipulation performed by the previous transactions.

2) Uncommitted Dependency

An uncommitted dependency is known as dirty read. The problem occurs when transaction queries data from one table when the other transaction is modifying the data. User should not be able to read the values until the changes are finalized.

3) Inconsistent Analysis

An inconsistent analysis problem is known as non – repeatable problem. The problem occurs when the data is changed between simultaneous read by one user.

4) Phantom Read

A phantom read is also known as phantom problem. The problem occurs when the new records are inserted by a user are identified by the transactions that started prior to the INSERT statement.

Locking in SQL Server

SQL Server implements multi – granular locks which allow the user to lock different types of resources at different levels. SQL Server automatically locks resources at a level appropriate to the transaction. SQL Server used the following lock modes for locking the objects.

1) Shared Locks

2) Exclusive Locks

3) Update Locks

4) Intent Locks

5) Schema Locks

6) Bulk update Locks

1) Shared Locks

They allow concurrent transactions to read a resource. If there shared locks present on the transaction, no other transaction can modify the data.

2) Exclusive Locks

They are exclusively used to restrict the concurrent transactions from accessing the resource. No other transaction can read or modify the data locked with the exclusive locks.

3) Update Locks

An update lock falls between a shared and an exclusive lock. When there is a physical update, any query acquires an exclusive lock. In the duration of transaction, any other transaction can change the data that user is going to update. An update lock can be acquired.

4) Intent Locks

An Intent lock is used when the server wants to acquire a shared or exclusive lock on some of the resources lower in the hierarchy. When a shared intent lock is implemented at a table level, the transaction will occur on pages or rows within the table.

5) Schema Locks

A schema lock is used when a schema dependent operation is performed on a table. The Schema stability ( Sch – S ) locks are used while executing the queries. It does not block any locks present in the transaction.

6) Bulk Update Locks

A Bulk Update Lock secures the table from any T – SQL statement but multiple bulk update can be performed at the same time.

Controlling Locks

The locks are implemented by default in the SQL Server. If the data to be queried is large, locks can expand from rows to tables and pages. The data will be restricted by other users. The concurrency of data is lost in the database. User might sometimes need to change the lock mode.

To overcome this problem, the isolation levels are used. The following isolation levels are provided by the SQL Server.

1) READ UNCOMMITTED

2) READ COMMITED

3) REPEATABLE READ

4) SNAPSHOT

5) SERIALIZABLE

1) READ UNCOMMITTED

The READ UNCOMMITED isolation level specifies that the transaction can read the data modified by the current transaction but the modifications have yet not been committed. The transactions running in the isolation levels do not perform a share lock on the database, enabling the other transactions to modify the data read by the current object.

When the level is set, the transaction can read the uncommitted data, resulting in the dirty read problem.

2) READ COMMITED

The READ COMMITED level is used to specify the transaction cannot read the data that is being modified by the transaction. When the isolation level is set, other transactions can update the data that has been read by the current transaction.

3) REPEATABLE READ

The REPEATABLE READ isolation level specifies that the transaction cannot read the data that is being modified by the transaction. No other transaction can update the data that has been read by the current transaction until the current transaction completes.

4) SNAPSHOT

The SNAPSHOT isolation level provides every transaction with a snapshot of the current data. Every transaction works and makes changes to the copy of the data. When the transaction is ready to update the changes, it checks whether the data is modified since the time user started working with it.

5) SERIALIZABLE

The SERIALIZABLE isolation level specifies that no transaction can read, modify or insert a new data while the data is being read or updated by the current transaction.

It is the safest isolation level provided by the server. The concurrency is at the lowest level.

22.4 Resolving Deadlocks

A Deadlock is a situation when two or more transactions have locks on separate objects. Every object waits for a lock on the other object to be released. The concurrency of the database objects is decreased due to deadlocks.

The SQL Server helps user to set the priority of the deadlock using the SET DEADLOCK_PRIORITY statement. The syntax for DEADLOCK_PRIORITY statement is as shown below:

      SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }

      where,

      LOW specifies the current session is the victim
      NORMAL specifies that the session returns to the default deadlock handling method
      @deadlock_var is the character variable with the length of characters