The first step in learning how to avoid deadlocks in SQL Server databases is preparedness. Knowing about deadlocks...
Since a deadlock occurred, we can try collecting it from the system health event session. The system health event session is an Extended Events session that is installed by default starting with SQL Server 2008 on.
-- in particular, what causes them -- is the key to preventing them and resolving those that do occur, according to SQL Server experts.
Deadlocking isn't the same thing as blocking. The latter happens normally during database operations when a transaction tries to access a system resource that has been locked by another transaction. The SQL Server system blocks the second transaction until the first completes its process and releases the lock -- a situation that can be resolved by SQL Server itself without requiring any manual intervention.
SQL Server deadlocks, on the other hand, happen when two transactions block each other from resources they need to use, resulting in what's referred to as a deadly embrace that stops both transactions from continuing. SQL Server can automatically clear deadlocks, but only by terminating one of the transaction threads, and the intervention process to ensure that deadlocks don't recur can be lengthy and complex for database administrators (DBAs).
What causes deadlocks in databases?
Thomas LaRock, 'head geek' at management tools vendor SolarWinds in Austin, Texas, wrote in a June 2016 blog post that there are four primary -- and interrelated -- causes of deadlocks in SQL Server: application code, the database schema design, the resulting access patterns and the transaction isolation level settings in a database.
Deadlocks don't only occur in databases that contain large tables with indexes, LaRock added -- they can also happen in small tables. He said databases that encounter deadlocks can be seen as performance bottlenecks by end users, which puts pressure on SQL Server DBAs and application developers to work together 'to track down the root cause and fix the issue.'
Ways to avoid SQL Server deadlocks
Avoiding deadlocks in SQL Server is easier said than done, but it's not an impossible task.
In a December 2017 blog post, Victor Simon, a data recovery expert at DataNumen in Hong Kong, pointed to poor database design as a common cause of deadlocks. To help minimize the deadlock risk, he said a SQL Server DBA should create a well-defined order for how concurrent transactions access database objects, along with a clear set of rules to govern the process.
DBAs should also restrict users from inputting data while a transaction is being processed, Simon said. In addition, deadlocks are sometimes created because tables are locked when users run queries against them if a database is configured with the READ COMMITTED transaction isolation level, SQL Server's default setting. Additional queries have to wait for the lock to be released, but Simon said using the NOLOCK table hint in T-SQL statements allows DBAs to override table locking.
Using bound connections is another way to help avoid deadlocks in SQL Server, Simon wrote. That enables an application to open multiple cooperative connections to a database that can work with each other on locking so they don't block each other, reducing the likelihood of problems like deadlocking and database corruption.
Deadlock removal with SQL Server's lock monitor
SQL Server comes with a lock monitor feature that can detect and end deadlocks. It periodically searches for them in a database instance. The default interval between searches is every five seconds, but Microsoft says that drops to as low as 100 milliseconds if any deadlocks are found, with the new interval varying based on the frequency of the deadlocks.
Application That Can Deadlock Meaning
When the lock monitor detects multiple application threads trapped in a deadlock, it selects one of them to be the deadlock victim. That thread's processing jobs are terminated, and the ongoing transaction is rolled back in the database. Doing so releases the locks held by the deadlock victim, which allows the other blocked session -- or sessions -- to resume processing.
The lock monitor, by default, determines the deadlock victim based on the rollback cost, choosing the thread that will be least expensive to roll back from a processing standpoint. DBAs can also use the SET DEADLOCK_PRIORITY statement to assign priority levels to threads in the event of deadlocks.
The session with the lowest priority will then be chosen as the deadlock victim. If multiple sessions have the same deadlock priority, the lock monitor will revert to choosing the one with the lowest rollback cost.
Info about deadlocks to help avoid them
Though it may seem more convenient to rely solely on the lock monitor to do its job, DBAs must still investigate deadlocks to determine their cause so steps can be taken to avoid them in the future. There are three primary methods of getting information about deadlocks -- known as the deadlock graph -- from SQL Server.
Using trace flag 1222 in T-SQL returns information captured in SQL Server's error log after deadlocks occur; trace flag 1204 can also be used, but 1222 provides more details. The trace flags were once the only real way to access the deadlock graph info, according to Grant Fritchey, a product evangelist at Redgate, a SQL Server tools vendor based in Cambridge, England.
To capture information on deadlocks as they occur, however, trace flag 1222 must be permanently enabled, Fritchey wrote in an article on Redgate's website. With the advent of newer, more efficient methods, he recommended only using the trace flag as a last resort to aid in deadlock prevention.
The other approaches include using the system_health session in SQL Server's built-in Extended Events performance monitoring tool to view deadlock information. Extended Events was introduced in SQL Server 2008, and Microsoft added the deadlock graph to the system_health session in SQL Server 2012. According to Microsoft's documentation, the session is enabled by default and automatically captures information about the processes and system resources involved in deadlocks, plus a list of deadlock victims.
DBAs can also use Microsoft's SQL Server Profiler tool to save deadlock graphs for viewing and analysis in SQL Server Management Studio, although the profiler is now in maintenance mode and not recommended for use with new applications. As an alternative, users can turn to third-party performance monitoring tools to help in their efforts to resolve and avoid deadlocks in SQL Server.
-->
**Updated: **
Important APIs
Creating DLLs presents a number of challenges for developers. DLLs do not have system-enforced versioning. When multiple versions of a DLL exist on a system, the ease of being overwritten coupled with the lack of a versioning schema creates dependency and API conflicts. Complexity in the development environment, the loader implementation, and the DLL dependencies has created fragility in load order and application behavior. Lastly, many applications rely on DLLs and have complex sets of dependencies that must be honored for the applications to function properly. This document provides guidelines for DLL developers to help in building more robust, portable, and extensible DLLs.
Improper synchronization within DllMain can cause an application to deadlock or access data or code in an uninitialized DLL. Calling certain functions from within DllMain causes such problems.
General Best Practices
DllMain is called while the loader-lock is held. Therefore, significant restrictions are imposed on the functions that can be called within DllMain. As such, DllMain is designed to perform minimal initialization tasks, by using a small subset of the Microsoft® Windows® API. You cannot call any function in DllMain that directly or indirectly tries to acquire the loader lock. Otherwise, you will introduce the possibility that your application deadlocks or crashes. An error in a DllMain implementation can jeopardize the entire process and all of its threads.
The ideal DllMain would be just an empty stub. However, given the complexity of many applications, this is generally too restrictive. A good rule of thumb for DllMain is to postpone as much initialization as possible. Lazy initialization increases robustness of the application because this initialization is not performed while the loader lock is held. Also, lazy initialization enables you to safely use much more of the Windows API.
Some initialization tasks cannot be postponed. For example, a DLL that depends on a configuration file should fail to load if the file is malformed or contains garbage. For this type of initialization, the DLL should attempt the action and fail quickly rather than waste resources by completing other work.
You should never perform the following tasks from within DllMain:
The following tasks are safe to perform within DllMain:
Deadlocks Caused by Lock Order Inversion
When you are implementing code that uses multiple synchronization objects such as locks, it is vital to respect lock order. When it is necessary to acquire more than one lock at a time, you must define an explicit precedence that is called a lock hierarchy or lock order. For example, if lock A is acquired before lock B somewhere in the code, and lock B is acquired before lock C elsewhere in the code, then the lock order is A, B, C and this order should be followed throughout the code. Lock order inversion occurs when the locking order is not followed—for example, if lock B is acquired before lock A. Lock order inversion can cause deadlocks that are difficult to debug. To avoid such problems, all threads must acquire locks in the same order.
It is important to note that the loader calls DllMain with the loader lock already acquired, so the loader lock should have the highest precedence in the locking hierarchy. Also note that code only has to acquire the locks it requires for proper synchronization; it does not have to acquire every single lock that is defined in the hierarchy. For example, if a section of code requires only locks A and C for proper synchronization, then the code should acquire lock A before it acquires lock C; it is not necessary for the code to also acquire lock B. Furthermore, DLL code cannot explicitly acquire the loader lock. If the code must call an API such as GetModuleFileName that can indirectly acquire the loader lock and the code must also acquire a private lock, then the code should call GetModuleFileName before it acquires lock P, thus ensuring that load order is respected.
Figure 2 is an example that illustrates lock order inversion. Consider a DLL whose main thread contains DllMain. The library loader acquires the loader lock L and then calls into DllMain. The main thread creates synchronization objects A, B, and G to serialize access to its data structures and then tries to acquire lock G. A worker thread that has already successfully acquired lock G then calls a function such as GetModuleHandle that attempts to acquire the loader lock L. Thus, the worker thread is blocked on L and the main thread is blocked on G, resulting in a deadlock.
To prevent deadlocks that are caused by lock order inversion, all threads should attempt to acquire synchronization objects in the defined load order at all times.
Best Practices for Synchronization
Consider a DLL that creates worker threads as part of its initialization. Upon DLL cleanup, it is necessary to synchronize with all the worker threads to ensure that the data structures are in a consistent state and then terminate the worker threads. Today, there is no straightforward way to completely solve the problem of cleanly synchronizing and shutting down DLLs in a multithreaded environment. This section describes the current best practices for thread synchronizing during DLL shutdown.
Thread Synchronization in DllMain during Process Exit
Application That Can Deadlock T
Thread Synchronization in DllMain for DLL_THREAD_DETACH during DLL Unload
Application That Can Deadlock Go
If a DLL is unloaded after all its threads have been created, but before they begin executing, the threads may crash. If the DLL created threads in its DllMain as part of its initialization, some threads may not have finished initialization and their DLL_THREAD_ATTACH message is still waiting to be delivered to the DLL. In this situation, if the DLL is unloaded, it will begin terminating threads. However, some threads may be blocked behind the loader lock. Their DLL_THREAD_ATTACH messages are processed after the DLL has been unmapped, causing the process to crash.
Recommendations
The following are recommended guidelines:
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |