English
Menu

Database Basics

Which correctly describes two-phase locking (2PL), used for concurrency control of transactions?

  1. AA transaction may alternate between acquiring and releasing locks any number of times. This guarantees that deadlocks never occur
  2. BLocks are taken only for writes, not for reads. This prevents every problem, including dirty reads
  3. COnce a transaction releases any lock, it acquires no new locks afterwards. This guarantees that concurrent execution gives the same result as serial execution, but deadlocks can still occur
Show answer and explanation

Answer: C. Once a transaction releases any lock, it acquires no new locks afterwards. This guarantees that concurrent execution gives the same result as serial execution, but deadlocks can still occur

Under two-phase locking (2PL), each transaction has two phases, a phase in which it acquires the locks it needs one after another (the growing phase) and a phase in which it releases them (the shrinking phase), and once it starts releasing locks, it acquires no new ones. If every transaction follows this rule, the result of concurrent execution is guaranteed to be the same as executing the transactions one at a time in some order (serializability). However, it cannot prevent a deadlock, in which two transactions each wait for the other to release a lock, so the DBMS detects deadlocks and handles them, for example by rolling back one of the transactions. In practice, strict two-phase locking, which holds exclusive locks until commit, is commonly used; it also prevents other transactions from reading uncommitted data.

  • A. Alternating between acquiring and releasing is not two-phase locking. Deadlocks can also occur under two-phase locking.
  • B. Without read locks, a transaction can read data that another transaction is in the middle of updating, so dirty reads and other problems are not prevented.
  • C. Correct. Acquiring no new locks after releasing begins guarantees serializability, but deadlocks can still occur.
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. 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?
  2. 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?
  3. 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?
  4. 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?
  5. 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?