Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.3 Transactions (Deadlock)

Sample Question


Choose three appropriate strategies to avoid deadlocks.

  1. Ensure each transaction is completed as quickly as possible.

  2. Once a lock is acquired, hold it for as long as possible.

  3. Aim to execute as many transactions as possible simultaneously.

  4. When locking multiple resources, maintain the same order of acquiring locks for each transaction.

  5. Avoid acquiring unnecessary locks.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

Deadlock is a term used not only in databases but also in other software. It generally refers to a situation where two processes are waiting for each other to finish, resulting in neither being able to proceed. For instance, consider a scenario where Transaction A has acquired a lock on Table X and Transaction B has acquired a lock on Table Y, and Transaction A tries to lock Table Y. Since Table Y is locked by B, it cannot acquire the lock and goes into a waiting state for release. If B then tries to lock Table X, it cannot acquire the lock because Table X is locked by A, and it also goes into a waiting state for release. In other words, A is waiting for B to finish, and B is waiting for A to finish, so neither can proceed.

PostgreSQL has a feature that automatically detects deadlocks and resolves them by forcibly terminating one of the transactions. However, one transaction will end in failure, and it is always better to avoid causing a deadlock. Therefore, it is important to understand how to prevent deadlocks.

To simplify the discussion, if there are no lock conflicts, there will be no deadlocks. Therefore, the basic strategy to avoid deadlocks is not to acquire unnecessary locks, to release acquired locks as quickly as possible, to ensure that individual transactions end as quickly as possible, and not to execute too many transactions simultaneously.

In the deadlock example above, Transaction A tried to acquire locks in the order of X to Y, and Transaction B tried to acquire locks in the order of Y to X. However, if Transaction B also tried to acquire the lock on X first, the one that failed to acquire the lock on X would wait for the other to finish. But the lock on Table Y would always succeed, so there would be no deadlock. Aligning the order in which locks are acquired across transactions in this way is an important technique to avoid deadlocks.

Therefore, the correct answers are A, D, and E.