Oracle SQL Transactions and Concurrency Control Flashcards

What SQL command permanently saves all changes made in the current Oracle transaction to the database?

Answer: COMMIT

COMMIT permanently saves all pending DML changes made in the current transaction to the database.

Which Oracle SQL statement undoes all uncommitted changes made in the current transaction?

Answer: ROLLBACK

ROLLBACK undoes all uncommitted DML changes in the current transaction, restoring data to the last committed state.

What is the purpose of the SAVEPOINT statement in Oracle SQL?

Answer: To create a named checkpoint within a transaction that can be rolled back to

SAVEPOINT creates a named point within a transaction, allowing partial rollback to that specific point rather than rolling back the entire transaction.

Which Oracle SQL syntax correctly rolls back a transaction to a savepoint named 'SP1'?

Answer: ROLLBACK TO SAVEPOINT SP1

The correct syntax is ROLLBACK TO SAVEPOINT savepoint_name to undo changes back to the specified savepoint.

Which event causes an implicit (automatic) COMMIT in Oracle SQL?

Answer: Executing a DDL statement such as CREATE TABLE

DDL statements (CREATE, ALTER, DROP, TRUNCATE) in Oracle implicitly issue a COMMIT before and after executing, finalizing any pending transactions.

What happens to all row-level locks held by a transaction when COMMIT is issued in Oracle?

Answer: All locks are immediately released

When a COMMIT is issued, Oracle releases all row-level and table-level locks acquired during the transaction, allowing other sessions to modify those rows.

In Oracle, which of the following best describes a transaction?

Answer: A logical unit of work consisting of one or more SQL statements that must all succeed or all fail

A transaction is a logical unit of work that groups one or more SQL statements, ensuring atomicity so all changes either commit together or roll back together.