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.

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]