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.
