"All threads are blocked" using External function with ODBC connection

[Former Member]

Hi,

We have issues calling external functions in our database. It seems to be related to the fact that we initiate the procedure calls from an external .Net application using the System.Data.Odbc provider. When we call the same procedure multiple times, we sometimes get errors with the message : ERROR [40001] [Sybase][ODBC Driver][SQL Anywhere]All threads are blocked

The external function is :

create function "dba"."az_get_daylight_time"( in "arg_timestamp" timestamp ) 
returns bit
external name 'ExternFunction.dll::ExternFunction.ExternalFunction.IsDaylight(datetime) bool' language "clr"

And the C# code executed in the dll is :

public static bool IsDaylight(DateTime dt)
    {
        bool bIsDaylight = dt.IsDaylightSavingTime();
        return bIsDaylight;
    }

It is obviously used to determine if the specified datetime is in daylight saving time range or not.

We use the latest version of SQL Anywhere 16 (16.0.0.2305).

We are able to reproduce the issue in a simple .Net Console application. I will post the source code here if it can help understand the problem.

Any idea how we can fix this while still using ODBC connections?

Regards

[VolkerBarth]

How do you call that external function? Is it run on its own statement (such as "SELECT "dba"."az_get_daylight_time"(dtMyDate);" or as part of another query, and therefore possibly queried several times?

You might add the DETERMINISTIC clause to the CREATE FUNCTION definition in case that fits here.

[VolkerBarth]

We use the latest version of SQL Anywhere 16 (16.0.0.2305).

Note, there are newer versions available for Windows, up to 16.0.0.2344.

[Vlad]

May I get the code of the simple console application and the sample DB reload.sql?
I'd like to reproduce the issue.

[Former Member]

I just posted it as an answer to make it clear.

[jack_schueler]

I can also reproduce this with the following non-ASP code.

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Odbc;

namespace Project2
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                for (int i = 0; i < 100; i++)
                {
                    String sSql = String.Format("select az_get_daylight_time('{0}')", DateTime.Now.ToString("yyyy-MM-dd"));
                    Console.WriteLine(SelectCommand(sSql));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private const String DATABASE_CONNECTION = "Driver=SQL Anywhere 17;ENG=JackDemo;LINKS=TCPIP(HOST=127.0.0.1);Uid=dba;Pwd=sql;";

        public static String SelectCommand(String szCommand)
        {
            String result = "<null>";
            OdbcConnection conn = new OdbcConnection(DATABASE_CONNECTION);
            conn.Open();
            OdbcCommand command = new OdbcCommand(szCommand, conn);
            try
            {
                OdbcDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    result = "Daylight is " + reader.GetInt32(0);
                }
                reader.Close();
            }
            catch (OdbcException ex)
            {
                Console.WriteLine(ex.ToString());
            }
            conn.Close();
            return result;
        }
    }
}

System.Data.Odbc.OdbcException (0x80131937): ERROR [40001] [SAP][ODBC Driver][SQL Anywhere]All threads are blocked
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcDataReader.Read()

My first attempt at a reproducible connected once and iterated the command 1000 times with no issue and then disconnected.

This version is connecting/disconnecting on every iteration. I'll check into this.

[Vlad]

My application shows this error 😞

[Vlad]

I have forgot to added that CLR configuring took few hours 😞 Meaningless errors such as "object reference couldn't be found" are so disappointing. But this is just the feedback from me.

[jack_schueler]

I understand it can be a mysterious process.

Basically, to avoid problems, I run SetupVSPackage to remove and reinstall the provider version I want, for example version 4.5.

Then the compile step.

csc /target:library /out:ExternFunction.dll isDaylight.cs

Then a bit of SQL.

ALTER EXTERNAL ENVIRONMENT CLR LOCATION 'dbextclr[VER_MAJOR]_v4.5';

CREATE OR REPLACE FUNCTION az_get_daylight_time( IN arg_timestamp timestamp ) RETURNS BIT EXTERNAL NAME 'ExternFunction.dll::ExternalFunction.IsDaylight(datetime) bool' LANGUAGE CLR;

And you're ready to go.

This doc page should explain most of this: dcx.sap.com/index.html#sqla170/en/html/816042a36ce210148494fb71e43bc865.html

