Browse DevX
Sign up for e-mail newsletters from DevX


Avoid Database Deadlocks with Planning-4 : Page 4




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 Transaction SELECT @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 Transaction RETURN @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.

Drew Georgopulos is the Database Architect for GoAmerica Communications. He moonlights as an Adjunct Professor of Computer Science at Marymount College of Fordham University in Tarrytown, NY teaching systems analysis and relational database design. He holds a certification in systems analysis and relational design from Columbia University, has presented at SQL Connections on SQLDMO, and is a member of the Worldwide Institute of Software Architects.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date