Sample Exam Questions

From the objective of OSS-DB Exam Gold
- Performance Tuning - Performance-related
parameters (log management)

Sample Question


Select the statement that gives a correct explanation
of the deadlock-related GUC parameter deadlock_timeout.

  1. When the time specified by deadlock_timeout has elapsed, it can be determined that deadlock has occurred.
  2. The occurrence of the deadlock can be avoided by adjusting the deadlock_timeout value.
  3. If the deadlock_timeout value is reduced, CPU load will decrease as the number of lock wait processes decrease.
  4. Deadlock is something that should be avoided through application development, and the deadlock_timeout value should ideally be set to exceed your typical transaction time.
  5. Deadlock detection will not be executed automatically with the default deadlock_timeout setting. 

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

Answer and Explanation

Data update transactions obtain a data lock before the update to prevent data inconsistencies occurring as a result of competing transactions.
While data is locked by a transaction, subsequent transactions will wait for the lock to be released before updating the same data.
When multiple transactions are waiting for data locked by each other transaction to be released, none of these transactions can proceed. This is referred to as a deadlock.
In PostgreSQL, there is a function to detect deadlock automatically. When a deadlock is detected, the system will resolve the deadlock by forcibly terminating one of the transactions.
deadlock_timeout specifies how long to wait after it has gone into a lock wait state before starting the deadlock detection process. The default value is one second.
The deadlock detection process is started after the specified time has passed. This does not imply that a deadlock has actually occurred.
Reducing the deadlock_timeout value will speed up deadlock detection; however, this will result in the deadlock detection process running even though deadlock is not actually occurring.
Increasing the deadlock_timeout value will do the opposite and will reduce the number of unnecessary deadlock detection processes; however, this will increase the wait time for detection of an actual deadlock.
Deadlock detection is a CPU-intensive process and should be run as infrequently as possible.
In environments where deadlock occurs frequently, it may be possible to resolve a deadlock swiftly by decreasing the deadlock_timeout value. However, the correct way of handling this is to improve the application side to ensure that a deadlock occurs as infrequently as possible and to increase the deadlock_timeout to ensure that the CPU-intensive deadlock detection process is not run unnecessarily.

Therefore, the correct answer is D.