[Solved] Given two transactions:-- Transaction 1BEGIN;UPDATE accounts SET balance = balance - 100 WHERE id = 1;-- Transaction 2BEGIN;UPDATE accounts SET balance = balance + 100 WHERE id = 2;What is the risk... | PostgreSQL

PostgreSQL - Transactions and Concurrency

Given two transactions:

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Transaction 2
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

What is the risk of deadlock if both transactions update each other's rows in reverse order?

AHigh risk of deadlock due to conflicting row locks

BNo risk because updates are on different rows

CSyntax error will prevent deadlock

DDeadlock only occurs with table locks

Step-by-Step Solution

Solution:

Step 1: Understand row-level locking in updates
Each UPDATE locks the row it modifies. If transactions update rows in reverse order, they wait on each other's locks.
Step 2: Recognize deadlock risk
This waiting cycle creates a deadlock risk, so High risk of deadlock due to conflicting row locks is correct.
Final Answer:
High risk of deadlock due to conflicting row locks -> Option A
Quick Check:
Row lock conflicts = Deadlock risk [OK]

Quick Trick: Updating rows in reverse order risks deadlock [OK]

Common Mistakes:

Thinking different rows mean no deadlock
Confusing syntax errors with deadlocks
Assuming deadlocks only happen with table locks

Master "Transactions and Concurrency" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Given two transactions:

Step 1: Understand row-level locking in updates

Step 2: Recognize deadlock risk

Final Answer:

Quick Check:

Want More Practice?