concurrent sqlite database access... how to handle?

[Eric Brown]

I have a function that you can pass the targeted sqlite database and query information, and it executes that query.  This function will be used concurrently in goroutines.  The sqlite package I'm using is mattn's (https://github.com/mattn/go-sqlite3).  It specifically says that it does not support concurrent access.  Using a simple mutex.lock would be easy, if I only used one database (file); however, I need the function to take in a string variable that is the file path of the database to open.  I don't want that function to lock itself in general, if two separate database files are being used (because then concurrency wouldn't matter, as it's two completely different database files).

What I'm getting at, is how would go about doing something like this?  Make the lock FILE/DATABASE dependent in the same function, and only lock that specific resource (not the function... so that another goroutine can call and use it for another database if that specific database that was passed isn't already used)?  I can't use maps, because those are not concurrent safe either.  I hope I've explained myself.  Any assistance would be highly appreciated...

[Val]

Hello Eric
You may use a map[string]*sync.Mutex where key is the DB filepath, and protect that map with a global mutex.

Note that a mutex value must not be copied, often it is necessary to use pointers.

[Eric Brown]

Thanks Val, I actually tried that approach... and failed.  Perhaps I incorporated it wrong.  I put the handling of locking/unlocking of the map[string]*sync.Mutex in it's own function, which was encapsulated in the global mutex like you suggested.  Only problem was, was that the database function ran once... because when it went to call the function that handled the unlocking of the map[string]*sync.Mutex, it was already locked by a previous goroutine trying to lock the map[string]*sync.Mutex.

Hahaha... I hope I made sense.  My brain is hurting here.  It doesn't help that I'm fairly new to this type of stuff in Go.  Appreciate you assistance, however.  Thank you!

[Dave Cheney]

Could you show some code, perhaps we can spot the point you're going wrong.

[Val]

Hi Eric
here is some sample code, with
- a "correct" implementation : it passes the race detector, and it behaves as expected : no overlapping of queries on a specific file.
- an "incorrect" impl that doesn't guard the map : it would seem like it works, but the race detector immediately reports the fraud.

The only subtle thing is to not confuse the mutex for the map, and the mutex for each file.  There should not exist a "function that handles the unlocking of the map[string]*sync.Mutex",  the map access itself is ultra-fast and the map locking should be nothing more than   mutex.Lock() ; defer mutex.Unlock() .  It is important to release the global mutex before executing the sqlite query.

For the general problem of re-acquiring a mutex, I learned today in the archives that code involving recursive (reentrant) mutex locking is frowned upon, regarded as a code smell, a bad practice that is very bug-prone.

Cheers
Val

[Eric Brown]

package configurationLockExample

import (
    "sync"
)

type _lockReference struct {
    fileIdentifier map[string]*sync.Mutex

var (
    _configurationLock _lockReference
    _lockReferenceLock sync.RWMutex
)

func init() {
    _configurationLock.fileIdentifier = make(map[string]*sync.Mutex)
}

func (lockReference *_lockReference) Lock(identifier string) {
    _lockReferenceLock.Lock()

    if _, hasKey := lockReference.fileIdentifier[identifier]; hasKey {
        // - reserved for future development; intentionally left blank
    } else { // mutex not already constructed in referenced map; construct it
        lockReference.fileIdentifier[identifier] = &sync.Mutex{}
    }

    lockReference.fileIdentifier[identifier].Lock()

    _lockReferenceLock.Unlock()
}

func (lockReference *_lockReference) Unlock(identifier string) {
    _lockReferenceLock.Lock()

    defer func() {
        errorReturn := recover()
        _lockReferenceLock.Unlock()
    }()

    lockReference.fileIdentifier[identifier].Unlock()
}

func (lockReference *_lockReference) Delete(identifier string) {
    _lockReferenceLock.Lock()

    if _, hasKey := lockReference.fileIdentifier[identifier]; hasKey
        delete(lockReference.fileIdentifier, identifier)
    }

    _lockReferenceLock.Unlock()
}

func StoreConfiguration(configurationFile string, recordName string, recordContent string) error {

    _configurationLock.Lock(configurationFile)

    defer _configurationLock.Delete(configurationFile)
    defer _configurationLock.Unlock(configurationFile)

    // handle configuration stuff here

[Eric Brown]

Sorry, left some comments and stuff in there.  Tried to condense it as much as possible.

[Dave Cheney]

This code does not compile, your unlock function has an unused variable.

[Dave Cheney]

Also, try using the two return value version of a map lookup to avoid having to recover from a nil pointer error when unlocking a non existent lock value.

https://tour.golang.org/moretypes/22

[Eric Brown]

Appreciate the sample, very much.  Go is my first language, and still learning.  It's just a hobby at the moment (which I'm sure you can tell by my amateur code).  Still need to learn about coding standards, etc.

[Eric Brown]

Thank you, Dave.  That was just code I copied & pasted, and removed pieces while in the textarea submit box on this page.  I didn't know about this way of the map lookup... thanks.  I'm still learning Go.  It's actually my first language, and still need to look into coding standards and syntax, which I'm sure you could tell looking at my mess.  Again, appreciate your help!1234