INF: Use of Thread Local Storage in an Ext. Stored Procedure

• Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

Thread Local Storage (TLS) can be a very tricky subject. When working in a thread pooling environment, it is very important that you understand the ramifications of TLS and the thread interactions.

It is highly suggested you avoid TLS in a thread pooling environment such as Microsoft SQL Server. However, if you must use TLS, please carefully read this document and consult the online documentation in the Win32 SDK for the TLS and DLL main functions.

Also, the compiler directive __declspec(thread) is not supported in an extended stored procedure. When LoadLibarary takes place, the __declspec(thread) definition is not properly initialized. See "Advanced Widows" by Jeffrey Ricter for more details.

NOTE: Any extension to SQL Server, including extended stored procedures, may never make a call to DisableThreadLibraryCalls().

MORE INFORMATION

As documented, the TlsAlloc function returns an index value that is used in function calls to TlsSetValue and TlsGetValue. The documentation suggests that you call the TlsAlloc function in your DLLMain function when ul_reason_for_call = DLL_PROCESS_ATTACH. By default, DLL_PROCESS_ATTACH is called when the DLL is initially loaded.

In DLL_PROCESS_ATTACH and DLL_THREAD_ATTACH, you should allocate memory and call TlsSetValue. This is specifically where the thread pooling starts to cause some problems in the scenario.

Below is an example scenario that applies to any application that does thread pooling. This example uses the Microsoft SQL Server application.

The following are a few basics for the example that you must understand. These are all documented in more detail in the Win32 SDK documentation under the DLLMain function.

• When a DLL is loaded, it calls DLL_PROCESS_ATTACH.
• All subsequent threads (spawned after the DLL is loaded) call DLL_THREAD_ATTACH.
• Currently running threads (spawned before the DLL is loaded) do not call DLL_THREAD_ATTACH.
• All threads call DLL_THREAD_DETACH, even if they never called DLL_THREAD_ATTACH.

   Thread     ATTACH CALLED     COMMAND     USER

     1              NO           select      Joe
     2             YES          xp_test     Mary
     3             YES           select     Adam

     1              NO          xp_test     Lynn

While Joe's select is running, Mary runs xp_test. The thread pooling mechanism determines that a new thread should be spawned to service Mary's request. SQL Server then calls the LoadLibrary function to load the Xproc.dll file. In doing so, thread 2 is the first thread to attach to the DLL, so DLL_PROCESS_ATTACH is called. As discussed earlier, TlsAlloc can be called to initialize the extended stored procedure's TLS index value.

While Joe's and Mary's commands are running, Adam submits his own select. Again, a new thread is spawned to handle Adam's request. Because thread 3 is spawned after the LoadLibrary took place, thread 3 calls DLL_THREAD_ATTACH. As documented, this is where you would allocate the memory for thread 3 and call TlsSetValue.

Now, suppose Joe's select has completed, so worker thread 1 is free for use. Lynn submits the xp_test command, and is assigned thread 1. Thread 1 never calls DLL_PROCESS_ATTACH or DLL_THREAD_ATTACH, because the thread was spawned before the LoadLibrary was called.

From this example, you can see that any attempt by thread 1 to access the TLS memory with TlsGetValue results in a NULL pointer being returned. If the extended stored procedure is not properly written to check for this condition, you will encounter an access violation (AV) when you try to write to the NULL address.

Below are several points that need to be mentioned about TLS and thread pooling.

If you are using TLS in a pooling environment, you must always check the TlsGetValue for a NULL return value. When you get a NULL, you must correctly allocate memory and call TlsSetValue to handle those threads that where spawned before the LoadLibrary took place.

Another caveat not directly addressed in the documentation is that you will allocate the TLS memory in the DLLMain function for every thread spawned after the LoadLibarary, even if the thread never uses the functions in the DLL. The example above shows this with thread 3. It only ran a select, but it called DLL_THREAD_ATTACH, which allocated the TLS memory for the extended stored procedure.

If you place this design in a thread pooling environment, you may allocate memory that may never be used.

The following is the best way to optimize the allocation of memory:

1. In DLL_PROCESS_ATTACH, obtain the correct TLS index value from TlsAlloc.

2. Do not allocate memory in DLL_PROCESS_ATTACH or DLL_THREAD_ATTACH.

3. Design a generic function to get the TLS value; if it is NULL, correctly allocate it and call TlsSetValue.

   This step restricts the allocation of memory to those threads that actually use it. This may reduce the overhead and startup time of worker threads significantly. It also builds in the redundancy to correctly handle those threads that were spawned before the LoadLibrary took place.

