Lock-Based Protocols & Basic 2PL MCQs: 12 Solved Questions with Explanations

Basic 2PL looks simple until a question combines phase boundaries with lock compatibility, granularity, serializability, deadlock or rollback. Classify each statement by the rule it tests, then trace schedules and boundary updates explicitly.

Start with the phase rule, then work through schedule analysis and applied update ordering. If a rule feels unfamiliar, use the GATE CS Exam Preparation route to place it within the wider DBMS syllabus; answer each question before reading the explanation.

Basic 2PL rules to fix before attempting the MCQs

During the growing phase, a transaction may acquire locks but release none. The first unlock starts the shrinking phase, when it may release locks but acquire no new one. The last acquisition is its lock point. Basic 2PL guarantees conflict serializability, but not deadlock freedom or cascading rollback prevention.

Valid: L1X(A), W1(A), L1S(B), R1(B), U1(A), U1(B), C1. Its lock point is L1S(B), with both unlocks later. Invalid: L1X(A), W1(A), U1(A), L1S(B), R1(B), U1(B), because L1S(B) follows the first unlock and breaks the phase discipline.

If T1 holds S(R), grant T2 another S(R) immediately, but make T3 wait for X(R). For broader anomaly and protocol practice, use DBMS Concurrency Problems MCQs.

Basic 2PL MCQs 1 to 3: phases, purpose and guarantees

Question 1

In context of 2-phase locking protocol in DBMS, a transaction issues lock requests in ______ phase and unlock requests in ______ phase.

• A. Growing, Shrinking

• B. Shared, Exclusive

• C. Wait, Wound

• D. Explicit, Implicit

Correct answer: A. Growing, Shrinking.

Growth acquires locks; shrinkage releases them. Other pairs describe lock modes or deadlock handling.

Question 2

Two-phase locking protocol in a database management system is:

• A. a concurrency mechanism that is not deadlock free

• B. a recovery protocol used for restoring a database after a crash

• C. Any update to the system log done in 2-phases

• D. not effective in Database

Correct answer: A. a concurrency mechanism that is not deadlock free.

Basic 2PL is concurrency control, not recovery or logging. Circular waits mean it is not deadlock free.

Question 3

Which of the following statements about the Two Phase Locking (2PL) protocol is/are TRUE?

• A. 2PL permits only serializable schedules

• B. With 2PL, a transaction always locks the data item being read or written just before every operation and always releases the lock just after the operation

• C. With 2PL, once a lock is released on any data item inside a transaction, no more locks on any data item can be obtained inside that transaction

• D. A deadlock is possible with 2PL

Correct answer: A, C and D.

A is the guarantee, C the shrinking rule and D a possibility. B may release before a later acquisition, violating 2PL.

Lock-based protocol MCQs 4 to 6: granularity and compatibility

Question 4

Which level of locking provides the highest degree of concurrency in a relational data base?

• A. Page

• B. Table

• C. Row

• D. Page, table and row level locking allow the same degree of concurrency

Correct answer: C. Row.

Row locks let different rows proceed together. Finer locks increase overhead, but row locking maximizes concurrency.

Question 5

A locked database file can be

• A. Accessed by only one user

• B. Modified by users with the correct password

• C. Used to hide sensitive information

• D. Updated by more than one user

Correct answer: A. Accessed by only one user.

An exclusive file lock allows one holder to access the file while conflicting users wait. Passwords and hiding data solve authentication or confidentiality, not concurrency.

Question 6

If transaction A holds a shared lock on R and if transaction B also requests a shared lock on R, it will -

• A. result in a deadlock situation

• B. immediately be granted

• C. immediately be rejected

• D. be granted as soon as it is released by A

Correct answer: B. immediately be granted.

S with S is compatible because both are reads. An X conflict would force waiting.

Basic 2PL MCQs 7 to 9: serializability, deadlock and rollback

Question 7

For the schedule given below involving transactions T1 and T2, which of the following is correct? 1. T1: Read(A) 2. T2: Read(B) 3. T1: Write(A) 4. T2: Read(A) 5. T2: Write(A) 6. T2: Write(B) 7. T1: Read(B) 8. T1: Write(B)

• A. The schedule is serializable and can occur under the 2PL protocol.

• B. The schedule is serializable but cannot occur under the 2PL protocol.

• C. The schedule is not serializable but can occur under the 2PL protocol.

• D. The schedule is not serializable and cannot occur under the 2PL protocol.

Correct answer: D. The schedule is not serializable and cannot occur under the 2PL protocol.

