SnowPro Advanced: Architect — Question 177

An Architect is defining transaction rules to adhere to ACID (Atomicity, Consistency, Isolation, Durability) properties for a project to ensure that executed statements are either committed or rolled back.

Based on this scenario, what characteristics of transactions should be considered? (Choose two).

Answer options

• A. The AUTOCOMMIT setting can be changed inside a stored procedure.
• B. Explicit transactions should contain DDL, DML, and query statements.
• C. Explicit transactions should contain only DML statements and query statements. All DDL statements implicitly commit active transactions.
• D. An explicit transaction can be started by executing a BEGIN WORK statement and can be ended by executing a COMMIT WORK statement.
• E. An explicit transaction can be started by executing a BEGIN TRANSACTION statement and can be ended by executing an END TRANSACTION statement.

Correct answer: C, D

Explanation

Option C is correct because explicit transactions are designed to manage only DML statements and queries, with DDL statements committing active transactions automatically. Option D is also correct as it correctly describes how to start and end an explicit transaction using BEGIN WORK and COMMIT WORK commands. The other options either misrepresent transaction rules or include incorrect combinations of statements.