Read phenomena (dirty reads, phantom reads) in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all rows from the orders table.

SQL

SELECT * FROM [1];

Drag options to blanks, or click blank then click option'

Acustomers

Bemployees

Cproducts

Dorders

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting from the wrong table like 'customers' or 'products'.

Forgetting to specify the table name.

The orders table contains the data we want to read, so we select from it.

2fill in blank

medium

Complete the code to start a transaction with the isolation level that prevents dirty reads.

SQL

SET TRANSACTION ISOLATION LEVEL [1];

Drag options to blanks, or click blank then click option'

AREPEATABLE READ

BREAD UNCOMMITTED

CREAD COMMITTED

DSERIALIZABLE

Attempts:

3 left

💡 Hint

Common Mistakes

Using READ UNCOMMITTED which allows dirty reads.

Confusing REPEATABLE READ or SERIALIZABLE with preventing dirty reads.

The READ COMMITTED isolation level prevents dirty reads by only reading committed data.

3fill in blank

hard

Fix the error in the transaction code to prevent phantom reads.

SQL

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL [1];
-- perform queries
COMMIT;

Drag options to blanks, or click blank then click option'

AREAD UNCOMMITTED

BREPEATABLE READ

CREAD COMMITTED

DDIRTY READ

Attempts:

3 left

💡 Hint

Common Mistakes

Using READ COMMITTED which does not prevent phantom reads.

Using invalid isolation level names like DIRTY READ.

The REPEATABLE READ isolation level prevents phantom reads by ensuring consistent reads within a transaction.

4fill in blank

hard

Fill both blanks to write a query that counts orders with amount greater than 100 inside a repeatable read transaction.

SQL

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL [1];
SELECT COUNT(*) FROM orders WHERE amount [2] 100;
COMMIT;

Drag options to blanks, or click blank then click option'

AREPEATABLE READ

B>

C<

DREAD COMMITTED

Attempts:

3 left

💡 Hint

Common Mistakes

Using READ COMMITTED which allows phantom reads.

Using '<' instead of '>' for the amount condition.

We use REPEATABLE READ to prevent phantom reads and > to select orders with amount greater than 100.

5fill in blank

hard

Fill all three blanks to write a transaction that prevents dirty reads and phantom reads, and selects orders with status 'pending'.

SQL

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL [1];
SELECT * FROM orders WHERE status = '[2]' AND amount [3] 0;
COMMIT;

Drag options to blanks, or click blank then click option'

ASERIALIZABLE

Bpending

C>

DREAD COMMITTED

Attempts:

3 left

💡 Hint

Common Mistakes

Using READ COMMITTED which does not prevent phantom reads.

Using wrong status value or comparison operator.

SERIALIZABLE is the strictest isolation level preventing dirty and phantom reads. We filter orders with status 'pending' and amount greater than 0.