English
Menu

Database Basics

Transaction T1 took an exclusive lock on row A and then requested a lock on row B, while T2 took an exclusive lock on row B and then requested a lock on row A, so neither can proceed. What does a DBMS typically do when it detects this situation?

  1. AIt keeps both waiting; one of the locks will eventually be released on its own
  2. BIt rolls back one transaction to release its locks and lets the other proceed
  3. CIt commits the updates of both T1 and T2 as they are
Show answer and explanation

Answer: B. It rolls back one transaction to release its locks and lets the other proceed

A state in which two or more transactions wait for each other to release locks, so that none can proceed, is called a deadlock. Waiting alone never resolves it, so many DBMSs detect a deadlock, for example when a cycle appears in the wait-for graph that shows which transaction is waiting for which, and roll back one of them (the victim) to release its locks. The rolled-back transaction is retried later. Another approach is to set an upper limit (timeout) on lock waits and roll back a transaction that exceeds it. As a preventive measure, it is effective to have every transaction acquire locks in a fixed order, such as row A before row B.

  • A. Each is waiting for the other, so the locks are never released by waiting alone.
  • B. Correct. Rolling back one transaction to release its locks is the usual response.
  • C. To commit, a transaction must obtain the locks it needs and finish its work; it cannot commit while stuck waiting.
Practice this topic Back to topic

Related calculators

SQL PracticeRun SQL in your browser and check the results, with 10 practice exercises that grade your answers

Books on Database Basics Ad

As an Amazon Associate I earn from qualifying purchases.

More Database Basics questions

  1. When updating data, many DBMSs record the changes in a log (journal) and write that log to nonvolatile storage before the database itself (WAL: write-ahead logging). What is the main reason for this approach?
  2. There are transactions T1 to T5, and one checkpoint was taken before a failure occurred. T1 started and committed before the checkpoint. T2 and T3 started before the checkpoint; T2 committed after the checkpoint but before the failure, and T3 had not committed at the time of the failure. T4 and T5 started after the checkpoint; T4 committed before the failure, and T5 had not committed. In recovery after the failure, which transactions must be rolled forward using the log?
  3. A B+ tree database index was created on the member_no column of a member table with 1 million rows. Which correctly pairs the effect that can generally be expected with its cost?
  4. For a member table with 1 million rows, consider creating a B+ tree index on each of the columns member_no (all different), prefecture (47 values) and gender (2 values, split roughly in half). When searching by specifying a value for one column in the WHERE clause, on which column is an index likely to be least effective?
  5. Among the databases called NoSQL, which is generally a correct description of the characteristics of a key-value store?