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 ONstatement.
B . Set the ANSI_DEFAULTstatement to OFF and add a COMMIT TRANSACTIONstatement after the INSERTstatement.
C . Add a COMMIT TRANSACTIONstatement after the INSERT statement.
D . Remove the SET ANSI_DEFAULTS ONstatement.
Answer: CD
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
Leave a Reply