There’re two stored procedures in the database. The stored procedures are respectively named AlterSelling and GetSelling. Every day the two procedures are executed very often. The AlterSelling stored procedure updates a table named SellingOrders by using a transaction. From the SellingOrders table, data is retrieved and aggregated by the GetSelling stored procedure for a sales trend analysis report. When users run the sales trend analysis report, they have to wait for a long time. So what should you do to make sure that sales trend analysis report runs as quickly as possible?
A.
For AlterSelling, the isolation level should be changed to SERIALIZABLE.
B.
The NOLOCK hint has to be added to the SELECT statements in GetSelling.
C.
The NOWAIT hint has to be added to the SELECT statement in GetSelling.
D.
For AlterSelling, the isolation level should be changed to READ UNCOMITTED.
Explanation:
Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction.