Do I need open every http handler for thread safe operation?

[davyzhang]

No description provided.

[mattn]

did you try below?

db, err := sql.Open("sqlite3", "file:locked.sqlite?cache=shared&mode=rwc")

[davyzhang]

so shared cache changed locking mode somehow, Thanks mattn, I will try your fix
I found the doc in sqlite3:

2.1 Transaction Level Locking
At most one connection to a single shared cache may open a write transaction at any one time. This may co-exist with any number of read transactions.

[mattn]

I'm not untra expert about locking of sqlite3. do you have any suggestion to fix this?

[davyzhang]

So far as I can tell, using shared cache and wal will make transaction faster, and avoid "many" errors.

But not all of them, I still can see "data base locked" error in my log, but much fewer than before

[davyzhang]

very dirty test for reproduce the problem
https://gist.github.com/davyzhang/1392344f993eac471c90

not working on my ssd mac pro
but very soon will exit when run my aws micro vps with mag disk

usage dbtest 0/1

it will locked no matter use 2 connections or single connection.

[mattn]

Could you try to add &_busy_timeout=9999999 to connection string?

[davyzhang]

I updated the gist, but... seems no help here still lock error

currently the only solution comes to me is using mutex to seperate reader and writer access. Which is not as described as in sqlite's thread mode doc.

I guess the go's thread mode is bit more different than c, might break the sqlite internal lock mechanism

[Compufreak345]

@davyzhang Did you get any new information about this issue? I've got the same problems.

[davyzhang]

@Compufreak345 Sorry, bad news. I think golang is not a suitable language for sqlite, especially writing http server applications. Thread model is different, like I guess.
I switched my app to mysql/mariadb later after this lock issue, works fine.

[roxma]

@davyzhang

According to #274,

It seems db.SetMaxOpenConns(1) or _busy_timeout would be helpful.

[gjrtimmer]

TODO: Add db.SetMaxOpenConn(1) to README

[gdamore]

SetMaxOpenConn(1) only seems to affect if using a single process. I'm seeing this error when using multiple processes (two separate go programs).

[rittneje]

@gdamore What error specifically are you seeing? Also, if possible please provide the two go programs in question (or a minimal version that reproduces the issue).

[gdamore]

Unfortunately I cannot provide the source for the programs, as they have a lot of other proprietary IP in them. I can however describe their behavior:

1. collect program opens the database and periodically inserts new events (telemetry) -- single table
2. query program opens the database, then runs a query to create a temporary table, using a select from the primary table. (The select performs group by, and ordering, etc.)

Both programs may have multiple tables open that they do this for. (For different resources.)

If the query program runs before the collect program (or rather before the collect program tries to open the database), then the collect program will encounter this error. If the collect programs runs first, then we don't seem to have any problems at all.

Examination shows that the query program seems to be acquiring a write lock with fcntl on the last 510 bytes in the database file, and does not ever release it.

We are using WAL and shared cache, with just a single database connection per process.

Both programs are written in Go using this library. The platform is illumos based.

If I find time later, I'll try to write a minimal repro case.

[arp242]

Does cache=shared have any effect with db.SetMaxOpenConn(1)? As I read the SQLite docs I think it doesn't?

If shared-cache mode is enabled and a thread establishes multiple connections to the same database, the connections share a single data and schema cache.

But with SetMaxOpenConn(1) I think Go never opens multiple connections?

In my testing concurrency issues seem resolved with just setting the connection limit to 1 (but I'm not sure if my tests are comprehensive).

[rittneje]

@arp242 In general, it is true that shared cache mode is irrelevant if you throttle the pool to one connection. However, if you have two separate connection pools for the same database, then shared cache mode would make a difference, even if each pool is throttled to one connection. But having two pools for the same database is generally indicative of some design issue.

On that note, the author of SQLite considers shared cache mode to be a misfeature (https://sqlite.org/forum/forumpost/1f291cdca4). Turning it on can result in "database table is locked" errors that are very difficult to resolve properly. (Not to be confused with "database is locked" errors, which are easily resolved in general by setting a busy timeout.) Consequently, despite what is currently stated in this repo's readme, you should never use shared cache. The only exception is if you are intend to have an in-memory database shared across multiple connections, but even then you are probably better served just throttling the connection pool to one anyway.

[arp242]

Right; thanks @rittneje. I created a PR to update the documentation in the README a bit: #827 – let me know if I got anything wrong 😅