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 can not 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 based on key value range,
For example, you are selecting your data between ID 200 and ID 500 so all this data has been locked and another transaction cannot perform INSERT or UPDATE in this range data until the completion of the reading transaction.
Now test this REPEATABLE READ isolation level:
First, create a table with sample data:
CREATE TABLE tbl_Employee
EmpID INTEGER PRIMARY KEY
INSERT INTO tbl_Employee
Open a new query window or session and executing this script:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT *FROM tbl_Employee WHERE EmpID BETWEEN 2 AND 5
WAITFOR DELAY '00:00:15'
During this 15 second delay, Open a new query window or session and try to UPDATE data which are in key range:
UPDATE tbl_Employee SET EmpName ='Loother' WHERE EmpID=2
The result is, You cannot UPDATE the data between ID 2 and ID 5.
During this 15 second delay, Open a new query window or session and try to INSERT a record which is in key range:
INSERT INTO tbl_Employee
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 primary key it locks the whole table so range value should be a Primary Key.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.