Multithreading

Question

It's been stated in several threads that the Excel object model can be accessed from multiple threads as long as exceptions are caught and retried. However, when I do the following, I get a hang as soon as the child thread starts to assign to the status bar. No exceptions are thrown, it just hangs. (The same thing happens if I also define and register a IMessageFilter, and again the RetryRejectedCall method is never invoked). How can I access the Excel object model from both threads without risking a hang?

        private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
            Thread t = new Thread(new ThreadStart(count));
            t.SetApartmentState(ApartmentState.STA);
            t.Start();
            count();
        }

       public count()
       {
            for (int i = 0; ; i++)
            {
                try
                {
                    Application.StatusBar = string.Format("count {0}", i);
                    break;
                }
                catch (COMException)
                {
                    if (i > 100)
                    {
                        throw;
                    }
                    continue;
                }
            }
       }

Answer 1

Hi Mike,

 

Are these codes above exactly what you run in your IDE? I copied them into both of my VSTO 2005 Excel document project and VSTO 2008 Excel document project. The codes refuse to compile for there’s no return type before the count function. 

 

After I add the term void and import some namespace to get the codes compiled. It runs fine in my side. Excel works OK with “count 0” showed in its status bar. And there is no hanging through my many times test.

To make it more clear about when the new thread starts to run. I change your codes in Startup method to the following:

Code Block

                        Thread t = new Thread(new ThreadStart(count));
            t.SetApartmentState(ApartmentState.STA);
            t.Start();
            Application.StatusBar = "Main UI Thread";

 

After the modification, Excel starts with status bar “Main UI Thread” followed by ”count 0”. This proves that the new child thread has already started. But Excel works fine, no hangs, and no exception.

 

As to your scenario, I think, child thread should not result in Excel hangs. That Excel hangs means the Main UI Thread is in a long process or tries to access some unavailable resource. Your calling count() method in ThisWorkbook_Startup may be problematical. So, try to modify it to Application.StatusBar = “Main UI Thread” to see if hangs of Excel still persists.

 

And the for loop seems to be the most potential place that caused the hang problem. But if there is no exception thrown continuously, the loop will be quite since you use a break statement.

Hope this information may help. If you still encounter such issue. Could you please clarify the exact codes you are working on. I guess you are minimize the codes and try to simplify the issue, right?

 

 

 

Thanks

Ji

Answer 2

Sorry, Ji, I missed your response and am slow in responding. You were correct that I pasted in improperly simplified code  from my full program. I was simply trying to create the simplest possible program that manipulates the Excel object model from two different threads.

The count function should look like:

        public void count()
        {
            for (int i = 0; ; i++)
            {
                try
                {
                    Application.StatusBar = string.Format("count {0}", i);
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    continue;
                }
            }
        }


Now, the program hangs as soon as count() is running in both threads. IOW, simply updating the status bar from two threads causes the program to hang. Any suggestions?

Thanks,

Mike

Answer 3

Hi Mike,

 

For starters if you are going to handle the exception in this fashion, you need to explicitly check the ErrorCode property for RPC_E_CALL_REJECTED (0x80010001).  As it stands, you code will eat any Com exception--which is not correct.  You might consider implementing IMessageFilter instead (the Com version, not the Windows Forms version) and registering it via the Com CoRegisterMessageFilter function. Doing this allows you to provide a single implementation for handling rejected calls (IMessageFilter::RetryRejectedCall) rather than having to handle the exception on each call. 

 

In addition, why are you declaring your thread as STA?  There are n single threaded apartments, so doing this just creates a new apartment for your thread.  Since your thread will be in a different STA than Excel, this will not improve marshalling performance.  More importantly, STA threads *must* pump messages.  The marshalling protocol for STAs is message based so if you don't pump messages, you will never receive your calls.  This isn't likely to hurt you in your example, but it will prevent you from establishing an RPC channel to your thread.  Unless you have some explicit reason to want your thread to be STA, you should just avoid setting ApartmentState (you will get MTA by default).  The marshalling costs will be the same.  OTOH, if you need STA for some reason, then you will need to create a message loop for your thread.

 

