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:

create event ...
handler
begin
    ...
     -- wait (at maximum several minutes) to get the mutex
    set bIsLocked = dbo.fnAsaLockMutex(strMutexName...);
    if bIsLocked = 1 then
        DoSomeMinorCalculations;    -- some small select stmts
        DoSomeAdministrativeStuff;    -- e.g. statements like "BACKUP DATABASE"
        DoMoreAdministrativeStuff;    -- e.g. copy backups to a net share via xp_cmdshell()
        DoSomeCleanup;

-- finally, if all is OK, release the mutex
         set bIsLocked = dbo.fnAsaUnlockMutex(strMutexName);
    end if;

DoTheErrorHandling;
end;

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:

  • Can I check what database task (thread/fiber) is running what request?
  • How can I check if a stored procedure (or event handler) is split into several tasks? (A "query plan" is not available for a stored proc or event AFAIK).
  • Are there any options to prevent parallelizing? (I tested both max_query_tasks = 1 and priority = 'low' but they had no effect on threading here.)

(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%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%


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.

permanent link

answered 07 Jul '10, 17:32

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

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.

(07 Jul '10, 21:08) Volker Barth

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.

(07 Jul '10, 21:16) Volker Barth

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.

(08 Jul '10, 02:32) Glenn Paulley

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.

permanent link

answered 07 Jul '10, 17:03

Karim%20Khamis's gravatar image

Karim Khamis
5.7k53870
accept rate: 40%

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.

(07 Jul '10, 21:12) Volker Barth

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...

(14 Jul '10, 14:04) Volker Barth
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:)

(14 Jul '10, 14:07) Volker Barth

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

permanent link

answered 08 Jul '10, 17:33

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

Thanks, that's an interesting hint - I'm gonna look at that (though it might mean changes to several applications...).

(09 Jul '10, 12:17) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×143
×23
×10
×1

question asked: 07 Jul '10, 13:50

question was seen: 4,744 times

last updated: 08 Jul '10, 17:33