Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > Tips > SQL Anywhere INSIDER June 2010  
RSS Feed
 
 
 
techtips.jpg

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.

Start Tracing

Using demo10 (10|11|12), start demo10 server and open dbisql window and execute following::

-- clear tracing options
CALL dbo.sa_set_tracing_level( 0 );
--  set option to trace blocking
INSERT INTO dbo.sa_diagnostic_tracing_level ( id, scope, identifier, trace_type, trace_condition, value, enabled )
VALUES( DEFAULT, 'database', NULL, 'plans_with_statistics', 'none', NULL, 1 );
// set tracing option to log SQL statements
INSERT INTO dbo.sa_diagnostic_tracing_level ( id, scope, identifier, trace_type, trace_condition, value, enabled )
VALUES( DEFAULT, 'database', NULL, 'blocking', 'none', NULL, 1 );

-- turn database tracing on
ATTACH TRACING TO LOCAL DATABASE;

Blocking example

Now, in order to simulate blocking, from the same dbisql window run:

UPDATE "GROUPO"."Customers" SET
"Street"='507 Northampton St.',
"City"='Waterloo',
"State"='ON',"Country"='Canada',
"PostalCode"='N2T2N6'
WHERE "ID"=101

then, open second window and run:

UPDATE "GROUPO"."Customers" SET
"Phone"='5197477000'
WHERE "ID"=101;

You can observe that one connection is blocked by the other. (from the first window run):

SELECT * FROM SA_CONN_INFO();

from first dbisql window, you should see similar output to:

eg.

Number,Name,Userid,DBNumber,LastReqTime,ReqType,CommLink,NodeAddr,ClientPort,ServerPort,BlockedOn,LockRowID,LockIndexID,LockTable,UncommitOps
14,'SQL_DBC_16b1c6c0','DBA',0,'2010-06-13 22:22:48.052','EXEC','local','',0,0,1,34996224,,'GROUPO.Customers',0
12,'','dbo',0,'2010-06-13 22:22:48.052','unknown (0)','local','',2347,2638,0,0,,'',0
10,'','dbo',0,'2010-06-13 22:21:55.956','unknown (0)','local','',2346,2638,0,0,,'',0
8,'','dbo',0,'2010-06-13 22:23:16.468','unknown (0)','local','',2345,2638,0,0,,'',0
6,'','dbo',0,'2010-06-13 22:21:55.316','unknown (0)','local','',2344,2638,0,0,,'',0
4,'','dbo',0,'2010-06-13 22:21:54.932','unknown (0)','local','',2343,2638,0,0,,'',0
2,'','dbo',0,'2010-06-13 22:21:54.676','unknown (0)','local','',2342,2638,0,0,,'',0
1,'SQL_DBC_158e5358','DBA',0,'2010-06-13 22:23:15.572','FETCH','local','',0,0,0,0,,'',3

Note, that connection #14 is being blocked by connection 1 on 'Customers' table

Now, you can release locks and stop blocking by executing:

            ROLLBACK;

from both dbisql windows

Save Tracing

From the first window execute:

-- stop tracing and save tracing data
DETACH TRACING WITH SAVE;

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.

Examples:

// 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.

-- show connections which were blocked, with duration in seconds, and the SQL commandselect bl.*,st.statement_text,rq.duration_ms
from sa_diagnostic_blocking bl,sa_diagnostic_request rq,sa_diagnostic_statement st
where
    bl.logging_session_id = rq.logging_session_id
    and rq.logging_session_id = st.logging_session_id
    and bl.request_id = rq.request_id
    and rq.statement_id = st.statement_id;

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

  • blocking_timeout

  • blocking_others_timeout (available in SQL Anywhere 12).

blocking : Controls the behavior in response to locking conflicts.

Default: On

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.

SET TEMPORARY OPTION "blocking" = 'OFF'

UPDATE "GROUPO"."Customers" SET

"Phone"='5197477500'

WHERE "ID"=101;

we would get the following error:

Could not execute statement. User 'DBA' has the row in 'Customers' locked SQLCODE=-210, ODBC 3 State="40001" Line 2, column 1

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.

Default: 0

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.

Example:

-- set blocking back 'ON' SET TEMPORARY OPTION "blocking" = 'ON'; -- set timeout to 10 seconds SET TEMPORARY OPTION "blocking_timeout" = '10000'; UPDATE "GROUPO"."Customers" SET "Phone"='5197477500' WHERE "ID"=101;

and after 10 seconds this error should pop-up:

Could not execute statement.
User 'DBA' has the row in 'Customers' locked
SQLCODE=-210, ODBC 3 State="40001"
Line 2, column 1

In upcoming version 12 of SQL Anywhere a new option was added :blocking_others_timeout option

Could not execute statement. User 'DBA' has the row in 'Customers' locked SQLCODE=-210, ODBC 3 State="40001" Line 2, column 1

blocking_others_timeout

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:

-- set to block to maximum 3 sec
SET TEMPORARY OPTION blocking_others_timeout = '3000';
-- run the first update
UPDATE "GROUPO"."Customers" SET
"Street"='508 Northampton St.',
"City"='Waterloo',
"State"='ON',"Country"='Canada',
"PostalCode"='N2T2N6'
WHERE "ID"=101;

then from the second window:

UPDATE "GROUPO"."Customers" SET
"Phone"='5197477500'
WHERE "ID"=101;

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.

Could not execute statement.
Rollback occurred due to blocking_others_timeout
SQLCODE=-1344, ODBC 3 State="40001"
Line 2, column 1

Summary

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”

(http://iablog.sybase.com/paulley/2010/02/diagnosis-of-blocking-with-application-profiling/ ).

SQL Anywhere documentation on “Advanced application profiling using diagnostic tracing”

(http://dcx.sybase.com/index.html#1101en/dbusage_en11/perform-s-5787380.html)


 

DOCUMENT ATTRIBUTES
Last Revised: Jun 24, 2010
Product: SQL Anywhere
Technical Topics: SQL Anywhere
  
Business or Technical: Technical
Content Id: 1080460
Infotype: Tips
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics