In which three situations does a new transaction always start? (Choose three.)
In which three situations does a new transaction always start? (Choose three.)
The correct situations where a new transaction always starts are: when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session, since a DDL command like CREATE TABLE causes an implicit commit and the following SELECT FOR UPDATE will start a new transaction; when issuing the first DML statement after a COMMIT or ROLLBACK statement was issued in the same session, because a new transaction always starts after the previous one ends; and when issuing a DML statement after a DML statement failed in the same session, as the failure of a DML statement does not constitute the end of a transaction, but the next DML statement will start a new one.
ADE should be correct https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-7690181F-8293-47B9-ADC1-905BD9C3AF57 • A transaction begins when the first executable SQL statement is encountered. An executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement. • A transaction ends when any of the following actions occurs: o A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause. Answer D o A user runs a DDL command such as CREATE, DROP, RENAME, or ALTER. The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction. o SELECT do not have a transaction start/end. Check by command: SELECT XID AS "txn id", XIDUSN AS "undo seg", XIDSLOT AS "slot", XIDSQN AS "seq", STATUS AS "txn status" FROM V$TRANSACTION;
Spot on, I agree
Thanks for the details. I agree with you. Just for additional info I would like to add from the same reference that you have given that SELECT query is not considered a Transaction (it is mentioned in 2 of the answer options in the question).
A & D & F is correct answer
why not A, D; E?
DDL should just close the transaction with an implicit COMMIT. In this case the question is asking when a Transaction START , no when is COMPLETED or CLOSED. So my opinion is that: A Is correct , DML after a DDL B not correct, TRUNACATE is a DDL and is executed after a select that is not a DML. C not correct for the same reason of B D is Correct because we have a DML after a commit so after the end of a transaction. E is not Correct : DDL after DDL F is correct : https://oracle-base.com/articles/10g/dml-error-logging-10gr2 By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected.
Issue again, only two possible answers here. A transaction implicitly begins with any operation that obtains a TX lock: - When a statement that modifies data is issued (DML ONLY) - When a SELECT ... FOR UPDATE statement is issued - When a transaction is explicitly started with a SET TRANSACTION statement or the DBMS_TRANSACTION package A is correct. A select for update clause obtains a transaction lock TX, which is one of the three conditions under which transactions will implicitly begin. B is wrong. A truncate statement is DDL, and DDL statements perform implicit commits after they are done. Commits ends transactions, they don't start them C is wrong. A create table statement is DDL, same issue as option B. D is correct. DML statements start transactions. E is wrong. A create index statement is DDL, same issue as option B and C. F is wrong. When a DML statement fails it does not end a transaction, only commit or rollback (implicit or explicit) can do this. New DML statements will simply continue in the context of the current transaction. This option is easily tested.
Right with you
F may be correct. First DML failed and the new transaction not start. The second DML start new transaction.
C D E are correct C and E: Create Table and Create Index are DDL and a new single transaction is created anyway. D: when a DML issued when no transaction is still active, a new transaction will started Select for update is not a DML Truncate is not starting a transaction if a DML fails, the transaction still remains active
A. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session --> DDL auto commit + new transaction. true. B. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session --> truncate doesn't start a transaction. wrong. C. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session --> whether have a transaction or not, a DDL always create a transaction. true. D. when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session --> a select doesn't create a transaction. wrong. E. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session --> a DDL always create a transaction. true. F. when issuing a DML statement after a DML statement failed in the same session --> we need commit or rollback for a new transaction. wrong. Answer : A,C,E
A. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session --> DDL auto commit + new transaction. true. B. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session --> truncate doesn't start a transaction. wrong. C. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session --> whether have a transaction or not, a DDL always create a transaction. true. D. when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session --> a select doesn't create a transaction. wrong. E. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session --> a DDL always create a transaction. true. F. when issuing a DML statement after a DML statement failed in the same session --> we need commit or rollback for a new transaction. wrong. Answer : A,C,E
A,C,E is correct
Sorry Guys but i think that E is not Correct. Oracle shouldn't support Transactional DDL. DDL should just close the transaction with an implicit COMMIT. In this case the question is asking when a Transaction START , no when is COMPLETED or CLOSED. So my opinion is that: A Is correct , DML after a DDL B not correct, TRUNACATE is a DDL and is executed after a select that is not a DML. C not correct for the same reason of B D is Correct because we have a DML after a commit so after the end of a transaction. E is not Correct : DDL after DDL F is correct : https://oracle-base.com/articles/10g/dml-error-logging-10gr2 By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. Then i would say : A , D , F
A - FALSE; SELECT FOR UPDATE locks table rows, but do not start a transaction B - TRUE; TRUNCATE is a single DDL command, so it starts a transaction C - TRUE; CREATE TABLE is a single DDL command, so it starts a transaction D - FALSE; typically, first DML command issued after COMMIT/ROLLBACK starts a transaction but it is not generally true. Only DML command that makes an atomic change in the database starts a transaction. E - TRUE; CREATE INDEX is a single DDL command, so it starts a transaction F - FALSE; DML statement does not start a new transaction when not preceeded by COMMIT, ROLLBACK, DDL statement or new session
E should be DDL after DDL , I don't konw what will happened? a new trans or not.
Oracle 19c documentation states a DDL command is performed as a single-statement transaction. See paragraph "End of a Transaction" at https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-510DF997-BA9C-4431-95EF-A0622881EC9C However if I am requested to choose three answers, I choose those options that causes transaction ID is assigned. It can be printed with exec dbms_output.put_line( dbms_transaction.local_transaction_id ); I tried all options and only A, D, F returned non empty transaction ID.
https://www.examtopics.com/discussions/oracle/view/10274-exam-1z0-071-topic-1-question-256-discussion/
A - tested C - DDL (always start new transaction) E - DDL (always start new transaction)
Provided answer are corrects! https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-7690181F-8293-47B9-ADC1-905BD9C3AF57
A new transaction is always created after a DML statement is issued! Remember this! ;-)
A D F E are two DDL statements after each other, each statement is a completed transaction, there is no active transaction after each statement. Since the question is asking when a new transaction starts E is incorrect since there is no active transaction after the two statements. Tested in Oracle.
the CREATE INDEX statement is now listed in the table, which means that it does start a new transaction in Oracle 19c and later
The correct answer is D,E,F The situations in which a new transaction always starts are: D. When issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session. This is true because a COMMIT or ROLLBACK statement ends the current transaction, and the next DML statement starts a new transaction. E. When issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session. Creating an index involves a separate transaction from the creation of the table, so issuing a CREATE INDEX statement after a CREATE TABLE statement always starts a new transaction. F. When issuing a DML statement after a DML statement failed in the same session. If a DML statement fails, it does not affect the transactional state of the session, so issuing another DML statement afterward will start a new transaction.