Manage database concurrency

Thought experiment answers

This section contains the solutions to the thought experiment. Each answer explains the resolution to each of the issues identified in the OLTP system.

1. The use of the NOLOCK table hint is common in reporting applications against OLTP systems in which lack of consistency is a trade-off for faster query performance. However, when users are dissatisfied with inconsistent results, you can recommend removing this table hint and allow the default isolation in SQL Server to manage transaction isolation. Long write operations can block the report from executing. Similarly, if the report takes a long time to execute, the read operation can block write operations.

2. Start with a query that returns sys.dm_os_waiting_tasks where blocking_session_id <> 0 and session_id equals the ID for the user’s session to see if anything is blocking the user’s request. The following columns will give you details about the blocking situation: blocking_session_id, wait_type, and wait_duration_ms. You can join this information to sys.dm_tran_locks to discover the current locks involved by including the request_mode and resource_type columns. The request_status column provides information about the locks. A value of CONVERT in this column is an indicator that a request is blocked. You can also use the value in the resource_associated_entity_id column to find the associated object’s name in sys.partitions.

3. In explicit transaction mode with nested transactions, each BEGIN TRANSACTION must correspond to a COMMIT TRANSACTION. As each new transaction starts with BEGIN TRANSACTION, the @@TRANCOUNT variable increments by 1 and each COMMIT TRANSACTION decrements it by 1. The complete transaction does not get written to disk and committed completely until @@TRANCOUNT is 0.

   While this solution is correct, a better solution is not to use nested transactions.

4. For this type of scenario, you recommend migrating the application to memory-optimized tables. The use of memory-optimized tables is well-suited for the ingestion of high-volume inserts because it prevents the bottlenecks commonly resulting from locking and eliminates logging. Consequently, the throughput rate (number of rows loaded per second) can substantially increase.

5. You should recommend a nonclustered B-tree index for this query pattern. It works best for range selections in contrast to a hash index which works best for point lookups or a columnstore index which works best for large table scans.