Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.3 Transactions (Transaction syntax)

Sample Question


Choose three appropriate descriptions for SAVEPOINT.

  1. It is often executed with a savepoint name as a parameter, like SAVEPOINT name, but this parameter can be omitted.

  2. The savepoint name, specified as in SAVEPOINT name, must be unique within a transaction. Reusing the same name will result in an error.

  3. Executing ROLLBACK TO name will discard all updates made after the SAVEPOINT, while updates made before the savepoint are preserved.

  4. Executing ROLLBACK will discard all updates made within the transaction, including those made before the SAVEPOINT.

  5. Executing COMMIT will confirm all updates, both before and after the SAVEPOINT, and write them to the database.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

Transactions can start with BEGIN (or START TRANSACTION), end with COMMIT, or abort with ROLLBACK. In addition to these, there is a temporary save feature for data called SAVEPOINT.
For complex transactions, it may be useful to be able to change the flow of processing depending on the contents of the data, and cancel and redo only the updates after a certain point. In such a case, you can use SAVEPOINT for temporary save.

SAVEPOINT is used in the form of 'SAVEPOINT name', with the savepoint name specified as a parameter. This pairs with 'ROLLBACK TO name', which allows you to return to that point (that is, it discards any updates since the temporary save). A savepoint name is required for both SAVEPOINT and ROLLBACK TO.
The same savepoint name can be used repeatedly. In that case, the savepoint is overwritten and the state of the previous temporary save is lost. Note that it is temporarily saved in a more recent state, so no data is lost).

If you execute ROLLBACK instead of 'ROLLBACK TO name', existing SAVEPOINT will be discarded and all updates in the transaction will be discarded.
COMMIT commits and writes all updates in a transaction, with or without SAVEPOINT.

So the correct answers are C, D, and E.