Re: Scope, context & threadsafety of IsThreadSafe

[Govert van Drimmelen]

Hi Craig,

If you set IsThreadSafe=true in the ExcelFunction attribute, Excel-DNA
will add a $ character to the registration string when registering the
function with Excel (as long as the function is _not_
IsMacroType=true). There are no other effects on the Excel-DNA side.
The $ character is an indication to Excel that the function should be
considered thread-safe and may be called concurrently from different
calculation threads. You can read more about this registration option
at the bottom here: http://msdn.microsoft.com/en-us/library/office/bb687900.aspx
and about Excel's multi-threaded recalculation here:
http://msdn.microsoft.com/en-us/library/office/bb687899.aspx.

If you don't have IsThreadSafe=true for your function, no matter what
the Excel settings, your function will only be called from the main
calculation thread. It might help if you can trace the actual thread
id used in the function calls, to understand better what Excel does.

I don't know how Excel deals with compound formulae that contain
thread-safe and non-thread-safe formulae.

I don't know of any problems in recent versions of Excel-DNA related
to the multi-threading. Excel-DNA just has to ensure that the memory
management for the parameters and return value are thread-safe, and
has no other involvement in the multi-threading story.

The STAThread attibute is related to the COM apartment models, and
should have no effect in this context (and doesn't make sense).

I imagine trying to call into Excel via the COM interfaces
(ExcelDnaUtil.Application) from within a multi-threaded function would
be a bad idea.
Database and back-end service connections need to be carefully
controlled to be thread-safe. The [ThreadStatic] attribute can be
useful to make sure static fields are stored per-thread - just be
careful about the intialization when using this.

If you can make a small, self-contained example that has a problem,
I'd be happy to have a closer look.

Regards,
Govert



On Jan 4, 4:15 pm, craig <cdrobinso...@gmail.com> wrote:
> Hi,
>
> This is bit of a convoluted entry post which may have more of a home on
> stackoverflow or the like in terms of multi-threading, but does have a
> concrete question for excel-dna, so please bear with me.  My system setup
> is probably fairly common amongst other excel-dna users
>
> ·         Windows7 64 bit
>
> ·         .net4.0
>
> ·         MS visual studio 2010
>
> ·         Excel front end – 32 bit Office 2010
>
> The project itself is a bit of a mish-mash. At the lowest level sits a c++
> based dll referenced through an interop, my main coding wrapping this is in
> c#, which is compiled into “myAddin.dll” and the “excel interface project”,
> also in c# and referencing exceldna (v0.30) is called “myAddinXll.dll”.  Thanks
> to Govert’s previous advice this is now packed into a single xll –
> myAddins.xll
>
> In general this all works nicely, until we start to bring multi-threading
> into the picture. All of the time if I uncheck the “enable muli-threading”
> in excel, it works perfectly. If I check the enable multi-threading the
> behaviour can be a bit flaky. Running through the debugger I frequently get
> the dreaded “The message indicated the application is busy” in my logger
> window.  The behaviour is somewhat sporadic however and as always the most
> difficult part is defining the precise problem.  Myself and a colleague
> have expended considerable effort in making our code threadsafe, achieving
> quite a bit more stability, but we ran out of leads – the fact the project
> depends on thirdparty dll’s complicates matter somewhat.  I tracked an
> instability down to a very simple example. A single spreadsheet with 2
> worksheets. On one worksheet there is a call:
>
> =GetInfo(“Erik”,”Official”,BumpDate(today(),”-1w”))
>
> The call gets the “official” info on “erik” as of 1 week ago. Note that the
> today() function is excel’s own volatile function. BumpDate is defined in
> my library and just does the date calculations. The presence of the
> volatile function forces the call on
>
> On other worksheet there is the call
>
> =if(today(),GetInfo(“Erik”,”Official”,25/12/2012))
>
> So in this case the date the information is required for is “hardwired”
> into the function call. The call on the today() function, just forces this
> to be called on a “shiftF9”.
>
> In the single threaded case, i.e. Enable multi-threaded calculation is
> unchecked both of these apparently equivalent calls are fine. However in
> the multi-threaded case the first call fails, whereas the second worked in
> a wholly reproducible way.  I found this interesting and puzzling.  My
> first thought was that the BumpDate function call was giving a rubbish date
> into GetInfo due to an incomplete calculation glitch, but this turned out
> not to be the case.  The underlying function calls in GetInfo in the
> myAddinXll project appear identical. I tried several things, trying to
> enforce single threaded calculation in the underlying GetInfo function and
> tried placing the call in myAddinsXll into a single thread apartment all
> without success:
>
> [STAThread]
>
> [ExcelFunction(Description = "Gets a pricing environment container",
> Category = "myAddins", IsVolatile = false, IsThreadSafe = true, IsMacroType
> = false)]
>
> public static string GetInfo(
>
> [ExcelArgument(Description = "Optional: Name")] object Name,
>
> [ExcelArgument(Description = "Optional: Data type")] object DataType,
>
> [ExcelArgument(Description = "Optional: Date")] object DateEnv,
>
>  Then I started thinking about the ExcelDna qualifiers and IsThreadSafe. I
> switched it to false. And everything worked.  I looked at some more complex
> spreadsheets and although the GetInfo command always seemed to work other
> “downstream” commands which take the output of GetInfo – a handle to the
> stored info in a depot, still seemed to fail. So I made their exceldna
> threadsafe status all false and they all worked. I then turned to true the
> exceldna threadsafe status in the all the function calls and it all worked
> again.  Clearly multi-threading, its techniques, debugging techniques and
> tricks is a very large topic (any tips on this welcome), but to focus on
> the specific issue here I’d like to know more about this flag. What
> precisely is IsThreadSafe doing ?
>
>    - Why did this “work” when all other methods apparently fail ?
>    - What is its scope ? i.e. if I have 1000 excel cell calls on a function
>    that is declared “threadsafe false” then will the sheet run using all the
>    processors available or in single processor mode as I if had unchecked the
>    “Enable multi-threaded calculation”.
>    - If I have chained commands in excel with mixed threadsafe and
>    non-threadsafe excel-dna declarations is this a problem (it appears to give
>    me problems) ?
>
> Maybe this is a red-herring and has nothing to do with excel-dna and is all
> to do with my own code, but it could be worth having an answer to the above
> questions, as it seems to have a fairly powerful effect.  Apologies for the
> verbosity but thought it best to give some proper context.
>
> Best regards
>
> Craig