MCQs on Transactions and Concurrency Control | PostgreSQL Database
Explore Transactions and Concurrency Control in PostgreSQL with a deep dive into ACID properties, transaction handling, isolation levels, and locking mechanisms. Strengthen your database expertise with these essential concepts.
Understanding ACID Properties
- Which of the following is NOT a characteristic of ACID properties?
- A) Atomicity
- B) Consistency
- C) Indifference
- D) Durability
- Atomicity ensures that:
- A) All transactions are logged.
- B) A transaction is completed fully or not at all.
- C) Data remains isolated during execution.
- D) No locks are required.
- The consistency property of ACID refers to:
- A) Uniform transaction duration.
- B) Maintaining database integrity after a transaction.
- C) Automatic locking mechanisms.
- D) Parallel execution of transactions.
- Durability in ACID is achieved through:
- A) Immediate transaction logging.
- B) Rollback operations.
- C) Temporary storage in memory.
- D) Isolation levels.
- Which ACID property ensures that a database remains unaffected by failed transactions?
- A) Atomicity
- B) Isolation
- C) Consistency
- D) Durability
- What is the main role of isolation in ACID properties?
- A) Preventing system crashes.
- B) Allowing transactions to execute without interference.
- C) Ensuring durability of the database.
- D) Enforcing database schema.
Basic Transactions (BEGIN, COMMIT, ROLLBACK)
- What does the BEGIN command in PostgreSQL signify?
- A) Starts a new session.
- B) Initiates a new transaction.
- C) Commits the current transaction.
- D) Rolls back the previous transaction.
- What is the result of a COMMIT operation?
- A) Temporarily saves changes.
- B) Abandons all changes in a transaction.
- C) Permanently saves changes made by the transaction.
- D) Resumes a paused transaction.
- What happens if a ROLLBACK command is issued?
- A) The database restores to its state before the last transaction.
- B) Changes are partially saved.
- C) Logs are cleared for the current session.
- D) Transaction status remains unchanged.
- Which of the following best describes a transaction?
- A) A single query execution.
- B) A series of operations treated as a single unit.
- C) An automatic backup operation.
- D) A user session in PostgreSQL.
- A transaction is automatically started in PostgreSQL when:
- A) BEGIN is issued.
- B) The first SQL statement is executed.
- C) COMMIT is called.
- D) The database restarts.
- What is the default behavior of transactions in PostgreSQL if no explicit BEGIN is issued?
- A) Automatic rollback.
- B) Auto-commit.
- C) Transactions are queued.
- D) Transactions are skipped.
- Which of the following statements is correct about ROLLBACK?
- A) It only works within a transaction block.
- B) It saves changes permanently.
- C) It can recover deleted records from earlier sessions.
- D) It cannot undo a COMMIT.
- What is a savepoint in PostgreSQL?
- A) A point to restart a failed query.
- B) A named point in a transaction to partially roll back.
- C) A snapshot of the database.
- D) A backup mechanism.
Isolation Levels Overview
- Which of the following is NOT an isolation level in PostgreSQL?
- A) Read Uncommitted
- B) Read Committed
- C) Serializable
- D) Immediate
- Which isolation level ensures no dirty reads occur?
- A) Read Committed
- B) Serializable
- C) Repeatable Read
- D) Read Uncommitted
- Read Committed isolation level ensures:
- A) Dirty reads are possible.
- B) Only committed data is read.
- C) All transactions are serialized.
- D) All data is locked during execution.
- At which isolation level can phantom reads occur?
- A) Serializable
- B) Read Uncommitted
- C) Read Committed
- D) Repeatable Read
- Which isolation level provides the highest level of data consistency?
- A) Serializable
- B) Repeatable Read
- C) Read Committed
- D) Read Uncommitted
- What is a dirty read?
- A) Reading uncommitted changes from another transaction.
- B) Reading stale data from outdated indexes.
- C) Reading corrupted data.
- D) Reading locked rows.
Locking Basics
- What is the primary purpose of locking in PostgreSQL?
- A) Improve query performance.
- B) Prevent data conflicts in concurrent transactions.
- C) Encrypt sensitive data.
- D) Execute faster queries.
- Which lock type is automatically acquired when a table is queried in PostgreSQL?
- A) Share lock
- B) Row lock
- C) Access Share lock
- D) Exclusive lock
- When a transaction updates a row, what lock type is placed?
- A) Share lock
- B) Row Exclusive lock
- C) Access Exclusive lock
- D) Row Share lock
- What happens if two transactions try to update the same row at the same time?
- A) One transaction blocks the other.
- B) Both transactions proceed.
- C) Both updates are combined.
- D) Both transactions fail.
- Which lock prevents other transactions from reading or writing to a table?
- A) Access Exclusive lock
- B) Row Share lock
- C) Share lock
- D) Access Share lock
- What is the default behavior when a deadlock occurs in PostgreSQL?
- A) Both transactions proceed.
- B) PostgreSQL terminates one of the transactions.
- C) The database crashes.
- D) The deadlock is ignored.
- What command can you use to explicitly acquire a lock in PostgreSQL?
- A) ACQUIRE LOCK
- B) LOCK TABLE
- C) GET LOCK
- D) SET LOCK
- How can you detect deadlocks in PostgreSQL?
- A) Using pg_stat_activity
- B) Reviewing server logs
- C) Monitoring pg_locks table
- D) All of the above
- Which command releases all locks held by a transaction?
- A) COMMIT
- B) ROLLBACK
- C) RELEASE ALL LOCKS
- D) SAVEPOINT
- How does PostgreSQL prevent inconsistent reads during updates?
- A) By using MVCC (Multi-Version Concurrency Control).
- B) By locking the entire database.
- C) By delaying all reads during writes.
- D) By using snapshots.
Answer Key
| Qno | Answer |
|---|---|
| 1 | C) Indifference |
| 2 | B) A transaction is completed fully or not at all |
| 3 | B) Maintaining database integrity after a transaction |
| 4 | A) Immediate transaction logging |
| 5 | A) Atomicity |
| 6 | B) Allowing transactions to execute without interference |
| 7 | B) Initiates a new transaction |
| 8 | C) Permanently saves changes made by the transaction |
| 9 | A) The database restores to its state before the last transaction |
| 10 | B) A series of operations treated as a single unit |
| 11 | B) The first SQL statement is executed |
| 12 | B) Auto-commit |
| 13 | A) It only works within a transaction block |
| 14 | B) A named point in a transaction to partially roll back |
| 15 | D) Immediate |
| 16 | A) Read Committed |
| 17 | B) Only committed data is read |
| 18 | C) Read Committed |
| 19 | A) Serializable |
| 20 | A) Reading uncommitted changes from another transaction |
| 21 | B) Prevent data conflicts in concurrent transactions |
| 22 | C) Access Share lock |
| 23 | B) Row Exclusive lock |
| 24 | A) One transaction blocks the other |
| 25 | A) Access Exclusive lock |
| 26 | B) PostgreSQL terminates one of the transactions |
| 27 | B) LOCK TABLE |
| 28 | D) All of the above |