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.
(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
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
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
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.
answered 08 Jul '10, 17:33
Ivan T. Bowman