Is it better to open SQLite in multi-thread mode?

[frankbraun]

With the currently used combination of flags in https://github.com/mattn/go-sqlite3/blob/master/sqlite3.go (compilation with -DSQLITE_THREADSAFE and sqlite3_open_v2() called with flag SQLITE_OPEN_FULLMUTEX) SQLite database files are opened in serialized mode (see https://www.sqlite.org/threadsafe.html).

Since sql.Open() (https://golang.org/pkg/database/sql/#Open) is safe for concurrent use by multiple goroutines it should be enough to open SQLite database files in multi-thread mode (flag SQLITE_OPEN_NOMUTEX instead of SQLITE_OPEN_FULLMUTEX). This should also improve performance.

Or am I missing something?

[mattn]

I don't think that SQLITE_THREADSAFE affect only for sql.Open.

[drinkmystery]

I agree with @frankbraun.
https://golang.org/src/database/sql/sql.go?s=44550:44604#L1014
The Package sql provides goroutine-safe access to a single database connection by using internal mutex.

As https://www.sqlite.org/threadsafe.html says, when using -DSQLITE_THREADSAFE=1 and SQLITE_OPEN_NOMUTEX, the SQLite is in Multi-thread mode.
In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.

As SQLite's source code shows, when in Multi-thread mode, the SQLite still has an CoreMutex.

#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-20520-54086 */
    case SQLITE_CONFIG_MULTITHREAD: {
      /* EVIDENCE-OF: R-14374-42468 This option sets the threading mode to
      ** Multi-thread. */
      sqlite3GlobalConfig.bCoreMutex = 1;  /* Enable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 0;  /* Disable mutex on connections */
      break;
    }
#endif
#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-59593-21810 */
    case SQLITE_CONFIG_SERIALIZED: {
      /* EVIDENCE-OF: R-41220-51800 This option sets the threading mode to
      ** Serialized. */
      sqlite3GlobalConfig.bCoreMutex = 1;  /* Enable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 1;  /* Enable mutex on connections */
      break;
    }
#endif

Since the Package sql already used mutex on connections, the SQlite's bFullMutex on connections is a bit redundant.

Just using -DSQLITE_THREADSAFE=1 and SQLITE_OPEN_NOMUTEX is enough.

[mattn]

How about to add _mutex ? #540

[gjrtimmer]

Closed by #540