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.
Now with the SERIALIZABLE Isolation level, you cannot modify the data while another transaction is reading the same data.
For SELECT-only transactions, use the SERIALIZABLE isolation level when you do not want to see the other transaction commits during your transaction.
For UPDATE and DELETE transactions, SERIALIZABLE isolation prevents concurrent modification of the same data row; it should therefore be used with caution.
Create a table with few sample records:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE tbl_Employee ( EmpID INTEGER ,EmpName VARCHAR(50) ); INSERT INTO tbl_Employee VALUES (1,'Anvesh'),(2,'Neevan') ,(3,'Roy'),(4,'Martin'); |
Execute in this current session. e.g. Session – A:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; UPDATE tbl_Employee SET EmpName ='Varlin' WHERE EmpID=4; SELECT *FROM tbl_Employee; empid | empname -------+--------- 1 | Anvesh 2 | Neevan 3 | Roy 4 | Varlin |
Execute in anohter session, e.g. Session – B:
|
1 2 3 4 5 6 7 8 9 10 |
UPDATE tbl_Employee SET EmpName ='Loother' WHERE EmpID=3; SELECT *FROM tbl_Employee; empid | empname -------+--------- 1 | Anvesh 2 | Neevan 4 | Varlin 3 | Loother |
Execute in anohter session, e.g. Session – C:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
INSERT INTO tbl_Employee VALUES (5,'Mahi'); SELECT *FROM tbl_Employee; empid | empname -------+--------- 1 | Anvesh 2 | Neevan 4 | Martin 3 | Loother 5 | Mahi |
Execute COMMIT on first Current Session – A:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--Nothing happen to this Query and Data is as it is. SELECT *FROM tbl_Employee; empid | empname -------+--------- 1 | Anvesh 2 | Neevan 3 | Roy 4 | Varlin -- Now Execute COMMIT: COMMIT TRANSACTION; |
