Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

SAVE TRANSACTION AND SAVEPOINT

SAVE TRANSACTION AND SAVEPOINT:

Creates points within groups of transactions in which to ROLLBACK.Save points offer a mechanism to roll back portions of transactions. A user can decide and set a savepoint, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled.

Using Save Tran command we can create save point in SQL SERVER ,following example explains Save point in detail.

Syntax:

SAVE { TRAN | TRANSACTION } { savepoint_name |
@savepoint_variable }
[ ; ]

 

Example (SAVE TRANSACTION AND SAVEPOINT):

Original Data before transaction:

T-Sql Query with transaction :


 USE LocalTest
SELECT 'Bfore BEGIN TRAN Parent ', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 0
 
BEGIN TRAN Parent
    SELECT 'After BEGIN TRAN Parent', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 1
    Update Employee
         SET PayScale=PayScale+20 where Country='USA'
    SAVE TRAN PayScaleUS  -- Mark a save point
    SELECT 'After SAVE TRAN PayScaleUS', @@TRANCOUNT
   -- The value of @@TRANCOUNT is still 1
 
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
        -- The value of @@TRANCOUNT is 2
        Update Employee
         SET PayScale=PayScale+50 where Country='India'
        SAVE TRAN PayScaleIND  -- Mark a save point
        SELECT 'After SAVE TRAN PayScaleIND', @@TRANCOUNT
        -- The value of @@TRANCOUNT is still 2
    ROLLBACK TRAN PayScaleUS
 
    SELECT 'After ROLLBACK TRAN PayScaleUS', @@TRANCOUNT
     -- The value of @@TRANCOUNT is still 2
 
    SELECT * FROM Employee
 
IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN
    SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT
    -- to 0.
END


After Executing Transaction:

Transaction Isolation levels in SQL Server

Transaction Isolation levels in SQL Server:

To ensure quality of your data in RDBMS transaction isolation level is important.

This article will cover the five transaction isolation settings - READ UNCOMMITTED, READ COMMITTED (locking), READ COMMITTED (snapshot), REPEATABLE READ and SERIALIZATION.

Syntax:

SET TRANSACTION ISOLATION
LEVEL
    {READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]
 

 

READ UNCOMMITTED

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

 For more info ….http://www.codechef4u.com/post/2015/04/05/read-uncommitted

READ COMMITTED:

This is the default isolation level and means selects will only return committed data. This prevents dirty reads.

For more info ….http://www.codechef4u.com/post/2015/04/05/read-committed

REPEATABLE READ:

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

 For more info ….http://www.codechef4u.com/post/2015/04/07/repeatable-read1

 SERIALIZABLE:

 This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads.

For more info ….http://www.codechef4u.com/post/2015/04/07/serializable

 

SNAPSHOT:

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

 

To use the snapshot isolation level you need to enable it on the database by running the following command


Syntax: 


ALTER DATABASE DatabseName

SET ALLOW_SNAPSHOT_ISOLATION ON

 

 Example:

 

ALTER DATABASE LocalTest 

SET ALLOW_SNAPSHOT_ISOLATION ON

 

--Query1  

BEGIN TRAN 

SELECT * FROM Employee 

WAITFOR DELAY '00:00:10' 

SELECT * FROM Employee 

ROLLBACK

 

--Query2

INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)

 

VALUES ('Anushka', 'England','English','London',5) 

 

 

SERIALIZABLE

Serializable Isolation in SQL Server

    ·     Statements cannot read data that has been modified but not yet committed by other transactions.
·     No other transactions can modify data that has been read by the current transaction until the current transaction completes.
·      Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

 This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.

Example:

--Query1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE   
BEGIN TRAN 
SELECT * FROM Employee 
WAITFOR DELAY '00:00:10' 
SELECT * FROM Employee 
ROLLBACK
--Query2
INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)
 
VALUES ('Anushka', 'England','English','London',5) 

Result:

Execute query1 and query 2 query 1 both Select statement reterns same data ,reason Serialaizable transaction wont allow to insert new records until the current transaction completes.