This article is half-done without your Comment! *** Please share your thoughts via Comment ***
THE SERIALIZABLE Isolation level is one kind of extended version of the REPEATABLE READ Isolation level of the SQL Server.
Yesterday, I have published REPEATABLE READ in which you get the same result by executing same SELECT statement because during your reading operation other cannot update the data, but others can insert the new records.
Now, with the SERIALIZABLE Isolation level, you cannot modify the data while another transaction is reading the same data.
You cannot even read uncommitted data.
Other transactions cannot insert new rows with the key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
The lock also works basis on the key value range.
For example, you are selecting your data between ID 200 and ID 500 so all these data has been locked, and another transaction cannot perform INSERT or UPDATE in this range data until the completion of the reading process.
Now, test the REPEATABLE READ isolation level:
First, create a table with sample data:
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE tbl_Employee ( EmpID INTEGER PRIMARY KEY ,EmpName VARCHAR(50) ) GO INSERT INTO tbl_Employee VALUES (1,'Anvesh'),(2,'Neevan') ,(3,'Roy'),(5,'Martin') GO |
Open a new query window or session and executing below script:
1 2 3 4 5 6 7 |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT *FROM tbl_Employee WHERE EmpID BETWEEN 2 AND 5 WAITFOR DELAY '00:00:15' ROLLBACK |
During the delay of 15 seconds, Open a new query window or session and try to UPDATE data which are in key range:
1 |
UPDATE tbl_Employee SET EmpName ='Loother' WHERE EmpID=2 |
The result is, You cannot UPDATE the data between ID 2 and ID 5.
During the delay of 15 seconds, Open a new query window or session and try to INSERT a record which is in key range:
1 2 3 |
INSERT INTO tbl_Employee VALUES (4,'Henny') GO |
The result is, you cannot INSERT the data with ID 4 because it is between ID 2 and ID 5.
Important Note: here, The Primary Key is mandatory on the ID column because without a primary key it locks the whole table so range value should be a Primary Key.