Transaction Control Language (TCL) Flashcards

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?

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.

What is the primary purpose of the `COMMIT` command in SQL?

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.

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?

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.

Which of the following statements best describes the concept of Atomicity in the context of database transactions (ACID)?

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.

In many relational database management systems, which of the following actions will typically cause an implicit `COMMIT`, automatically ending any active transaction?

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.

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?

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.