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

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.


Invalid entry,please enter valid data.

Loading