Transaction A executes UPDATE orders SET status='shipped' WHERE id=1, then continues running other operations. Transaction B tries to read row id=1. Which statement is correct?
ATransaction B can read the row because Transaction A's UPDATE statement has already completed
BTransaction B must wait because Transaction A still holds the exclusive lock on that row until it commits or rolls back
CTransaction B can proceed using a shared lock since it is only reading, not modifying
DTransaction B's wait will automatically time out when the UPDATE statement finishes
Under pessimistic concurrency control and two-phase locking, a lock acquired during a transaction is held until COMMIT or ROLLBACK — not released when the individual statement finishes. Transaction A's UPDATE acquired an exclusive lock on row id=1; that lock persists for the entire duration of Transaction A, regardless of how many more operations follow. Transaction B must wait for Transaction A to commit or abort. This is the most common misconception: conflating 'statement complete' with 'lock released.'
Question 2 Multiple Choice
Transaction A locks row X then waits for row Y. Transaction B locks row Y then waits for row X. What will the database do?
ABoth transactions will eventually proceed once the database serializes their lock requests
BA deadlock will be detected; the database will abort one transaction to break the cycle so the other can proceed
CBoth transactions will wait indefinitely until an administrator manually kills one
DThe second lock request in each transaction will immediately fail with a lock-not-available error
This is a classic deadlock: each transaction holds a resource the other needs, and neither can proceed. Databases handle deadlocks through automatic detection — periodically checking for cycles in the wait-for graph — and resolution by aborting one transaction (typically the one with the least work or lowest cost to retry). The aborted transaction receives an error and can be retried. This is not an administrator task; the database engine handles it automatically. Preventing deadlocks in application code requires acquiring locks in a consistent order.
Question 3 True / False
Pessimistic concurrency control is called 'pessimistic' because it acquires locks upfront, assuming conflicts are likely, rather than checking for conflicts at commit time.
TTrue
FFalse
Answer: True
Correct. The 'pessimistic' label reflects the assumption that another transaction might interfere — so you prevent interference by locking before you read or write. The contrasting approach, optimistic concurrency control, assumes conflicts are rare: it allows transactions to proceed without locks and only checks at commit time whether any conflict actually occurred. Pessimistic is safer for high-contention workloads; optimistic is better when conflicts are infrequent.
Question 4 True / False
A shared (read) lock prevents other transactions from reading the same row simultaneously.
TTrue
FFalse
Answer: False
Shared locks allow concurrent reads — multiple transactions can hold shared locks on the same row at the same time without blocking each other. Reads do not conflict with reads. A shared lock only blocks *exclusive locks* (writes): a transaction trying to modify a row that others are reading must wait for the readers to release their shared locks. This is why read-heavy workloads can achieve significant concurrency even under pessimistic locking.
Question 5 Short Answer
Why can a long-running transaction significantly reduce database throughput even if it never actually conflicts with other transactions?
Think about your answer, then reveal below.
Model answer: Under two-phase locking, any lock acquired during a transaction is held until commit or abort — even if the locked row is never touched again. A transaction that acquires a lock early and runs for minutes holds that lock the entire time, blocking every other transaction that needs the same row, whether or not an actual conflict would occur.
This is the fundamental cost of pessimistic concurrency control: locking is conservative. The system doesn't know whether a conflict will occur, so it prevents all potential conflicts. The result is that long transactions act as bottlenecks, serializing access to locked resources even during periods when no concurrent modification is happening. Keeping transactions short is the standard mitigation.