[Vlad]

Everything goes smoothly until you start CLR, and all of a sudden some .NET class crash because of meaningless errors. More joy I felt, when I realized that even if you are able to start CLR, it doesn't mean that your .DLL fill be loaded and used properly. No-no, the Java integration is better.

[Former Member]

Does anyone thinks a solution will be found soon?

If not we will need to implement a workaround (using Oledb Provider seems the be the easiest).

Regards

[VolkerBarth]

Can't tell, but if all you need to know is whether the current timezone is using daylight saving time or not, and the CLR environment seems to behave undesired here, you could "simply" use a C-based external function that calls the GetTimeZoneInformation WINAPI. I'm quite sure the C-based environments don't require too many workers...

(Note: I had not said that it's simple to write a C-based external function.)

[jack_schueler]

While we are looking into this problem, consider this workaround implemented in C++ (well mostly C).

#include "extfnapi.h"
#include <stdio.h>
#include <time.h>

// cl isDaylight.cpp /LD /IC:\\sa17\\sdk\\include

#define DLL_FN __declspec(dllexport)

extern "C"
DLL_FN unsigned int extfn_use_new_api( )
{
    return( EXTFN_API_VERSION );
}

extern "C"
DLL_FN void isDaylightSavings( an_extfn_api *api, void *arg_handle )
{
    an_extfn_value      args[1];
    an_extfn_value      retval;
    time_t              secs;
    struct tm           tmbuf;
    int                 i;
    int                 isdaylight = -1;
    int                 msecs;
    char                sep;
    char                buf[33];

    for( i = 0; i < 1; i++ ) 
    {
        if( ! api->get_value( arg_handle, i+1, &args[i] ) ) break;

        if( args[0].type != DT_VARCHAR ) break;

        tmbuf.tm_wday = 0;
        tmbuf.tm_yday = 0;
        tmbuf.tm_isdst = -1; // force Daylight Savings Time determination

        sscanf( (const char *)args[0].data, "%d%c%d%c%d %d%c%d%c%d%c%d",
                &tmbuf.tm_year,
                &sep,
                &tmbuf.tm_mon,
                &sep,
                &tmbuf.tm_mday,
                &tmbuf.tm_hour,
                &sep,
                &tmbuf.tm_min,
                &sep,
                &tmbuf.tm_sec,
                &sep,
                &msecs );
        tmbuf.tm_year -= 1900;
        tmbuf.tm_mon  -= 1;
        secs = mktime( &tmbuf );
        localtime_s( &tmbuf, &secs );
        isdaylight = tmbuf.tm_isdst;
    }        
    retval.type = DT_INT;
    retval.data = (void*) &isdaylight;
    retval.piece_len = retval.len.total_len = (a_sql_uint32) sizeof( int );
    api->set_value( arg_handle, 0, &retval, 0 );
    return;
}

The SQL interface is (you'll have to change the path below):

CREATE OR REPLACE FUNCTION isDaylightSavings( IN datetimestr VARCHAR(32) )
RETURNS INT
EXTERNAL NAME 'isDaylightSavings@c:\\\\c\\\\isDaylight.dll' LANGUAGE C_ESQL64;

and the expected argument is a string, for example:

SELECT isDaylightSavings('2016-03-13 01:00:00.000000'), 
       isDaylightSavings('2016-03-13 03:00:00.000000'), 
       isDaylightSavings('2016-11-06 00:59:59.000000'), 
       isDaylightSavings('2016-11-06 01:00:00.000000');

[Former Member]

Thanks, we will try this soon.

[Former Member]

We finally managed to get this to work. It does seem to be a good workaround for the "All thread are blocked" issue. Thanks.

However, the execution is still pretty slow using the ODBC provider and the SAConnection object (using connection pooling). Execution is much faster using Oledb provider (close to 200 times faster in my sample application). Is this normal? Or maybe it should be the subject of another question on this forum?

[VolkerBarth]

The sample by Jack does not use any database connection at all, so that cannot depend upon the fact which DB provider is used, so I'd suggest to ask that as a separate question...

If you have a single query that involves many calls of an external function, (say, calling that function for each column of a huge result set), I would expect that to be an expensive query by design...

[Former Member]

The sample application I used to test this solution and the performance is the same as before (I posted the code here). The query only calls the external function once...

I will post a new question about that in the near future.

[Breck_Carter]

Is there some reason to avoid using the technique shown in the SQL Anywhere 17 CLR samples folder?

C:\\Users\\Public\\Documents\\SQL Anywhere 17\\Samples\\SQLAnywhere\\ExternalEnvironments\\CLR

using Sap.Data.SQLAnywhere;
using Sap.SQLAnywhere.Server;

[VolkerBarth]

What exactly sample/technique do you mean? Server-side requests?

Oops, apparently using the particular SQL Anyhwere-related objects, that is.

[Former Member]

I will try using the SQLAnywhere objects soon and will post the result here.

[Former Member]

Same problem happens using Sap.Data.SQLAnywhere.SAConnection object when I open and close a new connection for each database call.

Using the same connection for every call is very difficult for us because of the way our c# applications are designed...

[VolkerBarth]

Using the same connection for every call is very difficult for us because of the way our c# applications are designed...

In my understanding, your .NET application should make use of the default connection pooling (the link is for v17, see Jack's comments, but should apply to v16, too) automatically - so even if you constantly create and drop connections, they should be taken from the pool...

[Former Member]

Same problem happens using the following connection string with the SAConnection object: "Data Source=QcDemo1_heblou_16;Pooling=true;Max Pool Size=100;Min Pool Size=5;UserID=dba;Password=pass"

And the documentation states that connection pooling is enabled by default so I guess that did not change much. Am I missing something?

[VolkerBarth]

I just wanted to know whether your app does use connection pooling or not (as that should at least be more efficient when you constantly create and drop connections). And yes, as stated, it's the default for ADO.Net connections.

AFAIK, there's no connection property that shows whether you are using pooled connections or not, but I guess sa_conn_info() should reveal that when monitored during the lifetime of your application:

• The connection number should stay the same for you app's connection
• RegType should be "CONNECT_POOL_CACHE" now and then...

---

But obviously that's not the main question here. As long as your external CLR call leads to deadlocks, pooling seems irrelevant...

---

Have you considered to use a non-CLR-based approach to get the daylight saving time property (say, via C) - see my according comment on Jacks's answer)?

[Former Member]

I am not an expert on the subject but when I start my sample application using the previously mentioned connection string, 5 connections appear in Sybase Central. I think that means the application does use connection pooling.

And yes, using non-CLR-based functions seems to be a good workaround. Thank you for the tip. We will probably try it soon. However it would be sad to stop using CLR-based functions because it seems "buggy" (mainly because we have experienced .Net developers here and we don't have experienced C developers).

[VolkerBarth]

As to the "buggy" behaviour:

See, Jack himself has stated "I'll check into this." in his answer. Given that and Jack's position within the SQL Anywhere team, I'd surely expect that this will be dealt with...

Note, I'm just another customer, so that's my expectation.

[Former Member]

Here is the code of the sample application I made to reproduce the issue. It uses .Net Framework 4.

using System;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ConsoleApplication1
{
class Program
{
    static void Main(string[] args)
    {
        try
        {
            for (int i = 0; i < 100; i++)
            {
                String sSql = String.Format("select * from proc_call_get_daylight('{0}')", DateTime.Now.ToString("yyyy-MM-dd"));
                Console.WriteLine(SelectCommand(sSql)[0][0]);
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

    private const String DATABASE_CONNECTION = "Provider=SAOLEDB.16;Driver=Sql Anywhere 16;ENG=DemoDB;LINKS=TCPIP(HOST=127.0.0.1);Uid=dba;Pwd=password;";
    // The same problem happens using this connection string
    //private const String DATABASE_CONNECTION = "DSN=DemoDB;Uid=dba;Pwd=password;";
    private const String DATABASE_PROVIDER_NAME = "System.Data.Odbc";

    public static DataView SelectCommand(String sCommand)
    {
        SqlDataSource sds = new SqlDataSource
        {
            ConnectionString = DATABASE_CONNECTION,
            ProviderName = DATABASE_PROVIDER_NAME,
            SelectCommandType = SqlDataSourceCommandType.Text,
            SelectCommand = sCommand
        };
        return (DataView)sds.Select(DataSourceSelectArguments.Empty);
    }
}
}

And the sample procedure :

create or replace PROCEDURE dba.proc_call_get_daylight(in arg_timestamp timestamp)
result (res_isdaylight char(3))
begin
  select if dba.az_get_daylight_time(arg_timestamp) = 1 then 'Yes' else 'No' endif;
end;

Edit : One little note that may help to find the problem, if we change the Provider name to "System.Data.Oledb", the issue does not happen and the execution is much faster.

[Breck_Carter]

FWIW your code calls SelectCommand 100 times inside a loop, and each instance of SelectCommand opens a new SQL Anywhere connection... that may not be the behavior you need.

If increasing -gn does not help, there is most likely a runaway loop in your code.

[Vlad]

I appreciate that you gave us the code. There is a single thing that i do not like - SqlDataSource in the console application.
I cannot explain why I do not like it, probably because you are mixing web and winforms in a single application.
I am currently trying to reproduce the issue, but I'd like to use the Odbc approach as MS recommends: https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcconnection(v=vs.110).aspx

[Former Member]

Would you like me to adapt the code using the recommended approach and post it here?

[VolkerBarth]

and each instance of SelectCommand opens a new SQL Anywhere connection.

Just hopping into the topic: Is the SQL Anywhere .NET Provider supposed to use connection pooling by default, so at least it should re-use connections here?

[Vlad]

If you look at my image below, the server has only a single connection from .NET.

[VolkerBarth]

Hm, that does not really answer my question, even one single connection at a time can be taken from a connection pool, in which case it should be much "cheaper" compared to a situation where a single "normal" connection gets created, used and closed repeatedly in a loop.

But in case the program just creates one single connection during its whole runtime, my point is moot. I don't know whether this is true here, if so, it would be cumbersome why then the engine would run out of workers...

[Vlad]

No need to do this, because I just took an average Odbc example in .NET, and got what you showed us. Thanks.

[Vlad]

Can you please suggest me what to check? Because yesterday night, I just looked at the number of active connections and didn't find anything unusual.

[Breck_Carter]

That image may just be showing what the server sees AFTER all the connections have been dropped.

Why do you execute "new SqlDataSource" one hundred times? That may cause SQL Anywhere to use that many threads (or possibly two-times that many threads).

[former_SQLA_member1694885]

Hi Louis-Joel,

first: Are you familiar with the thread deadlock (http://dcx.sap.com/index.html#sa160/en/dbusage/deadlock.html*d5e46598) phenomenon?
The usual ways to deal with this is to either reduce the number of blocked processes or (with the network server engine) to increase the number of server threads (aka "multiprogramming level"; -gn / -gna command line options).
Assuming that your call shouldn't last too long (what's the expected run time?), the error situation seen indicates that it is. Is the external function you call capable of multi- thread operation? If it isn't, your call may be stalled inside the external function, which will be propagated to the database engine. sa_conn_info or sa_conn_properties may return useful information to narrow this down.
Can you debug the external function on c# level?
Unfortunately, I can only ask more questions rather than provide an answer. HTH Anyway.

Volker Stöffler
DB-TecKnowledgy

[VolkerBarth]

AFAIK, an external environment will require at least one worker thread within the database engine to communicate with the external environment (and probably one other for maintenance of the external environment), and a further one to handle the requests from that external process. AFAIK, only the connections handling requests are shown with sa_conn_info(), so there might be more workers in use than are shown which makes it quite difficult to tell the number of worker threads involved...

[Former Member]

Hi,

Yes I made some research on the subject since the error message was indicating a thread deadlock issue. However, I don't understand how we can have such an issue since AFAIK the procedure calling the external function is never called twice at the same time. That is because the process calling the procedure always waits for the result before calling it again (and that process is not multi-threaded).

I tried setting the -gn option to some hi values like 100, 150, 200 but it did not fix the issue.

The external call should not last long since it only executes the code mentioned above. However, the call seems to take close to 2 seconds when I run it using the sample application with Sybase Central Profiling. This is not expected...

I don't know if the external function is multi thread capable but again, it is not supposed to be called again before the previous call ended.

I am actually not able the debug the external function.

I just posted the code of the sample application that reproduce the issue on my database. I hope that will make it clear for everyone.