Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Transaction Isolation

Explore how to manage transaction isolation in MySQL to ensure data consistency during concurrent access. This lesson guides you through using isolation levels such as SERIALIZABLE to prevent phantom reads, and demonstrates how locking reads and SELECT ... FOR UPDATE can control concurrent modifications. You will gain practical experience with real-time scenarios involving multiple sessions to understand transaction behavior and concurrency control.

We'll cover the following...

Solution

You can try it yourself using the steps below in the terminal:

  • Click “Click to Connect...” in the terminal widget to establish a connection. Once connected, click the “+” icon next to Terminal 1 in the top left corner of the terminal pane. This will open another terminal window. We need two terminals so you can observe how multiple sessions interact in real time. We will refer to these two SQL terminals as “Session A” and “Session B.”

  • The prepended command or start script will be executed upon connecting to the terminal. It will start the MySQL service and have the setup for OnlineStore ready for your use.

  • Type mysql and click to connect to MySQL in both terminals. Once you are inside the MySQL shell, select the OnlineStore database in both terminals by running the following:

USE OnlineStore;

Select the isolation level and start the transaction. In both terminals, run these queries to select the isolation level and start the transaction:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

In Terminal 1 (Session A), run the summary reads to establish stable ranges for this transaction:

-- Counts that must not see phantoms while the transaction stays open
SELECT COUNT(*) AS InStock
FROM   Products
WHERE  Stock > 0;
SELECT COUNT(*) AS PendingOrders
FROM   Orders
WHERE  DeliveryStatus = 'Pending';

This captures the current counts and, under SERIALIZABLE, protects the ranges defined by Stock > 0 and DeliveryStatus = 'Pending' for the duration of Session A’s transaction.

In Terminal 2 (Session B), run the queries below to try inserting rows that would match those predicates:

-- Insert a new in-stock product that would affect InStock
INSERT INTO Products (ProductName, CategoryID, Price, Stock)
VALUES ('Lock Test Product A', NULL, 9.99, 5);
-- Insert a pending order that would affect PendingOrders
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount, DeliveryStatus)
VALUES (1, CURRENT_DATE(), 49.99, 'Pending');

These inserts will wait because Session A’s SERIALIZABLE range reads hold next-key locks ...