Now, in terms of the hang...The simplest way to get to the bottom of things is to just break in with the debugger and look at the call stacks for both threads.  Since Excel is STA and there are no callbacks going on, it seems fairly unlikely to me that you are experiencing a real hang.  In order to get a deadlock, you would need Excel blocked on an outgoing call to a thread that was also blocked on an outgoing call back to Excel.  Due to the mutual blockage, the deadlock would never resolve and you would have a true hang. 

 

More than likely what is going on is the fact that you are executing a loop on the main UI thread without allowing messages to be processed is giving the appearance of a hang.  Probably, the UI is never updating (so it appears unresponsive) and the fact that you are preventing messages from being processed means that incoming cross apartment calls will never be processed either.  So your background thread is blocked (and will be until Excel starts pumping again).  To unblock things, you would need to pump messages within the loop (at least the loop that runs on the Excel thread).  Typically, you would do this by calling the Win32 PeekMessage and then calling TranslateMessage, followed by DispatchMessage as long as the PeekMessage call returns true.  You could probably get away with calling the Windows Forms, Application.DoEvents--but I hesitate to recommend that approach because VSTO is not actually a Windows Forms application and this method relies somewhat on knowledge of how DoEvents is implemented (which could very well change in future versions and break you). 

 

Sincerely,

 

Geoff Darst

Microsoft VSTO Team

Answer 4

Hi Geoff,
Thanks for your response but it doesn't seem to fix the issue. I get the same problem if I just run count() in two child threads and not in the main thread and have confirmed in the debugger that ThisWorkbook_Startup returns. I presume that the main thread message pump can then process messages. While my app is different from this little thread exerciser, I am afraid to use multithreading if I can't even make it work for the simple case of having two threads access the Excel status bar.

Some other comments:
The debugger will not break once the hang has occurred, so I can't find the problem that way.

Getting rid of the SetApartmentState has no effect.

I had the same problem when I used an IMessageFilter (the callback was never called). I did find in other tests that sometimes a COMException occurred even with an IMessageFilter enabled, which meant I had to do a try block anyway. Have you heard of this occurring?

Answer 5

Hi Mike,

 

Interesting.  This is about as simple of a threading example as you can generate and I can't think of a theoretical reason as to why it could cause a hang.  Since you are also unable to break in the debugger and since this example works fine on both my machine and Ji's machine, I'm inclined to believe there is something else going on that is unrelated to your code.  If you have another machine available for testing, now would be the time to try it.

 

Even if you can't get a debugger attached, you can confirm the presence of a hang by going to task manager and examining the CPU column for Excel.  If this is at zero, you truly do have a hang.  If you are showing some CPU activity then you know at least some threads are unblocked and running.  If your count() function is running, the CPU activity should be fairly high.

 

As far as getting the debugger to break, does debugging work for you generally?  If so, try putting a breakpoint in count and see if you hit it.  If need be you can condition it on i reaching a certain value so you can delay when you break.  You might also try putting a System.Diagnostics.Debugger.Break() call in your code to try to force a break.  Another thing to try would be to do a debug attach instead of a launch.  Instead of hitting F5 to launch your customization from with Visual Studio, try hitting Ctrl+F5 to start Excel without the debugger.  Then go to the Tools menu, choose "Attach to Process" and attach to the running Excel instance.  It may be that you will be able to break in via this mechanism.  If none of that works, let me know and we can talk about trying to use WinDbg to grab a dump.  One way or another, we need to get at a callstack to know definitively what is going on.

 

Are you having any other wierd issues with this machine?  Oddly enough, one thing that can produce a hang is a bad hard drive.  If the operating system isn't able to read memory that it has paged back from disk, you can end up with a hang.  You may want to investigate along those lines. 

 

On my own machine, I had no problems making any permutation of this code work.  I did some testing with VSTO 3.0 / Excel 2007 and here's what I can tell you.  First, Application.StatusBar will update the status bar immediately.  That is, you do not need to pump in order to see the update.  So in the initial example you gave, what happens is that count() runs on the Excel main UI thread, but the count() on the background thread remains permanently blocked (count is zero) because no messages are being pumped while Excel is in the count() loop.  This is as expected.  At some point, the status bar quit updating on me, but there was no hang--I could continue to step through the loop in the debugger. 

 