4. Because DLL_PROCESS_DETACH and DLL_THREAD_DETACH are always called, free memory in those processes.

The following is an extended stored procedure that shows the behavior described in the example:

   //
   //    1. Start SQL Server from the command prompt to see the output:
   //       ...\Mssql\Binn\Sqlservr -c
   //
   //    2. Run Xproctst.cmd to show the behavior.
   //
   #include "windows.h"
   #include "stdio.h"
   #include "srv.h"

   #define           TLS_FAIL    0xFFFFFFFF

   DWORD          dwTlsIndex     =  TLS_FAIL;
   DWORD          dwCounter      =  0;
   CRITICAL_SECTION  csSync;

   //
   //    Cleanup TLS memory
   //
   void vCleanUpTls(void)
   {
      char *   strData     =  NULL;

      if(TLS_FAIL != dwTlsIndex)
      {
         strData = TlsGetValue(dwTlsIndex);
         if(NULL != strData)
         {
            free(strData);
            printf("\n >>> Tls memory released by thread %ld",
   GetCurrentThreadId());
         }
      }

   }

   //
   //    Setup the TLS pointer
   //
   void vSetUpTls(void)
   {
      char *      strData  =  NULL;

      if(TLS_FAIL == dwTlsIndex)
      {
         dwTlsIndex = TlsAlloc();
      }

      //
      //    Are we ready to go
      //
      if(TLS_FAIL != dwTlsIndex)
      {
         strData = (char *) calloc(256,1);

         if(strData)
         {
            printf("\n >>> Tls memory allocated by thread %ld",
   GetCurrentThreadId());

            if(TRUE == TlsSetValue(dwTlsIndex, strData))
            {
               //
               //    Protect the counter.
               //
               EnterCriticalSection(&csSync);
               sprintf(strData, "Counter = %ld", ++dwCounter);
               LeaveCriticalSection(&csSync);

            }
            else
            {
               printf("\n >>> *** Serious error *** TlsSetValue
               failed.\n");
            }

         }
         else
         {
            printf("\n >>> *** Serious error *** can not allocate
            memory.\n");
         }

      }
      else
      {
         printf("\n >>> *** Serious error *** TlsAlloc failed.\n");
      }

   }

   //
   //    DLLMain
   //
   BOOL APIENTRY DllMain(HANDLE hInst, ULONG ul_reason_for_call, LPVOID
   lpReserved)
   {
      char  strInfo[256]   =  "";

      switch(ul_reason_for_call)
      {

         case DLL_PROCESS_ATTACH:
            InitializeCriticalSection(&csSync);

            vSetUpTls();
            sprintf(strInfo, "\n >>> DLL_PROCESS_ATTACH Thread: %ld",
   GetCurrentThreadId());
            break;

         case DLL_THREAD_ATTACH:
            vSetUpTls();
            sprintf(strInfo, "\n >>> DLL_THREAD_ATTACH Thread: %ld",
   GetCurrentThreadId());
            break;

         case DLL_PROCESS_DETACH:

            vCleanUpTls();

            if(TLS_FAIL != dwTlsIndex)
               TlsFree(dwTlsIndex);

            DeleteCriticalSection(&csSync);

            sprintf(strInfo, "\n >>> DLL_PROCESS_DETACH Thread: %ld",
   GetCurrentThreadId());
            break;

         case DLL_THREAD_DETACH:
            vCleanUpTls();
            sprintf(strInfo, "\n >>> DLL_THREAD_DETACH Thread: %ld",
   GetCurrentThreadId());
            break;
      }

      printf(strInfo);

      return TRUE;
   }

   //
   //    DLL function in the extended stored procedure to show TLS trap
   //
   __declspec(dllexport) SRVRETCODE xp_Tls(SRV_PROC *pSrvProc)
   {
      char  strInfo[256]   =  "";
      char *   strData        =  NULL;

      sprintf(strInfo, "\n >>> Invoking xp_Tls on Thread: %ld",
   GetCurrentThreadId());
      printf(strInfo);

      if(TLS_FAIL != dwTlsIndex)
      {
         strData = TlsGetValue(dwTlsIndex);
         if(NULL != strData)
         {
            sprintf(strInfo, "\n >>> %s", strData);
            printf(strInfo);
         }
         else
         {
            printf("\n >>> *** Serious error *** TlsGetValue returned NULL,
   thread pooling not handled correctly.\n");
         }

      }

      return 1;
   }