Tip

Sybase ASE on Linux: Getting rid of database deadlocks

A deadlock can occur in Sybase Adaptive Server Enterprise (ASE) when two user processes each have a lock on a separate page or table and each wants to acquire a lock on the same page or table held by the other process. The transaction with the least accumulated CPU time is killed and all of its work is rolled back.

There are ways to avoid deadlocks. These are general good practices:

  • Write transactions to acquire locks in the same order on the system. This will help to reduce both lock contention and deadlocks.
  • Write only those statements which must be committed or rolled back as a group within a transaction. This will help reduce lock contention and deadlocks.
  • Avoid hot spots. Hot spots are those points in the data or index where many queries access. If too many queries are accessing the same pages for Selects and DML operations, then lock contention and/or frequent deadlocks could result. For example, use a clustered index on the table to avoid heap.
  • Avoid heap tables (i.e. tables with no clustered index). They may cause many issues, including deadlocks, because of the hot spot. That is a "hot spot" on the last page of the table leading to lock contention and frequent deadlocks.
  • Applications that are not impacted by dirty reads (isolation level 0), may have better concurrency and reduced deadlocks when accessing the same data by setting

Requires Free Membership to View

  • transaction isolation level 0 at the beginning of each session. An example is an application that finds the momentary average balance for all savings accounts stored in a table. Since it requires only a snapshot of the current average balance, which probably changes frequently in an active table, the application should query the table using isolation level 0.
  • Those tables commonly involved in deadlocks are candidates for row-level locking (datarows or datapages).
  • Use stored procedures, as opposed to dynamic SQL, where feasible. There are many advantages to using stored procedures including: minimize blocks and deadlocks. it is a lot easier to handle a deadlock if the entire transaction is performed in one database request, also locks will be held for a shorter time, improving concurrency and potentially reducing the number of deadlocks. Furthermore, it is easier to ensure that all tables are accessed in a consistent order if code is stored centrally, rather than dispersed amongst a number of applications.
  • On OLTP systems, avoid creating a clustered index on a sequential key on a transaction busy table. This would create a hot spot on the last page of the table. In addition, despite row-level locking, this can still cause frequent deadlocks and blocks.
  • Avoid select alter #table, create #tables with constraints and defaults.

This was first published in August 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.