Adding a System.Windows.Forms.Application.DoEvents call to the end of the loop (which as I mentioned, I don't recommend--but it works for testing) is sufficient to allow RPC message processing to unblock the background thread.  Alternatively, you second approach of running count() on two different background threads accomplished the same thing (since Excel is able to pump messages).

 

Some additional comments.  If you register an IMessageFilter, you will not get RPC_E_CALL_REJECTED errors back from a Com call.  However, you will still get any other failures generated by the call itself and some of these may be marshalled as ComExceptions.  The problem is that none of these will be documented, so you will have to determine whether there are any interesting exceptions by trial and error.  In cases where you expect that a call can fail with a specific COM exception and there is no way to check for the failure case ahead of time, it would make sense to wrap the call in a try/catch block. 

 

Finally, while I don't want to discourage you from trying to use multiple threads, you should be very clear on what you are trying to accomplish.  Because STA serializes all incoming calls, you do not gain a performance benefit from simultaneous calls on multiple threads.  Two different threads running count() will be no more efficient than a single thread running count.  Running count on a background thread will be less efficient than running count on a main thread (due to the cross-apartment marshalling). 

 

Sincerely,

 

Geoff Darst

Microsoft VSTO Team

 

 

Answer 6

Hi Geoff,
I rechecked what code was being run (to make sure I wasn't calling any code from the larger application) and found that I was still registering an IMessageFilter in the main UI thread. When I stopped doing that, the example worked. I'll need to figure out what I was doing wrong there.

The reason I thought I needed to use a try catch block anyway was from this one of your posts (where you discuss VBA_E_IGNORE). Does that post mean that I always need to look for the VBA_E_IGNORE exception when I access the Excel object model from multiple threads? If not, when do I need to watch out for it?

The reason I am using multiple threads is because I am continuously updating the spreadsheet with realtime data in the background as long as it remains open. Is doing this from a background thread the preferred approach?

Sorry for some of the careless mistakes and thanks for your help. I'm still fairly new to VSTO.

Thanks again,

Mike

Answer 7

Hi Mike,

 

Glad you figured out the cause of the problem.  IMessageFilter is a confusing interface because it shares both client and server methods.  Servers need to implement HandleIncomingCall and MessagePending, while clients need to implement RetryRejectedCall.  You would need to register your IMessageFilter implementation on each background thread that intended to make calls into Excel's STA.  If you registered a message filter on Excel's UI thread, you would have replaced Excel's IMessageFilter implementation with your own.  My guess is your implementation was triggering an exception in the server methods--which could certainly cause very bad things to happen.  Anyway, you do not need to register a MessageFilter on Excel's UI thread (it has its own implementation that it relies on).

 

Regarding my VBA_E_IGNORE post, you would only really need to worry about this issue if your users could be interacting with Excel while your automation is running.  You get VBA_E_IGNORE when the user does something to cause the property browser to be suspended; i.e. editing a cell.  If your customization executes modally, then you will not have to worry about this issue.  Otherwise, you will need to give careful consideration to what your users are allowed to do while code is executing (and what your code does) and try to forsee exceptions that could be generated and handle them appropriately.  IMO, allowing simultaneous user and automation interaction is a somewhat dangerous approach that should be avoided if at all possible.

 

Regarding using a background thread to receive real-time data, the key is to understand to what extent (if any) you derive a concurrency benefit.  Any work that thread does *except* the call to Excel will be executed concurrently.  So if the thread does a bunch of work besides the OM call, it is probably useful.  Additionally, you might consider using a background thread for throttling reasons.  If data is coming in at a high rate of speed, you might want to designate a seperate thread to accept the data and queue it up.  Excel is fairly slow when it comes to pushing data into cells via automation, so it is possible that it may not be able to keep up.

 

Since your cross-apartment calls are actually going to be slower, the real question is whether you should even be making OM calls on the background thread at all.  I would think a good approach would be to have a background thread receive data and queue it up.  Code on Excel's UI thread would then be responsible for pulling data out of the queue and propogating it to the worksheet.  Because the OM calls occur on the main UI thread, there is no cross-apartment marshalling to deal with--which leaves you with a more performant and technically simpler solution.

 

Because Com will serialize all cross-apartment calls and because they will always be executed on the thread associated with the STA (in this case Excel's main UI thread), you actually get more control over execution if you avoid the marshalling scenario.  IOW, if you make the call cross-apartment, it will not get handled until Excel actually pumps the RPC message (if Excel doesn't choose to reject the call outright).  OTOH if you make the call directly from the UI thread, you have complete control over execution. 

 

Hope that helps clear things up.

 

Sincerely,

 

Geoff Darst

Microsoft VSTO Team

Answer 8

Hi Geoff,
Thanks for the detailed response. The real-time updates are continuous and not modal, so the VBA_E_IGNORE problem seems very serious. BTW, the GUI thread does a lot of cell "editing." Can that trigger VBA_E_IGNORE messages or do they only occur when the user is editing? Saying that VSTO can only reasonably work multithreaded if users aren't editing cells seems more like a bug than an ordinary restriction. Is there any potential for a patch to make IMessageFilter work properly?

I don't mind doing this in the UI thread, but I often need to spend all the background cycles on the updates without hurting responsiveness. How can I do this in the UI thread?

Thanks again,

Mike

Answer 9

Hi Mike,

 

It should be a lot harder to get a VBA_E_IGNORE error if code is executing on the UI thread.  In order for that to happen, you would need re-entrancy that would allow your code to execute in the middle of a user edit or something similar.  Unless you are pumping messages yourself to facilitate some sort of asynchronous processing, I would not worry about VBA_E_IGNORE.

 

There isn't anything stopping you from allowing your users to edit the document while you update it simultaneously on the background thread--its just that if you do this, you dramatically increase the likelihood that your calls are going to get rejected--which means you have to be able to deal with that.  The VBA_E_IGNORE concept is fine; its just that Excel's implementation of IMessageFilter::HandleIncomingCall fails to check for this condition on cross-apartment calls.  Instead of just getting your call rejected, you get an actual failure that you have to handle out of band.  So you have some extra work to do.  I didn't mean to suggest you could not take this approach, but you do want to be careful.  Going down this road adds complexity--so just be sure this is your best option.

 

Assuming you need your users to interact with the document, the simplest approach would be to do everything from a background thread.  If throttling is an issue, you could use two threads--one to receive the data and another to actually push it into Excel.  This approach is easy to implement, but it will be slightly less performant than a solution where you made the OM calls directly on the UI thread.

 

The problem with the latter approach is that you need user interaction--which means you need to pump messages.  You could do this yourself, but Excel's message loop is more complicated than a typical windows application because Excel is a component manager.  So if you tried to pump messages yourself, you would likely be breaking the Vba IDE and possibly the property browser.  You might also affect the way accelerators are handled--particularly wrt ActiveXControls.  There are probably other things I'm not thinking about--which is a concern.

 

If your user interactions will be limited then this might be acceptable--in which case you could just set up a queue like I suggested.  The background thread receives the data and pushes it into the queue, while the UI thread just sits in a message loop that retrieves data from the queue and pumps messages on each pass.

 

The only other approach would be to implement IOleComponent and register yourself with the Office component manager.  (You call CoRegisterMessageFilter to get Excel's message filter which you can then QI for IOleComponentManager).  You could get idle time that way, but then you would be effectively no different than if you were marshalling cross-apartment.  In either case, you wouldn't get processed until Excel pumped messages.  Your calls will be *slightly* faster, but probably not enough to make it worth while from a performance perspective.  However, this would circumvent the possibility of your calls being rejected.  The other trade-off is in terms of implementation.  Component manager stuff isn't well documented and you might struggle a bit to get this working.

 

So in the end, you'll need to decide which trade-offs you are willing to make in terms of performance, complexity and risk. 

 

Sincerely,

 

Geoff Darst

Microsoft VSTO Team

 

 

 

Answer 10

Thanks for all your input, Geoff. Since the background thread only interacts with the Excel Object Model in a relatively small number of places (about a dozen), I think I'll just put all of those in try blocks like I did with the count() method.  I also assume there's no reason for me to horse around with IMessageFilter in that case. Basically, I just need about a dozen try blocks. Sound reasonable?

Also, do I need to catch any exceptions other than VBA_E_IGNORE and RPC_E_CALL_REJECTED?

Thanks again,

Mike

Answer 11

Hi Mike,

 

Yes that approach should work.  VBA_E_IGNORE and RPC_E_CALL_REJECTED are the key HRESULTS you need to watch out for.  I'm not aware of any others that would be related to cross-process calls.

 

Sincerely,

 

Geoff Darst

Microsoft VSTO Team

Answer 12

Geoff,

Thanks a lot for all your time walking me through this. I'm good to go now.

Best,

Mike

Answer 13

Geoff Darst - MSFT wrote:

There isn't anything stopping you from allowing your users to edit the document while you update it simultaneously on the background thread--its just that if you do this, you dramatically increase the likelihood that your calls are going to get rejected--which means you have to be able to deal with that.  The VBA_E_IGNORE concept is fine; its just that Excel's implementation of IMessageFilter::HandleIncomingCall fails to check for this condition on cross-apartment calls.  Instead of just getting your call rejected, you get an actual failure that you have to handle out of band.  So you have some extra work to do.  I didn't mean to suggest you could not take this approach, but you do want to be careful.  Going down this road adds complexity--so just be sure this is your best option.

That's what I was asking about as a potential patch. I was suggesting MSFT patch Excel's IMessageFilter::HandleIncomingCall to check for VBA_E_IGNORE?

As I think about it, could I fix this just by replacing the UI threads IMessageFilter with one that checks for VBA_E_IGNORE and then chains to the old one? That way I could get rid of the need for try blocks entirely, which would be awesome.

Best,

Mike

Answer 14

Hi Mike,

 

That's a good idea and it might be possible to make that work.  The trick will be figuring out whether you are in the VBA_E_IGNORE case from within HandleIncomingCall.  In order to do that, you would need to to be able to call some method that would return VBA_E_IGNORE.  It may be that Excel dispatches all automation calls through the VBA wrapper function that triggers this error.  You could test that by trying to invoke a call while you are editing a cell in Excel.  For sure, setting the value of a cell in this case will trigger the error.  So you could do something like get the value of a cell and then try to set it back to its original value.  If that fails with VBA_E_IGNORE, then you would reject the call.  Ideally, you could use an even lighter-weight call--something like checking an application property.  Anyway, you'd just need to test it to see if it would correctly return the error.

 

Sincerely,

 

Geoff Darst

Microsoft VSTO Team

Answer 15

Hi Geoff,
That's very interesting. Isn't there still a race condition because even if the test call succeeds, the object browser could be suspended after my test invocation is called? To prevent this, I would need to acquire some critical section that protects suspending the object browser and somehow release that after the call is done. BTW, how is this race condition avoided for the RetryRejectedCall case? Does COM look at the the actual returned HRESULT?.

As one final point, I'd like to evangelize how serious an issue this is in general (not just for me). In my simple count example, even just clicking on a cell and holding the mouse button down produces VBA_E_IGNORE messages left and right. I suspect this means that pretty much any Excel VSTO program that uses VSTO functionality in worker threads will run into this problem (Only exception I can think of is if the spreadsheet has been made read-only). I think figuring out (or creating) a solution to this question would be a significant improvement to the utility of VSTO.

Thanks again for your help,

Mike

Answer 16

In its simplest form, deadlock occurs when each of two (minimun two) threads try to acquire a lock on a resource already locked by another. E.g: Take example of two threads 1. thread 1 2. Thread 2 And two resources 1. Resource 1 2. Resource 2 Thread 1 locked on Resources 1 and tries to acquire a lock on Reosurce 2. At the same time, Thread 2 has a lock on Resource 2 and it tries to acquire lock on Resource 1. Two threads never give up their locks, hence a DEADLOCK occures. Solution : The simplest way to avoid deadlock is to use a timeout value . You can use the Monitor class (system.Threading.Monitor) to set a timeout during acquiring a lock. Ex. in C# if(Monitor.TryEnter(this, 500)) { // critical section } catch (Exceprion ex) { } finally { Monitor.Exit(); } Here the timeout is 500 milliseconds . If the lock can't be acquired, after 500 miliseconds, timeout occurs and the code exit the Monitor block.

---

Cheers, Eliza