You have the following stored procedure:
The Numbers table becomes unavailable when you run the stored procedure. The stored procedure obtains an exclusive lock on the table and does not release the lock.
What are two possible ways to resolve the issue? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
A. Remove the implicit transaction and the SET ANSI_DEFAULTS ON statement.
B. Set the ANSI_DEFAULT statement to OFF and add a COMMIT TRANSACTION statement after the INSERT statement.
C. Add a COMMIT TRANSACTION statement after the INSERT statement.
D. Remove the SET ANSI_DEFAULTS ON statement.
Correct Answer: CD
Explanation/Reference:
Explanation:
SET ANSI_DEFAULTS is a server-side setting that the client does not modify. When enabled (ON), this option enables SET IMPLICIT_TRANSACTIONS (and some other options).
The SET IMPLICIT_TRANSACTIONS, when ON, the system is in implicit transaction mode.
This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN
TRANSACTION being executed first: ALTER TABLE, FETCH, REVOKE, BEGIN TRANSACTION, GRANT, SELECT, CREATE, INSERT, TRUNCATE TABLE, DELETE, OPEN, UPDATE, DROP.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-2017
I agree with C and D