Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


27 - Find Blocked Connections With System Functions

From time to time one connection can be blocked from continuing because a piece of data is locked by another connection. This problem goes away as soon as the lock is released but if that doesn't happen right away performance drops to zero for the affected connection. In effect the blocked application becomes completely catatonic.

Figure 27A shows an example of a simple connection monitor written in PowerBuilder. This program checks all the current connections to see if there are any problems caused by locking. In this example it has found that user BSHIN has a connection that is blocked from proceeding by user BCARTER.

Figure 27A - Find Blocked Connections Via PowerBuilder

This program calls a series of SQL Anywhere system functions to find blocked connection pairs and their corresponding user ids as follows:

  1. Call Next_Connection ( NULL ) to get the first connection number xxx.
  2. Call Connection_Property ( 'BlockedOn', xxx ) to get the blocking connection number yyy if any.
  3. Call Connection_Property ( 'UserID', xxx ) to get the user id.
  4. Call Connection_Property ( 'UserID', yyy ) to get the blocking user id if any.
  5. Call Next_Connection ( xxx ) to get the next connection number xxx.
  6. Repeat from Step 2 until xxx is empty.
  7. Display blocked connections if any.

The code for the PowerBuilder function is shown in Figure 27B.

Figure 27B - PowerBuilder Function f_check_for_block()

// f_check_for_block()

// Displays warning if there are any blocked connections.

// Returns 0 if there are no blocked connections

// nnn if there are nnn blocked connections

// -1 if some other error was detected

long ll_this_connection

long ll_blocked_by_connection

string ls_this_userid

string ls_blocked_by_userid

long ll_previous_connection

string ls_warning

long ll_blocked_count

ls_warning = ""

ll_blocked_count = 0

SQLCA.DBMS = "ODB"

SQLCA.DBParm = "ConnectString='DSN=TinyDB;UID=DBA;PWD=gzornenplatz'"

CONNECT USING SQLCA;

IF SQLCA.SQLCode <> 0 THEN

MessageBox ( "Error", "CONNECT failed" )

RETURN -1

END IF

// Get the first connection number.

ll_this_connection = 0

SELECT Next_Connection ( NULL )

INTO :ll_this_connection

FROM sys.dummy

USING SQLCA;

IF SQLCA.SQLCode <> 0 THEN

MessageBox ( "Error", "SELECT first connection failed" )

RETURN -1

END IF

IF IsNull ( ll_this_connection ) THEN

ll_this_connection = 0

END IF

DO WHILE ll_this_connection <> 0

// Check for the number of the connection that's blocking this one.

ll_blocked_by_connection = 0

SELECT Connection_Property ( 'BlockedOn', :ll_this_connection )

INTO :ll_blocked_by_connection

FROM sys.dummy

USING SQLCA;

IF SQLCA.SQLCode <> 0 THEN

MessageBox ( "Error", "SELECT BlockedOn failed" )

RETURN -1

END IF

// Record information about the blocked connection.

IF ll_blocked_by_connection <> 0 THEN

ll_blocked_count++

// Get the userids for the two connections.

ls_this_userid = ""

ls_blocked_by_userid = ""

SELECT Connection_Property ( 'UserID', :ll_this_connection ),

Connection_Property ( 'UserID', :ll_blocked_by_connection )

INTO :ls_this_userid,

:ls_blocked_by_userid

FROM sys.dummy

USING SQLCA;

IF SQLCA.SQLCode <> 0 THEN

MessageBox ( "Error", "SELECT UserIDs failed" )

RETURN -1

END IF

// Record information about the blocked connection.

IF ls_warning <> "" THEN

ls_warning = ls_warning + "~r~n"

END IF

ls_warning = ls_warning + "User " &

+ Upper ( ls_this_userid ) + " (" &

+ String ( ll_this_connection ) &

+ ") is blocked by " &

+ Upper ( ls_blocked_by_userid ) + " (" + &

+ String ( ll_blocked_by_connection ) + ")"

END IF

// Get the next connection number.

ll_previous_connection = ll_this_connection

ll_this_connection = 0

SELECT Next_Connection ( :ll_previous_connection )

INTO :ll_this_connection

FROM sys.dummy

USING SQLCA;

IF SQLCA.SQLCode <> 0 THEN

MessageBox ( "Error", "SELECT later connection failed" )

RETURN -1

END IF

IF IsNull ( ll_this_connection ) THEN

ll_this_connection = 0

END IF

LOOP

DISCONNECT USING SQLCA;

IF SQLCA.SQLCode <> 0 THEN

MessageBox ( "Error", "DISCONNECT failed" )

RETURN -1

END IF

IF ls_warning <> "" THEN

MessageBox ( "Warning", ls_warning )

END IF

RETURN ll_blocked_count


[Home] [Table of Contents] [Previous Section] [Next Section]