1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Implementing Transactions in SQL Server

Discussion in 'SQL Server' started by MinalS, Dec 30, 2014.

  1. There are certain conditions when either all the statements are executed successfully or none of them are executed. The data integrity is maintained by the user. The SQL Server helps user to maintain the integrity of data. There are several sources accessing a single resource at a time. To prevent the problem of errors, the concept of locks is introduced. The locks help user to secure the resource when the transaction is executed. Only one transaction executes the resources of the system.

    Creating Transactions



    A transaction can be considered as sequence of operations that are combined together as a single unit. The following properties are performed by the transaction.
    1. Atomicity: The atomicity property states that either all the transactions are performed or none are performed.
    2. Consistency: It states that all the data is in a consistent state after the transaction is executed successfully. The changes are applied and the integrity is ensured.
    3. Isolation: The changes made through the concurrent transactions are isolated from each other. The intermediate state is not valid in isolation.
    4. Durability: It states that the changes in the data are saved permanently in response to the system. The changes in the data are present even after the system fails.
    The SQL Server helps user to implement the transactions in following ways:
    1. Autocommit transaction:
    2. Implicit transaction
    3. Explicit transaction
    1. Autocommit transaction

    The autocommit transaction is defined as the default transaction in SQL Server. The transactions are committed or rolled back. A statement is committed if it is successful and rollback if there is an error.

    2. Implicit transaction

    The implicit transactions are the one where user does not need to define the start of the transaction. The rollback or commit state of the transaction is defined. By default the implicit transaction mode is set to off. User needs to turn it on.

    Every transaction remains in the active state until it is committed or rollback. The transactions are generated until the implicit transaction mode is turned off.

    The following code snippet demonstrates the implicit transaction is turned on.

    Code:
    SET IMPLICIT_TRANSACTION ON; 
    
    Consider the following code snippet to demonstrate the implicit transaction.
    Code:
    
    SET IMPLICIT TRANSACTIONS ON;
    Insert into Account( Account_no, Owner_name ) values ( 123, ‘John’ ), ( 234, ‘Nick’)
    
    COMMIT TRANSACTION;
    
    Insert into Account values( 456, ‘Harry’ );
    
    Select * from Account;
    
    COMMIT TRNASACTION;
    
    
    In the above statement, the implicit transaction mode is turned ON. The first transaction is committed; the second transaction is immediately executed as soon as the insert statement is executed.

    The implicit transaction is turned off by using the following statement.

    Code:
    
    SET IMPLICIT TRANSACTION OFF;
    
    
    3. Explicit Transaction

    An explicit transaction contains the beginning and the end of the transaction defined explicitly. They can also be stated as user defined or user specified transactions.

    The following statements are used in the explicit transactions.
    1. BEGIN TRANSACTION: It can be used as the starting of the transaction.
    2. COMMIT TRANSACTION: It is used for saving the data permanently in the database.
    3. ROLLBACK TRANSACTION: It can be used for undoing the changes made in the database
    4. SAVE TRANSACTION: It is used for maintaining the save points and perform the partial rollback.
    1. BEGIN TRANSACTION

    The BEGIN TRANSACTION is used at the start of the transaction. The syntax for the BEGIN TRANSACTION is as shown below:

    Code:
    
    BEGIN TRAN [SACTION] [ transaction_name | @tran_name_variable ]
    
    
    Where,

    transaction_name is the name of the transaction.

    @tran_name_variable is the name of the variable containing a transaction name.

    2. COMMIT TRANSACTION

    The COMMIT TRANSACTION states the end of the transaction. The syntax for the end of the transaction is as shown below:

    Code:
    
    COMMIT TRAN [ SACTION]  [ transaction_name | @tran_name_variable ]
    
    
    Where,

    transaction_name is the name of the transaction assigned prior to the BEGIN TRANSACTION statement.

    @tran_name_variable is the name of the user defined variable containing the transaction name.

    Consider the following example for demonstrating the transactions in SQL Server.

    Code:
    
    UPDATE StudentInfo
    SET StudID = 101
    where StudName = ‘Nisha’
    
    UPDATE StudentInfo
    SET StudID = 201
    where StudName = ‘Seema’
    
    
    When the server executed the above block of statements, either all are executed or none of them. If there is error in the execution of the statements, it should rollback. The beginning and end of the transaction needs to be defined.

    Code:
    
    	BEGIN TRAN tran1
    	UPDATE StudentInfo
    SET StudID = 101
    where StudName = ‘Nisha’
    
    UPDATE StudentInfo
    SET StudID = 201
    where StudName = ‘Seema’
    COMMIT TRAN tran1
    
    
    In the above block of statements, a transaction named as tran1 is created. It is used for updating the id of the students.

    Rollback transactions



    In a block of statements, all the statements are not executed successfully. In such situation the transaction is in an invalid state. User must revert the statements that are executed successfully for maintaining consistency.

    The ROLLBACK TRANSACTION statement is used for rolling back the implicit or explicit transaction. User can use a save point in the transaction. With the use of save point user can divide the transaction into small parts.

    The syntax for the ROLLBACK TRANSACTION is as shown below:

    Code:
    
    	ROLLBACK [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable | 
    	savepoint_name | @savepoint_variable ] ]
    
    
    where,

    transaction_name is the name of the transaction. It must follow the rules defined for identifiers.

    @tran_name_variable is the name of the user defined variable containing the valid transaction name

    savepoint_name is the name for the savepoint.

    @savepoint_variable is the name of the user defined variable that has the save point name.

    The following example demonstrates the rollback transaction.

    Code:
    
    BEGIN TRANSACTION TRN1
    BEGIN TRY
    UPDATE EmpData
    
    SET Salary = 20000
    Where Role=”Engineer”
    
    UPDATE EmpData
    SET EmpID=345
    Where Role=”Manager”
    COMMIT TRANSACTION TRN1
    
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION TRN1
    END CATCH
    
    
    In the above block of code, the TRN1 transaction updates the salary of the employee. The transaction also updates the ID of the employee. The first statement is executed but the second statement provides an error and hence the transaction is rollback.

    Locks in SQL Server



    The concept of multi granular locking is provided by the SQL Server. It helps user to lock resources at different levels. The server automatically locks the resources at the level of transaction. There are several lock modes available in the SQL Server.

    The lock modes are as explained below:

    1) Shared Locks

    The Shared lock helps the concurrent transactions to read the resource. Once a shared lock is added on a resource no other transaction can modify the resource. The lock is released on the resource after the data is read by the current transaction.

    2) Exclusive Locks

    The exclusive locks are used for restricting the concurrent transactions to access the resource. Other transactions cannot modify or read the data locked through an exclusive lock.

    3) Update Locks

    The update lock performs an intermediate role in context to shared and exclusive locks. Whenever there is any update the query acquires an exclusive lock. Another transaction can change the data in the time interval between the shared and exclusive lock. The update lock can be used for the prevention of data from other transactions.

    4) Intent Locks

    The Intent locks are used when the server wants a shared or exclusive lock on a resource that has a lower hierarchy. When the intent lock is implemented no other transaction acquires the exclusive lock on the table.

    5) Schema Locks

    The schema lock is used when the schema dependent operations are performed on the table. The schema modification locks are used when DDL operation is performed on the table. The schema stability locks are used when the user compiles the query.

    6) Bulk Update Locks

    The bulk update lock is used when user wants to secure the table from other SQL statements. User can perform multiple BULK INSERT statements at a same time.

    Controlling Locks



    The locks in the SQL server are automatically implemented by the system. The server locks every row that user queries. If there are large set of records the locks can grow from the rows to the tables.

    If the query is time consuming for execution, the other user will need to wait for accessing the database objects. The concurrency is not maintained in the database. The lock mode needs to be changing constantly. The isolation levels are specified for resolving the problem.

    Users can user isolation levels for specifying the permissions for other transactions to be modified by other user.

    User can implement isolation levels in the transactions using the SET TRANSACTION ISOLATION LEVEL statement at the start of the transaction.

    The syntax for setting the isolation level is as shown below:

    Code:
    
    SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE } [ ; ]
    BEGIN TRANSACTION
    …..
    …..
    COMMIT TRANSACTION
    
    
    The following isolation levels are supported by the SQL Server.

    1) READ UNCOMMITTED

    The READ UNCOMMITTED isolation level is used to specify that the transaction can read the data modified by the current transaction but the transactions are not yet committed. The transactions do not perform a shared lock on the database object. The objects are not restricted through the use of the exclusive locks.

    Once user sets this isolation level, the transaction can read the uncommitted data and the dirty read problem occurs. It is not effective in terms of data integrity.

    2) READ COMMITTED

    The READ COMMITED isolation level is used when the transaction cannot read the data that is modified by the current transaction. It has an exclusive lock on each UPDATE statement in the transaction. It resolves the issue of dirty read. But there is a problem of phantom read in this isolation level. It is the default isolation level in the transaction.

    The example to demonstrate the READ COMMITED isolation level is as shown below:

    Code:
    
    SET TRANSACTION ISOLATION LEVEL
    READ COMMITTED 
    BEGIN TRANSACTION TR1
    BEGIN TRY
    	UPDATE Employee SET Salary = 40000 where EmpID = 320
    	
    	UPDATE Student SET Grade = ‘A’ where StudID = 212
    	COMMIT TRANSACTION TR1
    
    	PRINT ‘Transaction is executed’
    
    END TRY
    BEGIN CATCH
    	ROLLBACK TRANSACTION TR1
    	PRINT ‘Transaction is roll backed’
    END CATCH
    
    
    In the above code, the isolation level of transaction TR1 is defined as READ COMMITTED. It avoids other transactions from reading the uncommitted updates in the tables available in the server.

    3) REPEATABLE READ

    The REPEATABLE READ isolation level is used to state that the transaction does not read the data modified by the current transaction. The other transactions cannot update the data read by the current transaction. Once the current transaction is completed, user can read the data.

    In the current statement the isolation level uses an exclusive lock. It is placed on the UPDATE statement. A shared lock is placed on the SELECT transaction. The other transactions can insert data. It results into the phantom read problem.

    The following example is used to demonstrate the REPEATABLE READ isolation level.

    Code:
    
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    BEGIN TRANSACTION TR2
    SELECT * FROM Employee
    GO
    SELECT StudID from Student 
    GO
    COMMIT TRANSACTION
    GO
    
    
    In the above code, the isolation level is set to REPEATABLE READ. It avoids other transactions to update the records till the current transaction is completed.

    4) SNAPSHOT

    The SNAPSHOT isolation level is used for providing the snapshot of the current data. Each transaction can update its individual copy of data. When the transaction is ready to update the copy, the data modifications are checked by the user and appropriate changes are made.

    The example to demonstrate the SNAPSHOT isolation level is as shown below:

    Code:
    
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    BEGIN TRANSACTION 
    SELECT StudName FROM Student
    GO
    SELECT EmpRole FROM Employee
    GO
    COMMIT TRANSACTION
    GO
    
    
    In the above code, the isolation level is set to SNAPSHOT. It will create a copy and update the changes later.

    5) SERIALIZABLE

    The SERIALIZBLE isolation level is used to state that no transactions can read, insert or modify the data that is under process by the current transaction.

    It is the safest isolation level provided by the SQL Server. A lock is provided on every transaction statement.

    The following example demonstrates the SERIALIZABLE isolation level.

    Code:
    
    	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    	BEGIN TRAN
    	SELECT * FROM Supplier
    	GO
    	SELECT * FROM Purchaser
    	GO
    	END TRAN
    
    
    In the above code, all the transactions are committed and then user can modify or update on the new data.
     
    shabbir likes this.

Share This Page