June 2010 Edition
How to Debug Blocking in a SQL Anywhere Database
Blocking is unavoidable for any busy database system. In SQL Anywhere, blocking occurs when one connection holds a lock on a specific row or table at the same time a second connection attempts to acquire a lock on the same row or table. When the first connection releases the lock, the second connection is free to acquire its own lock and continue processing. Although, this is normal behavior and can happen many times throughout the course of a day, in general it should be minimized so applications that rely on the database can operate efficiently.
It is the designer's (DBA) responsibility to make sure that the database operates in such a way that allows an application to efficiently utilize all resources and to avoid wasting time waiting for database locks. Monitoring blocking patterns is good practice for keeping the database server well tuned and to a provide high level of serviceability to the end users. It's is also a recommended practice in order to proactively eliminate possible performance problems in the database.
The following article discusses examples of blocking in version SQL Anywhere 10 or later. It also shows connection options that control blocking behavior, and describes commands that can be used from dbisql to diagnose blocking issues.
Debugging blocking using database tracing
Since version 10, SQL Anywhere provides a great tool designed to help debug and diagnose database performance problems called “Database Tracing”. This example shows how to use SQL commands to: turn tracing on, collect required information, and analyze the information with simple SQL commands. It is specially recommended for system administrators that are running SQL Anywhere but do not always have Sybase Central deployed with their installations.
Using demo10 (10|11|12), start demo10 server and open dbisql window and execute following::
Now, in order to simulate blocking, from the same dbisql window run:
then, open second window and run:
You can observe that one connection is blocked by the other. (from the first window run):
from first dbisql window, you should see similar output to:
Note, that connection #14 is being blocked by connection 1 on 'Customers' table
Now, you can release locks and stop blocking by executing:
from both dbisql windows
From the first window execute:
Analyze blocking patterns
Once all tracing data is saved in the database, it can be easily retrieved by running the following SQL statements in any of the dbisql windows.
// an example of connection 1 being blocked by connection 2 , with both SQL statements executed, from what time to what time and the duration , and block on what table if possible.
Database options related to blocking
When analyzing blocking on SQL Anywhere database it is important to be aware about the connection options that control how connections behave during a blocking situation. These options are:
blocking : Controls the behavior in response to locking conflicts.
If the blocking option is set to “On”, any transaction attempting to obtain a lock that conflicts with an existing lock held by another transaction waits until every conflicting lock is released or until the blocking_timeout is reached. If the lock is not released within blocking_timeout milliseconds, then an error is returned for the waiting transaction. If the blocking option is set to “Off”, the transaction that attempts to obtain a conflicting lock receives an error.
Using our blocking example, if we set the blocking option to “OFF” in the second 'UPDATE', e.g.
we would get the following error:
In the situation when we don't want users to 'wait' for locks, we could pop-up a message box and for example instruct a user to try later or implement a retry logic in our code.
Another important option to be mentioned is blocking_timeout
blocking_timeout : It simply controls how long a transaction waits to obtain a lock.
Again, in our example if we would set this option to for example 10000 ( ms), then connection would wait up to 10 seconds and then, error with SQLCODE = -210.
and after 10 seconds this error should pop-up:
In upcoming version 12 of SQL Anywhere a new option was added :blocking_others_timeout option
This option specifies the amount of time that one connection can block other connections before the current connection is rolled back. This option can be used to prevent a low priority task from blocking other connections for longer than the specified time.
Assuming you've already downloaded BETA version of SQL Anywhere 12. In the same blocking example as above but using demo12 database, execute following from dbisql:
then from the second window:
as soon as the second update runs, it would get blocked but for the maximum of 3 seconds, because the blocker’s ‘blocking_others_timeout’ is set to 3 seconds. After 3 seconds, first connection would rollback, and would need to be restarted. If not, the following error would occur.
Blocking in the database can be a silent performance degradation factor which is hard to detect without some in-depth knowledge about the application and database engine. Since blocking can lead to users occasionally complaining about response time, it is an area that should be looked into. With some time and little effort such as a code change or a schema change, big improvements to the overall database performance can be achieved.
This example just slightly discussed tracing tools in SQL Anywhere. For more information please see:
Dan Farrar’s whitepaper on Application Profiling (http://www.sybase.com/detail?id=1060302)
Glenn Pauley’s blog on “Troubleshooting blocking problems”
SQL Anywhere documentation on “Advanced application profiling using diagnostic tracing”