Another question during my migration tests from ASA 8.0.3 to SA 11.0.1/12: We have to serialize different "server" processes on a Windows-based database server. Not all of those processes use databases, so a "DB-wise serialization" (table-based or the like) is not possible. Therefore we use Win32 mutexes to serialize. (Sidenote: I accept all those "But that doesn't scale at all!" remarks. You are right, of course, but performance is not the main goal here.) Inside the database, mutexes can be locked and unlocked by calls to external C functions, namely fnAsaLockMutex() and fnAsaUnlockMutex(). This mutex solution has worked successfully for years on 8.0.3. We have several scheduled events for administrative tasks, and they also use this kind of serialization. The event procedures basically look as following:
In order to work, the calls to fnAsaLockMutex() and fnAsaUnlockMutex() must be executed on the same OS thread. Otherwise, a mutex would remain blocked, and other processes (and other work on the database server, too) would get blocked. As said, with ASA 8.0.3 this assumption holds, i.e. both external calls are always run on the same OS thread. But newer versions do much more in parallel: It seems that external calls are usually run on separate threads, and possibly tasks like doing backups or validating databases are parallelized, too. Therefore, it seems that the above assumption doesn't hold anymore. Questions:
(Note: I'm not at all dealing with the serialization of DML statements, isolation level and the like...) [Source: A similar question was raised in the general NG with topic "Intra-query parallelism inside event procedures" on 2007-09-11. I hope there are some new answers...] asked 07 Jul '10, 13:50 Volker Barth |
Volker, Don't bother with the dedicated_task option because that's not going to help you. In Version 10, we changed the underlying architecture for handling in-process C/C++ function calls so that those calls were executed on a thread different from the one handling the current request from the connection. That thread is from a separate thread pool that is allocated dynamically and is independent from workers (worker threads) used for stored procedure execution and query execution plans. While this architecture doesn't prevent the server from failing if the DLL call gets an exception, it insulates the worker thread from blocking and infinite loops from within the DLL so as not to tie up a worker. There is no way to control this other thread pool, and there is no guarantee that you'll use the same thread with successive calls. In short, your use of Win32 mutex calls from an in-process C function relied on the SQL Anywhere 8 threading architecture, and that approach won't work with version 11. The external environments supported with Version 11 take this isolation one step further, and isolate the DLL call by having it run as a separate process with (at least) two threads, which effectively sandboxes external functions to prevent catastrophic server failures. As Karim has indicated, you could use an external environment to execute the DLL function, which at the moment will guarantee that the same thread will be used on each call since the external environment is twinned with that connection. However, we will not guarantee that we won't change how CLR functions are executed in a future SQL Anywhere release, with obvious implications for your implementation. Karim's option (2) may be a better bet; by controlling your own threading you can make all of the necessary guarantees. answered 07 Jul '10, 17:32 Glenn Paulley Thanks for this clarification - I was not aware of the particular thread pool for external C calls but that explains the behaviour I see. At the moment, I guess I'll go with the external C environment (with a workaround for the issue described in my other question http://sqlanywhere-forum.sap.com/questions/798/whats-the-difference-between-calling-a-simple-c-function-in-the-servers-context). - Your statement makes clear that external C environments work as expected for my need. I was not sure about that because I had noticed that the dbexternc11.exe uses several threads, too. Just to complete the answers to the "list of question" in the question: Are there any means (properties or the like) to find out which request runs on which task/thread? - Obviously, using external calls to find out won't work at all when they generally run on different threads. I'm not aware of any mechanism to return the "name" of worker thread, or a thread from the external environment pool, that can be queried from within an SQL application. |
Volker, The change to use separate non-worker threads to make in process external C calls (as well as remote data access calls, external environment calls, http calls, etc.) was done to ensure that the server does not get completely hung up due to a misbehaving C function (or remote server etc.). As a result, you are correct that you are no longer guaranteed that a call to your in process C function will come in on the same thread each time. I know that Glenn is planning to provide a different answer for your immediate problem; but, given that performance is not necessarily an issue here, I have two suggestions. 1) Move your code to an external C environment. Even though the server can use a different thread to communicate with the external C environment, the calls to your C functions within the external C environments will always come in on the same thread. 2) If you want to stay in process (or if for some reason the external C environment approach does not work), then you may need to change your C code to spawn its own threads. You can then switch to the appropriate thread when the server calls your C routine to make the Mutex call. answered 07 Jul '10, 17:03 Karim Khamis I guess the external C environment approach should work in my case. It will isolate each connection (in contrast to the ASA 8 situation, where different connections could use the same thread as long as they don't have simultaneous requests), but that isolation is okay, too. Just to confirm: Fortunately, the external C environment approach is working in my particular case. Whereas I had only to add the "language C_ESQL32" (or C_ODBC32) clause to the external function defintions, the somewhat harder part was to adapt the tracing code inside the DLL (in debug mode) to handle concurrent access from different processes (instead of different threads) - the debug code writes output to a debug file, and that had to be serialized, too... Comment Text Removed
So let me say thanks for introducing the external C environments - one of the not so rare moments where I get the impression "How could they know I would need this feature?" - But that is another question:) |
I believe that Win32 semaphores don't require ReleaseSemaphore to be called from the same thread that waited for the semaphore. A semaphore with a maximum count of 1 might offer another solution requiring fewer changes. http://msdn.microsoft.com/en-us/library/ms686946(v=VS.85).aspx answered 08 Jul '10, 17:33 Ivan T. Bowman Thanks, that's an interesting hint - I'm gonna look at that (though it might mean changes to several applications...). |