Gitea: random database lock when sqlite database

[lunny]

This is a summary issue since there are so many issues to report the database lock when using sqlite. I will close all other issues and please continue in this issue.

[lunny]

And maybe we should pause make test-sqlite util this issue is resolved.

[typeless]

Reference: https://sqlite.org/unlock_notify.html

[bkcsoft]

@typeless Unless that is available in https://github.com/mattn/go-sqlite3 _and_ https://github.com/go-xorm/xorm/ we can't use it 😞

[typeless]

@bkcsoft Agreed. This would probably need to be fixed upstream.

[andreynering]

I hope #2116 fixes the issue

[andreynering]

Reopening, since it seems it not really fixed.

[PaulBINK]

We are getting this problem frequently when working with issues: add label, comment, close issue, reopen. Sooner or later it will 500 on us. According to the logs it happens when calling GetNotificationCount.

[jonasfranz]

Enabling WAL mode worked for me. https://www.sqlite.org/wal.html

[hoxnox]

Same for me.

Caused by: hudson.plugins.git.GitException: Command "/usr/local/git/bin/git fetch --tags --progress ssh://sources.acme.com/repo.git +refs/heads/*:refs/remotes/origin/*" returned status code 128:
stdout: 
stderr: Gitea: Internal error
Failed to get repository: database is locked

[typeless]

Potential alternatives that could fix this:

https://github.com/bvinc/go-sqlite-lite
https://github.com/crawshaw/sqlite

[bvinc]

Author of go-sqlite-lite here. My driver, and also crawshaw's driver, don't provide a database/sql interface, so they would be extremely inconvenient to use. But for a few reasons, it would probably help with locking errors. My driver gives complete control over connections, which means you'll only get locking errors if you're doing something directly to cause them. The crawshaw driver defaults to using shared cache mode and WAL mode, and uses the unlock_notify API to prevent locking issues with shared cache mode.

I think the current mattn driver is the best one to use if you require a database/sql driver. Their FAQ mentions setting the the maximum number of open connections to 1. Have you tried that?

https://github.com/mattn/go-sqlite3#faq

[lunny]

@bvinc thanks for your reply, It's a good idea to send a PR to set open connections to 1 when using mattn/go-sqlite3

[hoxnox]

What is the best way to migrate from sqlite to postgresql?

[typeless]

Now that https://github.com/mattn/go-sqlite3/pull/439 is merged, we can either use the tip right away or wait for the new release of go-sqlite3 to fix the issue. Note that to enable this functionality, we have to add a new build tag sqlite_unlock_notify.

Edit: I hope the users don't have to specify -tags sqlite sqlite_unlock_notify besides just -tags sqlite in that it would be better to not expose the nuances of a dependent package. I don't know how it should be done though.

Edit2: I changed my mind. If one wants to build Gitea from source, he should be capable of handling the additional flag. We should update the documentation accordingly though.

[lunny]

at first, we need a PR to update go-sqlite3 library.

[lunny]

Even #5144 resolved single process issue. There is some issues when pushing via SSH. This should be closed by #4886.

[sapk]

@typeless go-sqlite3 library with sqlite_unlock_notify tag seems to not build on win 32bit https://drone.gitea.io/go-gitea/gitea/3536/18

[typeless]

@sapk Similar to https://github.com/mattn/go-sqlite3/issues/238. Will submit a fix soon.

[sapk]

@typeless exactly what I was searching but I use the terms uint too large and I found nothing ...

[typeless]

I have submitted https://github.com/mattn/go-sqlite3/pull/654.
However, I cannot find a computer with Windows/386 to test. 😨

[sapk]

@typeless use gox to cross-compile ?

[typeless]

@sapk I just installed it. But after typing gox -osarch="windows/linux" -tags sqlite_unlock_notify
, it only prints Number of parallel builds: 31. I've not figured out how to use it to run go test either.

Edit: fixed at https://github.com/mattn/go-sqlite3/pull/655

[lunny]

Just type make release-windows on gitea?

[typeless]

https://github.com/go-gitea/gitea/pull/5162 fixes the CI failure and TAGS='bindata sqlite sqlite_unlock_notify' make release-windows passes.

[lunny]

Closed this since it's not a system problem any more. If there is any issue please fire another issue.