Mutex - record locking mechanism

mvoronkin
mvoronkin used Ask the Experts™
on
I have vb6 app that communicates with MSAccess2003 database. The problem I'm having is the record locking. I use parameterized store procedures to insert records to a database. I have 3 users constantly entering data. Every day they ran into the locking problem.
I was advised to use mutex to manage exclusive access to the database while inserting records.
My question is what is the proper name for a mutex object located on the server? I need all my clients to be able to access it.
Will the following code work:
Public Function SaveData(ByRef MyData As MyType) As Boolean  
    Dim hMutex As Long
    Dim lngWaitResult As Double
    Dim blnReturn As Boolean
    MUTEX_NAME = "\\SERVER_NAME\Mutex_Name"
    hMutex = CreateMutex(0, True, MUTEX_NAME)
    If Err.LastDllError = ERROR_ALREADY_EXISTS Then        
        lngWaitResult = WaitForSingleObject(hMutex, INFINITE)
    End If    
    Select Case lngWaitResult
        Case WAIT_OBJECT_0
          '  Write to the database.
            blnReturn = WriteToDB(MyData)
            Dim lngReleasmutex As Long
            lngReleasmutex = ReleaseMutex(hMutex)
            If lngReleasmutex = 0 Then
                 '   Deal with error.
            Else
                CloseHandle hMutex                
            End If          
       Case WAIT_TIMEOUT
            blnReturn = False
       Case WAIT_ABANDONED
            blnReturn = False
        Case WAIT_FAILED
            blnReturn = False
    End Select
    SaveData = blnReturn    
   Exit Function

Another question is what is the proper way to handle the ReleaseMutex error?

Thanks in advance

Milena
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The Mutex name should be something that is (a) easy to understand who/which app creates it, and (b) difficult for two different apps to use the same name by coincidence. For example, \\SERVER\MYAPPNAME_DBMutex. If MYAPPNAME is not distinctive enough, you need to figure out what combination of fields would be distinctive on that server environment.

Moving on to the code
> hMutex = CreateMutex(0, True, MUTEX_NAME)
>    If Err.LastDllError = ERROR_ALREADY_EXISTS Then        
>        lngWaitResult = WaitForSingleObject(hMutex, INFINITE)
>    End If    

The first time you execute this, you would expect it to return success. The above code will not do a WaitForSingleObject in that case.

Second, I suggest putting the mutex management into separate functions and not embed them in the main body of the code.

Author

Commented:
Hi,
Thanks for a reply.
My problem is that I run this code on the client machine and I can't create a mutex on the server using following name for a mutex \\SERVERNAME\MUTEXNAME. The return value is 0.
 Is it possible to create a mutex on server from code running on the client machine?


Thanks again

I don't think there is an out-of-the-box way to grab a mutex on a remote machine - at least I am not aware of any. Looks like you really need a client-server architecture - here are some ways:

1. Upgrade to MS SQL Server from Access. SQL Server handles concurrency between multiple users/connections. This may be your simplest option.

The following are all different ways to put a mutex lock around the database call. Bear in mind that all of these will be less scalable design than the first one, if you envisage your usage growing.

2. Roll your own mutex-management server. This can be a standard web server with mutex management code (which you write) plugged in through ASP/JSP/etc. You will then need to change your VB client to call this lock server before every DB call (and after, to release the lock).

3. Look for an Access ODBC driver that manages concurrency.

4. Look for commercial ODBC Gateway products. These sit in the middle of ODBC communications, either on the client side or the server side, and give you a programmatic hook to insert your own code.

I don't have much experience with 3 & 4, so cannot give you more specifics. EasyDirect seems to be a very popular name in the ODBC Gateway space, however.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial