New Method for SQL Server concurrency

You can optimize the database query and upgrade the database server, but it is not enough to ensure the database performance of the application. this is because the query operation may be performed concurrently (synchronously) in the system. in fact, in a productized database system, the problem that may be caused by synchronous query execution cannot be solved by many query optimizations. we can have the most optimal query and the fastest query to execute. However, if these queries do not obtain the required data due to concurrency issues, the optimization work is not enough.

Fundamentally, we must ensure that the connection between database query and query execution must be able to run concurrently without errors (such as deadlocks or update conflicts ), and the results can be obtained within an acceptable waiting time. if it cannot be done, You need to locate the concurrency problem and find out the cause: Why is it correct when the query is run independently, and there is a problem when the concurrent operation occurs.

The process of solving concurrency problems is an experience-based process. it is not easy to imitate the product environment, and it is also difficult to predict the behavior of the query operation concurrency from many synchronous connections. this is why the system status must be retained frequently to diagnose the problem when a concurrency problem occurs.

In most cases, the concurrency problem is that an SQL Server process (or task) waits for a certain resource, and this resource has not been released by other processes. if two processes are serialized, there will be no conflicts. When they are parallel, a resource conflict will occur. these waits are mostly caused by lock requests to user resources.

In this chapter, we will learn how to solve the concurrency problem. In summary, we will diagnose and solve the following problems: locks, blocking, and deadlocks. The basic method is simple and clear:

● Identify concurrency issues;

● Analyze and identify the cause;

● Modify and solve the problem.

This method requires us to be familiar with a lot of background information. this chapter assumes that The reader has read or is familiar with The content in Chapter "lock and concurrency" in "Inside SQL Server 2005: The Storage Engine 8th" written by Kalen Delaney (see references in this book: other resources and references ). specifically, that chapter contains the required information about SQL Server 2005 transactions, isolation levels, locks, and other content required to solve concurrency issues.

1. new tools to solve concurrency Problems

SQL Server 2005 adds some important new tools to solve concurrency problems. in addition to the new tools, it still contains some original tools, such as sp_who2 and other system stored procedures, Perfmon counters, and SQL Trace/Profiler tools to solve concurrency problems.

To detect and analyze concurrency problems, SQL Server 2005 includes the following new tools:

DMV, especially sys. dm_ OS _wait_stats and sys. dm_ OS _waiting_tasks.

Enhanced sqldiag.exe to collect running system information.

Line version-based isolation options ("snapshot" and "committed read snapshot" isolation level ).

The new 1222 Trace identifier and SQL Trace detection and analysis deadlock "deadlock Chart" event class.

The blocked process reporting event class in SQL Trace/Profiler and the server configuration option for detecting long-blocked "blocked process threshold.

The new counter "SQLServer: Transactions Perfmon" object is used to detect update conflicts in the "Snapshot isolation" transaction and calculate the version storage usage in tempdb.

Currently, there are two basic tools in the new tool: sys. dm_ OS _waiting_tasks DMV and the row-based version isolation level for resolution. here we will only give a general introduction to more details when talking about specific technologies for solving the problem.

(1) New blocking detection tool: sys. dm_ OS _waiting_tasks

Sys. dm_ OS _waiting_tasks is the most important new tool for SQL Server 2005 to detect the blocking type of concurrency problems. this DMV displays the wait information for all tasks. not all waiting tasks are blocked because the task may wait for I/O or memory authorization. when a task is waiting for another task for a relatively long time, it will be blocked. when one task A is blocked by another task B, blocking Task B is displayed in the view.

One of the advantages of this view is that it lists tasks and processes. the task shows more detailed SQL Server execution details than SPID (Server process ID. if the process is parallel, a given SPID may contain some synchronization tasks. If the spid is not parallel, the task has only one spid.

There are many causes of task waiting, and many of them have nothing to do with concurrency. Therefore, it is important to filter out irrelevant rows from the view, so that you can pay more attention to the blocking problem caused by resource locking.

(2) New blocking resolution tool: isolation level based on Row Version Control

Another major new tool is composed of the "Snapshot isolation" option based on the row version, which reduces the number of locks and thus reduces congestion and deadlocks. to be exact, the row version can reduce or remove the internal and external share locks of transactions. therefore, the query and transactions that may be blocked due to the release share lock are no longer blocked. unlike the NOLOCK prompt, these queries only read submitted data.

The use of row version-based isolation levels plays a role in some aspects, including the code changes required by the trigger when the row version increases in tempdb and when one or all of the "Snapshot isolation" options are used, conflicts between DDL options and possible missing updates when the SNAPSHOT isolation level is used.

2. Types of concurrency Problems

When an SQL Server process or task is waiting, it is usually waiting for a resource locked by another SQL Server. The most likely concurrency problem is related to the lock, including the following content:

● Lock

● Blocking

● Deadlock

● Use tempdb

● Update conflict

The first three are related to the following: locking is the basic concurrency mechanism of SQL Server. Blocking occurs when a task has to wait for resources locked by another task, A deadlock occurs when tasks are blocked.

The last two types in the list are generated by the row version. in general, we may see that the increasing tempdb activity is related to the use of row versions, and some performance problems may be related to it. (We may also need to locate other concurrency problems related to tempdb, such as high tempdb activity related to high-frequency temporary table creation .) in addition, when the "snapshot" isolation level of write database transactions is used, we may find update conflicts.