MCQs on Concurrency and Locking | PostgreSQL Database
Master concurrency and locking in PostgreSQL with this collection of 30 multiple-choice questions. Explore advanced locking mechanisms, deadlock resolution, MVCC internals, and performance tuning to optimize your PostgreSQL experience.
Topics Covered:
- Advanced Locking Mechanisms
- Deadlocks and Their Resolution
- MVCC (Multi-Version Concurrency Control) Internals
- Performance Tuning for Concurrency
MCQs on Concurrency and Locking
Advanced Locking Mechanisms
- Which of the following is the default locking mode in PostgreSQL?
- A) Row-level lock
- B) Exclusive lock
- C) Share lock
- D) Access share lock
- Which lock is used to prevent other transactions from modifying a table’s schema in PostgreSQL?
- A) Share row exclusive lock
- B) Access exclusive lock
- C) Row exclusive lock
- D) Exclusive lock
- In PostgreSQL, what does the “FOR UPDATE” clause do in a SELECT statement?
- A) Prevents updates to the rows retrieved
- B) Locks the selected rows for update
- C) Prevents concurrent selects
- D) Grants the SELECT privilege
- What type of lock does PostgreSQL acquire when a transaction modifies a row?
- A) Share lock
- B) Row exclusive lock
- C) Exclusive lock
- D) Access share lock
- What is the purpose of the “FOR SHARE” clause in PostgreSQL?
- A) Prevents rows from being updated
- B) Locks rows for reading only
- C) Prevents other transactions from locking the rows for exclusive access
- D) Allows exclusive access to the rows
- Which type of lock does PostgreSQL use to prevent other transactions from modifying a row while it is being updated?
- A) Row exclusive lock
- B) Share lock
- C) Exclusive lock
- D) Access share lock
- What does the “NOWAIT” option in PostgreSQL locking do?
- A) Causes the transaction to wait indefinitely for a lock
- B) Causes the transaction to fail immediately if a lock cannot be acquired
- C) Allows the transaction to proceed without acquiring a lock
- D) Increases the lock timeout
- What type of lock is acquired when a SELECT statement is executed without any locking clause?
- A) Access share lock
- B) Row exclusive lock
- C) Share lock
- D) Exclusive lock
- Which lock is needed to perform a schema modification in PostgreSQL?
- A) Access share lock
- B) Access exclusive lock
- C) Row exclusive lock
- D) Share lock
- Which command can be used to view the current locks held by transactions in PostgreSQL?
- A) SHOW LOCKS;
- B) SELECT * FROM pg_locks;
- C) LIST LOCKS;
- D) SELECT * FROM pg_transaction_locks;
Deadlocks and Their Resolution
- A deadlock occurs when:
- A) Two transactions are competing for the same resource
- B) Two transactions cannot proceed because each is waiting for the other to release a lock
- C) A transaction is waiting for an exclusive lock
- D) A transaction exceeds the timeout limit
- Which of the following is NOT a common cause of deadlocks in PostgreSQL?
- A) Two transactions requesting the same resources in different order
- B) Transaction holding a lock for too long
- C) Inserting data into multiple tables in a transaction
- D) Reading data without acquiring locks
- How does PostgreSQL detect a deadlock?
- A) Using a timeout-based mechanism
- B) Using a waiting queue
- C) By checking for circular dependencies between transactions
- D) By automatically releasing the locks after a predefined time
- What happens when PostgreSQL detects a deadlock?
- A) It kills both transactions involved
- B) It rolls back one transaction to resolve the deadlock
- C) It cancels the transaction with the most recent update
- D) It retries the transaction automatically
- Which of the following is a typical deadlock resolution strategy used by PostgreSQL?
- A) Rolling back the transaction with the smallest number of updates
- B) Rolling back the transaction that requested the lock last
- C) Rolling back the transaction that acquired the most locks
- D) Allowing the system to automatically retry
- What system view can be used to identify potential deadlocks in PostgreSQL?
- A) pg_stat_activity
- B) pg_locks
- C) pg_stat_bgwriter
- D) pg_deadlocks
- In PostgreSQL, what isolation level helps to avoid deadlocks by ensuring transactions do not overlap?
- A) Serializable
- B) Repeatable Read
- C) Read Uncommitted
- D) Read Committed
- How can deadlocks be prevented in PostgreSQL?
- A) By using long transactions
- B) By acquiring locks in the same order in all transactions
- C) By running all queries asynchronously
- D) By disabling autocommit
- What is the first step PostgreSQL takes when it detects a deadlock?
- A) Logs the deadlock to a file
- B) Rolls back one of the conflicting transactions
- C) Sends a warning to the administrator
- D) Issues a COMMIT to release all locks
- What PostgreSQL configuration option can be adjusted to control the detection of deadlocks?
- A) deadlock_timeout
- B) deadlock_detection
- C) transaction_timeout
- D) lock_timeout
MVCC (Multi-Version Concurrency Control) Internals
- In PostgreSQL, MVCC allows:
- A) Multiple transactions to read the same data at the same time
- B) Transactions to access the data only when it is locked
- C) Only one transaction to access the data at any time
- D) All transactions to be executed serially
- How does PostgreSQL implement MVCC?
- A) By using a global lock for all transactions
- B) By storing multiple versions of a row in the table
- C) By creating a separate table for each transaction
- D) By using a single version of the row and locking it for exclusive access
- Which system table in PostgreSQL stores information about tuple visibility and transaction states in MVCC?
- A) pg_stat_activity
- B) pg_class
- C) pg_tuple
- D) pg_catalog.pg_proc
- What happens to a row version in PostgreSQL when a transaction is committed in an MVCC-enabled system?
- A) The row version is deleted
- B) The row version becomes visible to other transactions
- C) The row version is locked
- D) The row version is discarded
- Which isolation level uses MVCC in PostgreSQL to avoid read locks?
- A) Serializable
- B) Repeatable Read
- C) Read Committed
- D) Read Uncommitted
- What is a key benefit of MVCC in PostgreSQL?
- A) Reduced need for locks during reads
- B) Increased storage requirements
- C) Reduced transaction throughput
- D) Increased query execution time
- How are updates handled in an MVCC system?
- A) By overwriting existing row versions
- B) By creating new row versions and marking old ones as obsolete
- C) By locking the row and updating it in place
- D) By using temporary tables for the updates
- In MVCC, how does PostgreSQL ensure that each transaction sees a consistent view of the database?
- A) By storing separate snapshots for each transaction
- B) By using a global transaction log
- C) By locking all tables during updates
- D) By using a single shared transaction pool
- In PostgreSQL, what happens to obsolete row versions after a transaction completes?
- A) They are automatically removed during VACUUM
- B) They remain until manually deleted
- C) They are moved to a separate archive
- D) They are immediately deleted
- Which command helps to clean up obsolete row versions in an MVCC system in PostgreSQL?
- A) CLEANUP
- B) VACUUM
- C) TRIM
- D) PURGE
Answer Key
| Qno | Answer |
|---|---|
| 1 | D) Access share lock |
| 2 | B) Access exclusive lock |
| 3 | B) Locks the selected rows for update |
| 4 | B) Row exclusive lock |
| 5 | C) Prevents other transactions from locking the rows for exclusive access |
| 6 | A) Row exclusive lock |
| 7 | B) Causes the transaction to fail immediately if a lock cannot be acquired |
| 8 | A) Access share lock |
| 9 | B) Access exclusive lock |
| 10 | B) SELECT * FROM pg_locks |
| 11 | B) Two transactions cannot proceed because each is waiting for the other to release a lock |
| 12 | D) Reading data without acquiring locks |
| 13 | C) By checking for circular dependencies between transactions |
| 14 | B) It rolls back one transaction to resolve the deadlock |
| 15 | B) Rolling back the transaction that requested the lock last |
| 16 | B) pg_locks |
| 17 | A) Serializable |
| 18 | B) By acquiring locks in the same order in all transactions |
| 19 | B) Rolls back one of the conflicting transactions |
| 20 | A) deadlock_timeout |
| 21 | A) Multiple transactions to read the same data at the same time |
| 22 | B) By storing multiple versions of a row in the table |
| 23 | B) pg_class |
| 24 | B) The row version becomes visible to other transactions |
| 25 | C) Read Committed |
| 26 | A) Reduced need for locks during reads |
| 27 | B) By creating new row versions and marking old ones as obsolete |
| 28 | A) By storing separate snapshots for each transaction |
| 29 | A) They are automatically removed during VACUUM |
| 30 | B) VACUUM |