Avoid Database Deadlocks with Planning

Avoid Database Deadlocks with Planning

esolving deadlocks is one of the more elusive solutions in database application development. Deadlocks are the dark side of concurrency, that is, they occur when some combination of locking, coding, or transaction management goes wrong. Deadlocking represents a failure of processes to work and play well together.

In this 10-Minute Solution, I discuss the controls and settings that go into transaction management and describe how to debug deadlocks by integrating resources in a manner that balances concurrency and integrity.



How do I apply locking, coding, and transaction management in a way that strikes the correct balance between concurrency and integrity and avoids deadlocks?



Integrate resources using the proper transaction isolation levels and locks, and plan ahead with your own serialization convention to avoid deadlocks.

Transaction Isolation Levels: Concurrency vs. Integrity
Transaction isolation levels dictate how visible a transaction’s intermediate product (the state of the data undergoing change before it is committed to the database) is to a second process that also needs to access the resources undergoing a change. The changes result from the traditional CRUD actions (Create, Read, Update, and Delete).

The four transaction isolation levels are:

  • Read Uncommitted Isolation Level The benefit of Read Uncommitted is that it offers maximum concurrency; transactions don’t have to wait or be blocked because no shared locks or exclusive locks are given. Under the Read Uncommitted isolation level, a second process can see data before the transaction that changes the data is complete. This is called a dirty read. Another possible con is the lost update: a change made by a first process is changed by a second one.
  • Read Committed Isolation Level Read Committed guarantees that the data viewed by a transaction is “real.” This level holds shared locks, which prevents dirty reads, but a writing transaction could cause either an unrepeatable read or a phantom record. Read Committed is the default transaction isolation level setting in SQL Server.
  • Repeatable Read Isolation Level Repeatable Read locks the data viewed to guarantee its stability throughout the entire transaction. A second transaction can add new inserts to the set, however. If the process needs to revisit the data, it will see the same data it did the first time, but it could see additional, “phantom” data as well.
  • Read Serializable Isolation Level In the Read Serializable level, the set is guaranteed to be free of phantoms because locks are held throughout the transaction. The set under consideration cannot have any updates, inserts, or deletes that weren’t there the first time. The tradeoff for serialized transactions is it has the lowest concurrency. No changes can happen to the data until it is finished.

    By serializing the power to change data, the state of the data is guaranteed to be consistent (i.e., stable for each modification made). In general, the four levels illustrate the trade off in transaction isolation: maximum concurrency comes at the price of data integrity.

    Ensure Integrity with Locks
    Locking ensures integrity in data-changing events by informing a process that its action or contemplated action will be completed. Locks block competing processes until the resources being used by the current process are released. You can apply locks to various resources. In SQL Server 2000, you can lock a row, a key, a page of an extent, an extent of a table, an entire table, and even the entire database. You also can apply locks in an escalating scope through the following locking modes:

  • Shared
  • Update
  • Exclusive
  • Intent
  • Schema

    Locks act in a hierarchy. They tell the higher levels controlling a resource that a lower-level lock is present (by placing an Intent lock at the higher levels). For example, for a row exclusively locked, the page and the table will each have an Intent Exclusive lock. Intent locks telegraph a process’s actions in advance to other processes that may also require that resource.

    In addition to locking a specific resource, each mode indicates the severity of the restriction and the reduction in concurrency placed on the resource. Remember that each escalating lock combination causes a reduction in concurrency and an increase in the degree of exclusivity applied to a transaction.

    Orchestrate Processes with Isolation Levels and Locking
    SQL Server offers many commands to influence concurrency. To create the illusion of everyone working together, developers can manipulate transaction isolation levels and locking granularity and exercise coding practices to orchestrate the cooperation between processes. By doing so, they ensure that their transactions execute without invalidating each other’s changes. This orchestration is an illusion because only one process or transaction can happen at a time on a resource, but enforcing isolation and locking the DBMS can guarantee consistency and concurrency.

  • Anatomy of a Deadlock
    Deadlocks come in two flavors: cycle deadlocks and conversion deadlocks. Both types arise when processes contend for the same resources. Cycle deadlocks arise in contention for data, either a row or a table. Conversion deadlocks occur over contention for lock escalation, where two processes are in competition for exclusivity.

    In a cycle deadlock, Process A wants a resource that is already locked by Process B, and Process B wants the resource already locked by Process A. Each process waits for the other to release its lock on the resource it’s waiting for, but because both processes hold the lock on the resource requested by the other, they wind up waiting forever.

    Both types, especially conversion deadlocks, can materialize from relationships that are not immediately obvious. Thankfully, the tools that ship with SQL Server and Windows 2000 are very helpful in identifying deadlock messages from the server. You can use the Create Trace Wizard and choose “Identify causes of a deadlock” or “Profile the performance of a stored procedure” to see what happened and how.

    The server identifies deadlocks by raising error 1205. The server messages can be turned on and redirected by trace flags. The relevant trace flags are 1204 (for deadlock detection), 3604, and 3605. Trace 1204 gives voluminous information on the participants in the deadlock and provides a clue as to what type of deadlock you are clearing. Trace 3604 sends messages to Query Analyzer; trace flag 3605 redirects 1204’s output to the SQL Server error log.

    Once the source for contention is known, you can determine which fix to apply or even elect to live with the deadlock, which also is an option. If you decide to live with the deadlock condition, you’ll have your application retry the operation after a 1205 error.

    As an exercise to see how deadlocks come about, you can make a deadlock happen in Query Analyzer. Start a transaction in one session that will do an update on resource A (say, change an order number in an order header of Northwind). Open another Query Analyzer window and start another transaction on the same resource you decided to modify, but pick a different field (say, date). Go back to the first window. In the running transaction, run the command to also update date. You will succeed in tying the server in knots and the server will return the 1205 error.

    Planning Ahead Is Easier Than Recovering
    Identifying deadlocks is not too difficult?after all, the server complains. Resolving deadlocks is a different story. With the proper planning, however, you can debug deadlocks before they become a problem.

    You can resolve cycle deadlocks by killing the cheapest process, the one that acquired its lock last or locked for the shortest amount of time. You also can set a deadlock priority high or low, which will alter the server’s selection of a deadlock victim. Even if you decide to live with the deadlock condition, you can still control which process the server picks as the deadlock victim. For example, if you determine that the deadlock occurs between a transaction processing operation and an operation servicing a reporting application, you can set the report application’s deadlock priority to low so that the server kills it instead of the transaction process.

    Note: Altering the server code to trap for the error is not an option, because once the server detects the deadlock it terminates the transaction, which short-circuits your error-detection code. The retry must happen at the application.

    While debugging cycle deadlocks is straightforward, conversion deadlocks are not as simple, because with them, locks can accumulate and become unmanageable. One way to plan for deadlocks is to adopt your own serialization convention to help the server do the right thing:

    • Pick an order for things to happen and enforce that order everywhere. Alphabetical order by table name will do. This means that the server can serialize access to inserts, updates, and deletes in a universally observed order.
    • Have your applications test for error 1205 and retry the transaction again.
    • Code procedures so that the data modification comes before a select statement. In other words, do your update first, then select.

    For example, despite looking fine, the following procedure deadlocked because of contention for the row that holds the nexid value:

    Begin TransactionSELECT @Next_Number = NextID_Next_num 	FROM NextID 	WHERE (NextID_Id_name = @FieldName)UPDATE NextID SET NextID_Next_num = (@Next_Number + 1) 	WHERE (NextID_Id_name = @FieldName)Commit TransactionRETURN @Next_Number

    Here is a different approach, in which the update comes before the select:

    Begin Transaction  UPDATE NextID SET NextID_Next_num = NextID_Next_num + 1 	WHERE (NextID_Id_name = @FieldName)SELECT @Next_Number = NextID_Next_num 	FROM NextID WHERE  (NextID_Id_name = @FieldName)Commit Transaction

    Once you’ve set an order for your server to approach your data modifications, all you have left to do is fix a deadlock. Since the server picks its victim, you are limited to retrying your transaction. You can do so by coding a retry loop in your application.

    devx-admin

    devx-admin

    Share the Post:
    Software Development

    Top Software Development Companies

    Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in

    India Web Development

    Top Web Development Companies in India

    In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India,

    USA Web Development

    Top Web Development Companies in USA

    Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner

    Clean Energy Adoption

    Inside Michigan’s Clean Energy Revolution

    Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the

    Chips Act Revolution

    European Chips Act: What is it?

    In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor

    Revolutionized Low-Code

    You Should Use Low-Code Platforms for Apps

    As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with

    Software Development

    Top Software Development Companies

    Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

    India Web Development

    Top Web Development Companies in India

    In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

    USA Web Development

    Top Web Development Companies in USA

    Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

    Clean Energy Adoption

    Inside Michigan’s Clean Energy Revolution

    Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

    Chips Act Revolution

    European Chips Act: What is it?

    In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

    Revolutionized Low-Code

    You Should Use Low-Code Platforms for Apps

    As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

    Cybersecurity Strategy

    Five Powerful Strategies to Bolster Your Cybersecurity

    In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

    Global Layoffs

    Tech Layoffs Are Getting Worse Globally

    Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

    Huawei Electric Dazzle

    Huawei Dazzles with Electric Vehicles and Wireless Earbuds

    During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

    Cybersecurity Banking Revolution

    Digital Banking Needs Cybersecurity

    The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

    FinTech Leadership

    Terry Clune’s Fintech Empire

    Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

    The Role Of AI Within A Web Design Agency?

    In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

    Generative AI Revolution

    Is Generative AI the Next Internet?

    The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

    Microsoft Laptop

    The New Surface Laptop Studio 2 Is Nuts

    The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

    5G Innovations

    GPU-Accelerated 5G in Japan

    NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

    AI Ethics

    AI Journalism: Balancing Integrity and Innovation

    An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

    Savings Extravaganza

    Big Deal Days Extravaganza

    The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

    Cisco Splunk Deal

    Cisco Splunk Deal Sparks Tech Acquisition Frenzy

    Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

    Iran Drone Expansion

    Iran’s Jet-Propelled Drone Reshapes Power Balance

    Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

    Solar Geoengineering

    Did the Overshoot Commission Shoot Down Geoengineering?

    The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

    Remote Learning

    Revolutionizing Remote Learning for Success

    School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

    Revolutionary SABERS Transforming

    SABERS Batteries Transforming Industries

    Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

    Build a Website

    How Much Does It Cost to Build a Website?

    Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted

    Battery Investments

    Battery Startups Attract Billion-Dollar Investments

    In recent times, battery startups have experienced a significant boost in investments, with three businesses obtaining over $1 billion in funding within the last month. French company Verkor amassed $2.1