Win 2k8 Server 64bit - Named (System Wide) Semaphores - SQL Server 2k8

  • Hello,

    We have, for years, been running software in which a standalone Windows executable ('management program') creates a system semaphore and waits. In SQL Server, we use xp_cmdshell to execute another Windows executable ('work program') that will open this semaphore, do some work, and release it. However, once we upgraded to Windows 2008 Server 64bit, this functionality was broken. When a named system semaphore is created OUTSIDE of SQL Server's xp_cmdshell by the 'MANAGEMENT' program, we cannot access this semaphore when we invoke an instance of our 'WORK' program via xp_cmdshell. The documentation at http://msdn.microsoft.com/en-us/library/z6zx288a.aspx clearly states that "once the named semaphore is created, it is visible to all threads in all processes" and it has worked perfectly up until our Windows 2008 Server 64bit upgrade from 2003 Server 32bit.

    A named system semaphore, which is SUPPOSED to be available to the entire system, is not. I cannot access these semaphores from xp_cmdshell from SQL Server 2008 unless they are created in SQL Server 2008. The purpose of them is to be accessible from ANYWHERE in the system, by name, as clearly stated at http://msdn.microsoft.com/en-us/library/z6zx288a.aspx.

    Does anyone know any reasons/fixes/workarounds/etc for this problem?

  • ray.jacksonsr (12/22/2009)


    Hello,

    We have, for years, been running software in which a standalone Windows executable ('management program') creates a system semaphore and waits. In SQL Server, we use xp_cmdshell to execute another Windows executable ('work program') that will open this semaphore, do some work, and release it. However, once we upgraded to Windows 2008 Server 64bit, this functionality was broken. When a named system semaphore is created OUTSIDE of SQL Server's xp_cmdshell by the 'MANAGEMENT' program, we cannot access this semaphore when we invoke an instance of our 'WORK' program via xp_cmdshell. The documentation at http://msdn.microsoft.com/en-us/library/z6zx288a.aspx clearly states that "once the named semaphore is created, it is visible to all threads in all processes" and it has worked perfectly up until our Windows 2008 Server 64bit upgrade from 2003 Server 32bit.

    A named system semaphore, which is SUPPOSED to be available to the entire system, is not. I cannot access these semaphores from xp_cmdshell from SQL Server 2008 unless they are created in SQL Server 2008. The purpose of them is to be accessible from ANYWHERE in the system, by name, as clearly stated at http://msdn.microsoft.com/en-us/library/z6zx288a.aspx.

    Does anyone know any reasons/fixes/workarounds/etc for this problem?

    I don't understand what you mean so please explain because it is either you don't know what are semaphores, your question is not clear. Semaphores are like thread Mutexes and Monitor so I cannot understand why you expect to use x86 Semaphores in x64 box without issues. That take me to if your application is managed code you could try AnyCPU build if this is native code then I don't think it can be fixed. So try connecting your application to SQL Server in the x86 directory. Semaphores are new to .NET 2.0.

    Kind regards,
    Gift Peddie

  • Thanks Gift Peddie,

    I have a VB.net threaded application that I use. However, to verify that this is where the problem lies, I made a simplified test program sem_test.exe which can be run in multiple instances.

    Usage: sem_test Semaphore_Name (waits | signals ) --

    Semaphore_Name is the name of the semaphore

    'waits' is the code where the semaphore named Semaphore_Name is created and it waits for single object

    'signals' is the code where we open the semaphore named Semaphore_Name, do work, release it, and close the handle

    To test:

    1. I open 2 cmd windows so that i can run separate instances.

    2. cmd1: sem_test semaphore1 waits

    cmd1 output is:

    semaphore1: Creating Semaphore semaphore1

    Waiting on semaphore semaphore1

    3. cmd2: sem_test semaphore1 signals

    cmd2 ouput is:

    Opening Semaphore semaphore1

    Releasing Semaphore semaphore1

    Successfully Releasing Semaphore semaphore1 Prev Count = 0

    Closing Semaphore semaphore1

    4. Now when I do step 3, the output in cmd1 adds:

    Captured semaphore semaphore1

    So it works if we use native windows cmd prompts OUTSIDE of SQL Server. I can also do the same test INSIDE of SQL Server Management Studio, using 2 query tabs (instead of windows cmd windows), through xp_cmdshell. I can see the same output: queryTab1 output = cmd1 output and queryTab2 output = cmd2 output. So it that works too.

    Now I test between the cmd1 window and SQL Server xp_cmdshell

    1. cmd1: sem_test semaphore1 waits

    cmd1 output is:

    semaphore1: Creating Semaphore semaphore1

    Waiting on semaphore semaphore1

    2. xp_cmdshell: exec xp_cmdshell '{path}\sem_test semaphore1 signals'

    SQL output is:

    Exiting. Could not open semaphore. Semaphore semaphore1 not defined.

    3. cmd1 output has not changed and is still waiting for the signal for semaphore1

    So that shows it does not work and that the Named Semaphore is NOT system wide like it should be, as stated on Microsoft's website http://msdn.microsoft.com/en-us/library/z6zx288a.aspx. I've been using this architecture on Win Server 2k3 32bit and every windows os I've had before that with no problems. I'm not sure whether it's a Win Server 2k8 thing or a 64bit thing. However, it does not seem to be a 64bit thing because it works perfectly when both instances are run exclusively INSIDE and exclusively OUTSIDE of SQL Server on the 64bit setup.

    I appreciate any ideas. Thank you.

  • I've been using this architecture on Win Server 2k3 32bit and every windows os I've had before that with no problems. I'm not sure whether it's a Win Server 2k8 thing or a 64bit thing. However, it does not seem to be a 64bit thing because it works perfectly when both instances are run exclusively INSIDE and exclusively OUTSIDE of SQL Server on the 64bit setup.

    The main difference between Mutexes and Semaphores is that Mutexes are controlled by the operating system so I think this is platform issue which I think it can be fixed if you open your VB.NET code and recompile it for x64. The reason is you said if run separately it works so it is related to the use of two x64 system that depends on named thread synchronization.

    Kind regards,
    Gift Peddie

  • I am not sure I understand exactly about two x64 systems because it's only 1 64bit system, win2k8 server, but you DO seem to know! So I will definitely try that solution and rebuild for x64 and see how that works out.

    Thanks again for your help!

    Ray

  • So it works if we use native windows cmd prompts OUTSIDE of SQL Server. I can also do the same test INSIDE of SQL Server Management Studio, using 2 query tabs (instead of windows cmd windows), through xp_cmdshell. I can see the same output: queryTab1 output = cmd1 output and queryTab2 output = cmd2 output. So it that works too.

    By two x64 system I am talking about SQL Server x64 and Win2008 x64 your code use both but technically you have two x64 systems. And I think I know what is your problem and that is the very nature of Semaphore because other process can use Semaphores defined by your system. I think the easy way to fix it may be recompile but I could be wrong. Look at your code again you will see could not open Semaphore which means it may be used. I have also checked I could not find anything related to Win2008. Please post again if you find a fix.

    Kind regards,
    Gift Peddie

  • ray.jacksonsr (12/22/2009)


    I am not sure I understand exactly about two x64 systems because it's only 1 64bit system, win2k8 server, but you DO seem to know! So I will definitely try that solution and rebuild for x64 and see how that works out.

    Thanks again for your help!

    Ray

    I found some code from Microsoft that may help find what is blocking your Semaphore, at the end of the page in the link below there is a 108 pages Word file download it at the end is a long code that will find the blocking. I may help and may is the operative word.

    http://technet.microsoft.com/en-us/library/cc966540.aspx

    Kind regards,
    Gift Peddie

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply