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

Question: Transaction Isolation

Understand how to configure transaction isolation levels in MySQL to avoid phantom reads during concurrent data operations. Explore practical examples using the Products and Orders tables to maintain stable read results within the same transaction despite concurrent inserts.

We'll cover the following...

Question

Given the following structure of the Products table:

Field

Type

ProductID

int

ProductName

varchar(50) not null unique

CategoryID

int

Price

decimal(10,2) not null

Stock

int not null

LastRestockDate

date

MonthlySales

int default 0

InventoryTurnoverRate

decimal(5,2) generated stored

Where, ProductID is the primary key and CategoryID is the foreign key referencing the Categories table. The table contains information about products available in the OnlineStore database.

Also, given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where, OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to ...