programming
beginner
10 sample questions
Transaction Management MCQ Practice Test
Ensuring data integrity with ACID properties (Atomicity, Consistency, Isolation, Durability).
Q1. A database transaction is rolled back due to a deadlock. Which of the following is a characteristic of the transaction that was rolled back?
-
A. It was committed successfully before the rollback
-
B. It held locks on multiple resources for an extended period ✓
-
C. It was the only transaction running on the system
-
D. It was part of an atomic update operation
Explanation: A deadlock occurs when two or more transactions are blocked indefinitely, waiting for each other to release resources. In this case, the transaction that was rolled back likely held locks on multiple resources for an extended period, contributing to the deadlock.
Q2. In a database transaction, what is the purpose of the "savepoint" feature in MySQL?
-
A. To roll back a transaction to a specific point without rolling back the entire transaction. ✓
-
B. To commit a transaction without releasing the locks.
-
C. To release locks held by a transaction without committing it.
-
D. To start a new transaction from a previous one.
Explanation: A savepoint is a temporary mark within a transaction that allows the transaction to be rolled back to that point without affecting the entire transaction. This feature is useful for isolating a portion of a transaction and rolling it back independently of the rest of the transaction.
Q3. In a database transaction, which of the following is an example of a cascading update that occurs when a parent table's primary key is updated?
-
A. The deletion of a parent record automatically deletes its corresponding child records.
-
B. The update of a parent record automatically updates its corresponding child records. ✓
-
C. The insertion of a parent record automatically inserts its corresponding child records.
-
D. The modification of a parent record automatically modifies its corresponding child records.
Explanation: A cascading update is a type of constraint that automatically updates related records in a database when a primary key is updated. This is often used in referential integrity to maintain data consistency.
Q4. In a database system that supports nested transactions, what is the purpose of the "Savepoint" mechanism?
-
A. To roll back changes made within a specific transaction
-
B. To commit changes made within a specific transaction
-
C. To create a temporary point within a transaction to which changes can be rolled back ✓
-
D. To abort the entire transaction
Explanation: The Savepoint mechanism allows a transaction manager to create a temporary point within a transaction to which changes can be rolled back in case of a failure, enabling the transaction to be recovered and retried.
Q5. In a database transaction using pessimistic locking, which of the following statements best describes the effect of a roll back operation on a locked resource?
-
A. The lock is immediately released and the resource becomes available to other transactions. ✓
-
B. The lock is retained and the transaction is retried with the same lock.
-
C. The lock is retained but the resource is marked as "deadlocked" and the transaction is rolled back.
-
D. The lock is released but the resource remains in a locked state until the transaction is explicitly unlocked.
Explanation: In pessimistic locking, when a transaction rolls back, all locks held by the transaction are released, making the resources available to other transactions. This is in contrast to optimistic locking, where locks are held until the transaction commits or rolls back.
Q6. In a database transaction, which of the following isolation levels ensures that a transaction will see a consistent view of the database, but may not see its own changes until it is committed?
-
A. Read Committed
-
B. Repeatable Read ✓
-
C. Serializable
-
D. Snapshot Isolation
Explanation: In Repeatable Read isolation level, a transaction will see a consistent view of the database, but may not see its own changes until it is committed. This is because the transaction will see the data as it was at the start of the transaction, and will not see any changes made by other transactions until it is committed.
Q7. Which SQL transaction isolation level is the weakest, allowing dirty reads (reading uncommitted changes from other transactions)?
-
A. READ UNCOMMITTED ✓
-
B. READ COMMITTED
-
C. REPEATABLE READ
-
D. SERIALIZABLE
Explanation: READ UNCOMMITTED permits reading data other transactions have not yet committed (dirty reads); each stronger level eliminates more anomalies, up to SERIALIZABLE.
Q8. A database transaction is considered "consistent" if it follows the ACID principle. Which of the following statements about consistency in database transactions is correct?
-
A. A transaction is consistent if it doesn't modify any data.
-
B. A transaction is consistent if it doesn't violate any constraints. ✓
-
C. A transaction is consistent if it doesn't change the database's state.
-
D. A transaction is consistent if it rolls back to a consistent state in case of failure.
Explanation: A consistent transaction doesn't violate any constraints, such as primary key or foreign key constraints, during its execution. This ensures that the database remains in a consistent state even in the presence of concurrent transactions.
Q9. In database systems, what does a SAVEPOINT do?
-
A. Marks an intermediate point within a transaction that later ROLLBACK can return to, preserving earlier work ✓
-
B. Commits part of a transaction permanently
-
C. Grants other users access
-
D. Duplicates the database
Explanation: A savepoint is a marker inside an uncommitted transaction; ROLLBACK TO SAVEPOINT undoes only the work after it. Nothing is partially committed — the whole transaction remains atomic until COMMIT.
Q10. In a database transaction, which of the following is an example of an error that typically leads to a transaction being rolled back?
-
A. Deadlock error
-
B. Disk full error ✓
-
C. Network connection lost error
-
D. Transaction timeout error
Explanation: A disk full error prevents the database from writing data to disk, which is a critical operation for transaction completion. This usually results in the transaction being rolled back to maintain data consistency. While the database might attempt to handle the error, it's not a seamless recovery. Deadlocks, network issues, and timeouts also commonly cause rollbacks.
That was just a sample. Sign up to unlock the full question bank with timed tests and certificates.
Sign Up Free