Manage database concurrency

Thought experiment

In this thought experiment, demonstrate your skills and knowledge of the topics covered in this chapter. You can find answers to this thought experiment in the next section.

You are a database administrator at Coho Winery. Your manager has asked you to troubleshoot and resolve a number of concurrency problems in the OLTP system running on SQL Server 2016. Your manager has presented you with the following issues that users of the system are experiencing:

1. Two users ran the same report within seconds of one another. When they meet to review the results, they notice that the totals in the reports do not match. One report has more detail rows than the other report. You examine the stored procedure code that produces the report.

   SELECT
       so.OrderID,
       OrderDate,
       ExpectedDeliveryDate,
       CustomerID,
       CustomerPurchaseOrderNumber,
       StockItemID,
       Quantity,
       UnitPrice
   FROM Sales.Orders so
       WITH (NOLOCK)
   INNER JOIN Sales.OrderLines sol
       WITH (NOLOCK)
       ON so.OrderID = sol.OrderID

   What step do you recommend to ensure greater consistency in the report and what are the ramifications of making this change?

2. Users are reporting a process to update the order system is running slowly right now. Which DMVs do you use to identify the blocking process and why?

3. A new application developer is asking for help diagnosing transaction behavior. The transaction in the following code never gets committed:

   BEGIN TRANSACTION;
       UPDATE <do something>;
       BEGIN TRANSACTION;
           UPDATE <DO SOMETHING>;
           BEGIN TRANSACTION;
               UPDATE <DO SOMETHING>;
   COMMIT TRANSACTION;

   What recommendation can you give the developer to achieve the desired result and commit all update operations?

4. An internal application captures performance and logging data from thousands of devices through a web API. Seasonally, the incoming rate of data shifts from 3,000 transactions/sec to 30,000 transactions/sec and overwhelms the database. What implementation strategy do you recommend?

5. Which indexing strategy should be used for a memory-optimized table for which the common query pattern is shown below?

SELECT CustomerName FROM Customer
WHERE StartDate > DateAdd(day, -7, GetUtcDate());