Deadlock detection and prevention in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a deadlock in a database system?

A deadlock happens when two or more transactions wait forever for each other to release locks, so none can proceed.

intermediate

How does PostgreSQL detect deadlocks?

PostgreSQL periodically checks for cycles in the waiting transactions graph to find deadlocks and then aborts one transaction to break the cycle.

beginner

Name one common method to prevent deadlocks.

One method is to always acquire locks in a consistent order across transactions to avoid circular waiting.

intermediate

What happens when PostgreSQL detects a deadlock?

PostgreSQL cancels one of the transactions involved in the deadlock and returns an error to that transaction, allowing others to continue.

advanced

Why is deadlock prevention better than detection in some cases?

Prevention avoids deadlocks before they happen, improving performance and user experience by not having to roll back transactions.

What causes a deadlock in PostgreSQL?

A Two transactions waiting on each other's locks

B A transaction running too long

C A missing index

D A syntax error in SQL

Deadlocks occur when transactions wait on each other's locks, creating a cycle with no progress.

How does PostgreSQL resolve a detected deadlock?

A It cancels one transaction involved in the deadlock

B It restarts the database server

C It ignores the deadlock and waits

D It locks all tables

PostgreSQL cancels one transaction to break the deadlock cycle.

Which practice helps prevent deadlocks?

A Acquiring locks in random order

B Acquiring locks in a consistent order

C Using only read locks

D Disabling transactions

Consistent lock order prevents circular waiting and deadlocks.

What is a sign of a deadlock in PostgreSQL logs?

A NOTICE: query completed

B WARNING: slow query

C ERROR: deadlock detected

D INFO: connection established

PostgreSQL logs 'ERROR: deadlock detected' when it finds a deadlock.

Why might deadlock prevention be preferred over detection?

A It uses more CPU

B It disables locking

C It allows deadlocks to happen

D It avoids transaction rollbacks

Prevention avoids deadlocks and the need to rollback transactions, improving performance.

Explain what a deadlock is and how PostgreSQL detects it.

Think about transactions waiting on each other's locks.

Describe two ways to prevent deadlocks in database transactions.

Focus on how to avoid circular waiting and long lock holding.