🏠 Portal 

CREATE TABLE product
(id  INT PRIMARY KEY,
name VARCHAR(10),
price FLOAT
);

INSERT INTO product VALUES (1,'phone',100);
INSERT INTO product VALUES (2,'tv',200);
READ UNCOMMITTED
Terminal 1 Terminal 2 
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT * FROM product WHERE id = 1; 

 
BEGIN;
UPDATE product SET price = 1000 WHERE id = 1;


SELECT  * FROM product  WHERE id = 1;

This would return the uncommited values;

READ COMMITTED
Terminal 1 Terminal 2 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT * FROM product; 

BEGIN;
UPDATE product SET price = 1000 WHERE id = 1;


SELECT  * FROM product;

COMMIT;

SELECT * FROM Product;
Repeatable Read
Terminal 1 Terminal 2 
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM product; 

BEGIN;
 INSERT INTO `product` VALUES (3,'C', 5);
COMMIT;


SELECT  * FROM product;
Serializable
Terminal 1 Terminal 2 
SET SESSION TRANSACTION ISOLATION LEVEL Serializable;
BEGIN;
SELECT COUNT(*) FROM product;
BEGIN; INSERT INTO `product` VALUES (4,'D', 5); COMMIT; 

SELECT  * FROM product;
Example I
Terminal 1 Terminal 2 

SET SESSION TRANSACTION ISOLATION LEVEL Serializable;
BEGIN;


SET SESSION TRANSACTION ISOLATION LEVEL Serializable;
BEGIN;
SELECT COUNT(*) FROM product;



SELECT * FROM product;
INSERT INTO `product` VALUES (6, 'F', 6);
⚠️ Note: After this insert, Terminal 1 will be blocked (it’s waiting for Terminal 2’s lock). 

INSERT INTO `product` VALUES (7, 'G', 7);
⚠️ Note: This will cause a deadlock between Terminal 1 and Terminal 2, because both are waiting on each other’s locks under Serializable isolation level.
Example II
Terminal 1 Terminal 2 
SET SESSION TRANSACTION ISOLATION LEVEL Serializable;
BEGIN;



 SET SESSION TRANSACTION ISOLATION LEVEL Serializable;
BEGIN;

 INSERT INTO `product` VALUES (10,'G', 5);


 INSERT INTO `product` VALUES (10,'H', 6);


   COMMIT;
   

   COMMIT;
Example III
Terminal 1 Terminal 2 
SET SESSION TRANSACTION ISOLATION LEVEL Repeatable Read;
BEGIN;
SELECT  COUNT(*) FROM Product;


 SET SESSION TRANSACTION ISOLATION LEVEL Serializable;
BEGIN;

 INSERT INTO `product` VALUES (11,'G', 5);
COMMIT;
 


SELECT  COUNT(*) FROM Product;
   COMMIT;
Isolation Level dirty read non repeatable reads Phantoms
Read uncommited Y Y Y
Read Commited N Y Y
Repeatable read N N Y*
Serializable N N N

MySQL at REPEATABLE isolation level:

🏠 Portal | 🚀 Getting Started | 📝 SQL Cheat Sheet