Repeatable read behavior in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a transaction with repeatable read isolation level.

PostgreSQL

BEGIN TRANSACTION ISOLATION LEVEL [1];

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

Aread committed

Bread uncommitted

Crepeatable read

Dserializable

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'read committed' which allows non-repeatable reads.

Using 'serializable' which is stricter than repeatable read.

The repeatable read isolation level ensures that all reads within the transaction see a consistent snapshot of the database.

2fill in blank

medium

Complete the code to set the transaction isolation level to repeatable read using SET command.

PostgreSQL

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

Confusing 'repeatable read' with 'read committed'.

Using 'serializable' which is stricter.

Using SET TRANSACTION ISOLATION LEVEL repeatable read sets the isolation level for the current transaction.

3fill in blank

hard

Fix the error in the transaction start statement to use repeatable read isolation level.

PostgreSQL

BEGIN TRANSACTION ISOLATION [1];

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

Alevel repeatable read

Bisolation level repeatable read

Crepeatable_read

Drepeatable read

Attempts:

3 left

💡 Hint

Common Mistakes

Using underscores instead of spaces.

Including extra keywords in the blank.

The correct syntax is BEGIN TRANSACTION ISOLATION LEVEL repeatable read; but since LEVEL is missing, the correct fix is to add the full phrase. Here, the blank replaces only the isolation level name, so 'repeatable read' is correct.

4fill in blank

hard

Fill both blanks to complete the query that shows the current transaction isolation level.

PostgreSQL

SHOW [1] [2];

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

Atransaction

Bisolation

Clevel

Dmode

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'transaction mode' which is invalid.

Using 'isolation mode' which is invalid.

The correct command is SHOW transaction isolation level; which displays the current isolation level.

5fill in blank

hard

Fill all three blanks to complete the SQL that sets repeatable read isolation level and starts a transaction.

PostgreSQL

[1] TRANSACTION [2] LEVEL [3];

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

ABEGIN

BISOLATION

Crepeatable read

DSTART

Attempts:

3 left

💡 Hint

Common Mistakes

Using BEGIN without ISOLATION keyword.

Using 'repeatable_read' with underscore.

The correct syntax is START TRANSACTION ISOLATION LEVEL repeatable read; which begins a transaction with the specified isolation level.

Practice

1.

What does the REPEATABLE READ isolation level guarantee in PostgreSQL?

easy

A. It ensures all queries in a transaction see the same data snapshot.

B. It allows reading uncommitted changes from other transactions.

C. It locks all rows in the database for the transaction duration.

D. It automatically commits after each query in the transaction.

Solution

1. Step 1: Understand Repeatable Read isolation

   Repeatable Read ensures that all queries in a transaction see the same snapshot of data, preventing changes made by others from appearing during the transaction.

2. Step 2: Compare options with definition

   It ensures all queries in a transaction see the same data snapshot. matches this definition exactly. Options B, C, and D describe behaviors not related to Repeatable Read.

3. Final Answer:

   It ensures all queries in a transaction see the same data snapshot. -> Option A

4. Quick Check:

   Repeatable Read = Same snapshot [OK]

Hint: Repeatable Read = stable snapshot during transaction [OK]

Common Mistakes:

• Confusing Repeatable Read with Read Uncommitted
• Thinking it locks all rows
• Assuming auto-commit after each query

2.

Which of the following is the correct way to start a transaction with REPEATABLE READ isolation level in PostgreSQL?

BEGIN;
-- your queries
COMMIT;

easy

A. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN;

B. BEGIN ISOLATION LEVEL REPEATABLE READ;

C. BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

D. BEGIN; SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Solution

1. Step 1: Recall correct syntax for setting isolation level

   In PostgreSQL, you start the transaction with BEGIN, then set the isolation level for that transaction using SET TRANSACTION ISOLATION LEVEL.

2. Step 2: Match options to syntax

   BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; correctly shows BEGIN; then SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; sets isolation before BEGIN which is invalid. BEGIN ISOLATION LEVEL REPEATABLE READ; uses invalid syntax. BEGIN; SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ; sets session level, not transaction level.

3. Final Answer:

   BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -> Option C

4. Quick Check:

   Set isolation after BEGIN = BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; [OK]

Hint: Set isolation level after BEGIN with SET TRANSACTION [OK]

Common Mistakes:

• Trying to set isolation before BEGIN
• Using BEGIN with isolation level directly
• Confusing session and transaction level commands

3.

Consider this sequence in PostgreSQL with REPEATABLE READ isolation:

-- Transaction 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- returns 100

-- Transaction 2
BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Back to Transaction 1
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

What will be the result of the second SELECT in Transaction 1?

medium

A. Error due to concurrent update

B. 100

C. NULL

D. 200

Solution

1. Step 1: Understand snapshot behavior in Repeatable Read

   Transaction 1 sees a consistent snapshot from its start. Changes committed by Transaction 2 after Transaction 1 began are not visible.

2. Step 2: Apply to the SELECT query

   The first SELECT returned 100. The second SELECT in the same transaction will also return 100, ignoring the update committed by Transaction 2.

3. Final Answer:

   100 -> Option B

4. Quick Check:

   Repeatable Read = same snapshot = 100 [OK]

Hint: Repeatable Read ignores later commits in same transaction [OK]

Common Mistakes:

• Expecting updated value 200 inside same transaction
• Thinking it causes error on concurrent update
• Assuming NULL if data changed

4.

Given this transaction in PostgreSQL:

BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE products SET stock = stock - 1 WHERE id = 10;
SELECT stock FROM products WHERE id = 10;
COMMIT;

But you get an error: ERROR: syntax error at or near "ISOLATION". What is the fix?

medium

A. Change to BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

B. Remove the isolation level, just use BEGIN;

C. Use START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

D. Use SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ; before BEGIN

Solution

1. Step 1: Identify syntax error cause

   PostgreSQL does not support specifying isolation level directly in BEGIN statement.

2. Step 2: Correct syntax to set isolation level

   You must first BEGIN; then run SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; before queries.

3. Final Answer:

   Change to BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -> Option A

4. Quick Check:

   Set isolation after BEGIN, not inside [OK]

Hint: Set isolation after BEGIN with SET TRANSACTION [OK]

Common Mistakes:

• Trying to put isolation level inside BEGIN
• Using unsupported START TRANSACTION syntax
• Setting session level instead of transaction level

5.

You want to run multiple SELECT queries in a transaction and ensure the data does not change during the transaction, but you also want to allow other transactions to update data concurrently without blocking. Which isolation level should you choose in PostgreSQL?

hard

A. READ COMMITTED

B. READ UNCOMMITTED

C. SERIALIZABLE

D. REPEATABLE READ

Solution

1. Step 1: Understand isolation levels and concurrency

   READ COMMITTED allows seeing changes committed during the transaction, so data can change between queries. SERIALIZABLE is strict and may block or abort concurrent updates. READ UNCOMMITTED is not supported in PostgreSQL.

2. Step 2: Match requirement to isolation level

   REPEATABLE READ provides a stable snapshot for all queries in the transaction, preventing data changes from appearing, while allowing concurrent updates without blocking reads.

3. Final Answer:

   REPEATABLE READ -> Option D

4. Quick Check:

   Stable snapshot + concurrency = REPEATABLE READ [OK]

Hint: Repeatable Read = stable snapshot without blocking writes [OK]

Common Mistakes:

• Choosing SERIALIZABLE which blocks more
• Thinking READ COMMITTED prevents data changes
• Assuming READ UNCOMMITTED exists in PostgreSQL