SQL - Structured Query Language Transaction Control Language (TCL) Questions and Answers 1 — Questions and Answers
Question 1: A database administrator begins a transaction, deletes a batch of old records, creates a savepoint named 'PreUpdate', and then updates the salaries for all employees in the 'Sales' department. After reviewing the changes, they realize the salary update was incorrect and needs to be undone, but the deletion of old records should be kept. Which TCL command should they use?
- ROLLBACK;
- ROLLBACK TO SAVEPOINT PreUpdate; (Correct answer)
- COMMIT;
- DELETE FROM Employees WHERE Department = 'Sales';
Correct answer: ROLLBACK TO SAVEPOINT PreUpdate;
The `ROLLBACK TO SAVEPOINT PreUpdate;` command will undo all changes made after the savepoint was created (the salary updates), while preserving the changes made before it (the record deletions). A full `ROLLBACK;` would undo the entire transaction, including the valid deletions.
Question 2: What is the primary purpose of the `COMMIT` command in SQL?
- To undo all changes made since the beginning of the transaction.
- To create a temporary marker within a transaction to which one can later return.
- To permanently save all changes made in the current transaction, making them visible to other users. (Correct answer)
- To start a new transaction and set its isolation level.
Correct answer: To permanently save all changes made in the current transaction, making them visible to other users.
The `COMMIT` command concludes the current transaction and makes all of its changes permanent and visible to other user sessions. `ROLLBACK` is used to undo changes, and `SAVEPOINT` is used to create an intermediate marker.
Question 3: User A starts a transaction and updates a customer's address but does not issue a `COMMIT`. Simultaneously, User B runs a `SELECT` query on that same customer's address. Assuming the database's default transaction isolation level is `READ COMMITTED`, what will User B see?
- User B will see the new, updated address from User A's uncommitted transaction.
- User B's query will be blocked and will wait until User A either commits or rolls back.
- User B's query will fail with a 'resource locked' error.
- User B will see the original customer address as it existed before User A's transaction began. (Correct answer)
Correct answer: User B will see the original customer address as it existed before User A's transaction began.
The `READ COMMITTED` isolation level ensures that a transaction will only see data that has been committed. This prevents 'dirty reads', where one transaction sees the uncommitted, in-progress work of another. User B will read the last committed version of the data.
Question 4: Which of the following statements best describes the concept of Atomicity in the context of database transactions (ACID)?
- A transaction's changes, once committed, will persist even in the event of a system failure.
- A transaction is an 'all or nothing' proposition; either all of its operations succeed, or none of them are applied. (Correct answer)
- Concurrent transactions are isolated from each other, as if they were running serially.
- A transaction brings the database from one valid state to another, preserving data integrity.
Correct answer: A transaction is an 'all or nothing' proposition; either all of its operations succeed, or none of them are applied.
Atomicity is the 'A' in the ACID properties and guarantees that a transaction is treated as a single, indivisible unit of work. If any part of the transaction fails, the entire transaction is rolled back, leaving the database unchanged.
Question 5: In many relational database management systems, which of the following actions will typically cause an implicit `COMMIT`, automatically ending any active transaction?
- Executing a `SELECT` statement that queries millions of rows.
- Running a Data Definition Language (DDL) statement, such as `ALTER TABLE`. (Correct answer)
- Issuing a `SAVEPOINT` command.
- Performing a series of `INSERT` statements within a single batch.
Correct answer: Running a Data Definition Language (DDL) statement, such as `ALTER TABLE`.
Data Definition Language (DDL) statements (like CREATE, ALTER, DROP) modify the database's structure. In most RDBMS (like Oracle and MySQL), executing a DDL statement will cause an implicit COMMIT of the preceding transaction before the DDL statement runs.
Question 6: A developer executes the following sequence of commands: 1. `START TRANSACTION;` 2. `INSERT INTO Logs (Message) VALUES ('Step 1');` 3. `SAVEPOINT A;` 4. `INSERT INTO Logs (Message) VALUES ('Step 2');` 5. `COMMIT;` What is the outcome of this sequence?
- Both 'Step 1' and 'Step 2' are permanently saved to the Logs table. (Correct answer)
- Only 'Step 1' is saved because the savepoint was never used for a rollback.
- An error occurs because a `SAVEPOINT` must be followed by a `ROLLBACK TO SAVEPOINT`.
- The entire transaction is rolled back because the `COMMIT` invalidates the savepoint.
Correct answer: Both 'Step 1' and 'Step 2' are permanently saved to the Logs table.
The `COMMIT` command finalizes the entire transaction, making all changes since the `START TRANSACTION` permanent. A `SAVEPOINT` is only a marker for a potential partial rollback. If `COMMIT` is issued, all savepoints within the transaction are disregarded, and all work is saved.
SQL - Structured Query Language Transaction Control Language (TCL) Questions and Answers 1
A database administrator begins a transaction, deletes a batch of old records, creates a savepoint named 'PreUpdate', and then updates the salaries for all employees in the 'Sales' department.
After reviewing the changes, they realize the salary update was incorrect and needs to be undone, but the deletion of old records should be kept.
Which TCL command should they use?