Exercise_Transactions_Isolation

Exercise: Transactions & Isolation Levels

Belongs to: Transactions_CAP_Scaling · Duration: ~50 min · Work in pairs

You do not need a cluster. One MySQL server is enough: we simulate two users with two separate connections.

---

0. Setup (5 min)

Open two terminal windows and connect to MySQL in each, e.g.:

docker exec -it mysql mysql -u root -p

We call them Session A (left window) and Session B (right window).

Use two separate CLI windows. In GUI tools, several tabs often share one connection, and then the experiment does not work.

Run once (in either session):

CREATE DATABASE tx_lab;
USE tx_lab;
CREATE TABLE product (
  id    INT PRIMARY KEY,
  name  VARCHAR(50),
  price DECIMAL(6,2),
  stock INT
);
INSERT INTO product VALUES (1, 'Keyboard', 40.00, 10);

In both sessions: USE tx_lab; and check the current level with:

SELECT @@transaction_isolation;

Rule for all tasks: run the steps exactly in the given order, alternating between A and B. Before each step, write down what you expect. Then write down what you observe.

---

1. Dirty Read (8 min)

Step	Session A	Session B
1	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2		START TRANSACTION;
3		UPDATE product SET price = 99 WHERE id = 1;
4	SELECT price FROM product WHERE id = 1; → ?
5		ROLLBACK;
6	SELECT price FROM product WHERE id = 1; → ?

Now repeat everything with Session A set to READ COMMITTED.

---

2. Non-Repeatable Read (8 min)

Step	Session A	Session B
1	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2	START TRANSACTION;
3	SELECT price FROM product WHERE id = 1; → ?
4		UPDATE product SET price = 50 WHERE id = 1;
5	SELECT price FROM product WHERE id = 1; → ?
6	COMMIT;

Reset: UPDATE product SET price = 40 WHERE id = 1;

Now repeat with Session A set to REPEATABLE READ.

---

3. Serializable (5 min)

Step	Session A	Session B
1	SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2	START TRANSACTION;
3	SELECT price FROM product WHERE id = 1;
4		UPDATE product SET price = 60 WHERE id = 1; → what happens?
5	COMMIT;	→ what happens now?

Reset: UPDATE product SET price = 40 WHERE id = 1;, then set both sessions back to REPEATABLE READ.

---

4. Lost Update (12 min)

Two clerks sell one keyboard each. The application reads the stock, subtracts 1, and writes the new value back.

Step	Session A	Session B
1	START TRANSACTION;
2	SELECT stock FROM product WHERE id = 1; → ?
3		START TRANSACTION;
4		SELECT stock FROM product WHERE id = 1; → ?
5	UPDATE product SET stock = 9 WHERE id = 1;
6		UPDATE product SET stock = 9 WHERE id = 1; → ?
7	COMMIT;
8		COMMIT;
9	SELECT stock FROM product WHERE id = 1; → ?

Two keyboards were sold. Is the stock correct?

Fix it: reset the stock to 10 and repeat. This time, both sessions use SELECT stock FROM product WHERE id = 1 FOR UPDATE; in steps 2 and 4, and each session writes the value it read minus 1. What changes?

---

5. ACID or BASE? CP or AP? (10 min, no computer)

Scenario	ACID or BASE?	CP or AP?	Reason (one sentence)
Online banking transfer
Like counter on a social media post
Cinema seat reservation
Temperature sensors sending data every second
Online shopping cart (before checkout)
DNS

---

Hand-in

1. Your expected vs. observed values for tasks 1–4.
2. Short answers:
   • a) Which anomaly did you see in task 1, 2 and 4?
   • b) Why did Session B have to wait in task 3? What is the price of SERIALIZABLE?
   • c) Why does FOR UPDATE fix the lost update?
   • d) Give a third fix for task 4 that needs no SELECT at all.
3. The completed table from task 5.