on 2016 Nov 08 6:00 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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.)
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');
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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...
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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?
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:
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)?
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).
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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...
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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.
User | Count |
---|---|
75 | |
10 | |
10 | |
10 | |
10 | |
9 | |
8 | |
7 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.