A conflicts give T1 -> T2; B conflicts give T2 -> T1. This cycle is not conflict serializable, so 2PL cannot produce it.

Question 8

Given below are two statements: Which of the following concurrency control protocol ensures both conflict serializability and freedom from deadlock? Statement I: Two phase locking Statement II: Timestamp ordering In the light of the above statements, choose the most appropriate answer from the options given below:

• A. Both Statement I and Statement II are correct

• B. Both Statement I and Statement II are incorrect

• C. Statement I is correct but Statement II is incorrect

• D. Statement I is incorrect but Statement II is correct

Correct answer: D. Statement I is incorrect but Statement II is correct.

2PL allows circular wait, so I fails. Timestamp ordering aborts instead of waiting, giving serializability without deadlock.

Question 9

Which of the following statements is wrong?

• A. 2-phase Locking Protocols suffer from deadlocks

• B. Time-Stamp Protocols suffer from more aborts

• C. Time-Stamp Protocols suffer from cascading roll back whereas 2-Phase locking Protocol do not

• D. None of these

Correct answer: C. Time-Stamp Protocols suffer from cascading roll back whereas 2-Phase locking Protocol do not.

A is true; B reflects timestamp aborts. C is wrong because basic 2PL can cascade; strict 2PL holds write locks until commit or abort.

Lock-based protocol MCQs 10 to 12: lock purpose and applied updates

Question 10

In multiuser database if two users wish to update the same record at the same time, they are prevented from doing so by

• A. Jamming

• B. Password

• C. Documentation

• D. Record lock

Correct answer: D. Record lock.

A record lock makes one conflicting update wait while the other transaction finishes. Jamming, passwords and documentation do not serialize writes to the same record.

Question 11

Which of the following is correct according to the technology deployed by DBMS?

• A. Pointers are used to maintain transactional integrity and consistency

• B. Cursors are used to maintain transactional integrity and consistency

• C. Locks are used to maintain transactional integrity and consistency

• D. More than one of the above

• E. None of the above

Correct answer: C. Locks are used to maintain transactional integrity and consistency.

Locks regulate conflicting reads and writes. Pointers and cursors do not provide concurrency control.

Question 12

A company maintains records of sales made by its salespersons and pays them commission based on each individual's total sales made in a year. This data is maintained in a table with following schema: salesinfo = (salespersonid, totalsales, commission) In a certain year, due to better business results, the company decides to further reward its salespersons by enhancing the commission paid to them as per the following formula: If commission < = 50000, enhance it by 2% If 50000 < commission < = 100000, enhance it by 4% If commission > 100000, enhance it by 6% The IT staff has written three different SQL scripts to calculate enhancement for each slab, each of these scripts is to run as a separate transaction as follows: T1 Update salesinfo Set commission = commission * 1.02 Where commission < = 50000; T2 Update salesinfo Set commission = commission * 1.04 Where commission > 50000 and commission is < = 100000; T3 Update salesinfo Set commission = commission * 1.06 Where commission > 100000;Which of the following options of running these transactions will update the commission of all salespersons correctly

• A. Execute T1 followed by T2 followed by T3

• B. Execute T2, followed by T3; T1 running concurrently throughout

• C. Execute T3 followed by T2; T1 running concurrently throughout

• D. Execute T3 followed by T2 followed by T1

Correct answer: D. Execute T3 followed by T2 followed by T1.

At 49,500, T1 gives 49,500 x 1.02 = 50,490; T2 then gives 50,490 x 1.04 = 52,509.60, applying twice. Highest-first T3, T2, T1 prevents movement into a pending slab.

Basic 2PL traps and their rules

Classify each as lock mode, phase, guarantee or schedule. For schedules, draw one node per transaction and an edge per conflict. For requests, check S/S, S/X, X/S, X/X. Reject claims that basic 2PL is deadlock free. Continue with DBMS Transaction MCQs: 12 Solved (ACID, Locking) for wider practice.

Lock-based protocols and Basic 2PL: the next practice step

Retest Q3, Q6, Q7, Q8, Q9 and Q12 from memory, without answers. Say the rule aloud: phase boundary for Q3, compatibility for Q6, graph cycle for Q7, wait or abort for Q8, basic or strict for Q9, and descending slabs for Q12.

For a sequenced DBMS route covering concurrency control, transactions and recovery, continue with GATE Guidance by Sanchit Sir. The short version is simple: 2PL controls when locks may be acquired and released. It gives conflict serializability, but lock mode, granularity, deadlock handling and recovery strength remain separate questions.