handleCreateError()] [E] CreatePost: database table is locked: repository'

[typeless]

• Gitea version (or commit ref):
  Gitea 1.10

• Git version:
  it version 2.7.4

• Operating system:
  "Ubuntu 16.04.2 LTS"

• Database (use [x]):

  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite

• Can you reproduce the bug at https://try.gitea.io:

  • Yes (provide example URL)
  • No
  • Not relevant

• Log gist:
  In log/gitea.log
  routers/repo/repo.go:103 handleCreateError()] [E] CreatePost: database table is locked: repository

Description

Creating a new repo would occasionally return HTTP code 500.
...

[lunny]

I cannot reproduce in master version

[typeless]

@lunny yes, this is not easy to reproduce. I'll provide more details (or solve it myself ) when I encounter the problem again.

[typeless]

By the way, maybe many users accessing the server simultaneously can trigger it.

[lunny]

maybe

[typeless]

Once the new integration test is merged, we can add certain stress tests for such scenario. I am also thinking about migrate all global maps to syncmap gradually. That makes sense because gitea is a highly concurrent application.

[plessbd]

Similar to #1434 and #1372
At least for #1372 it doesn't have to do with load as I was the only one using the system at that point.

[typeless]

The pattern used at https://github.com/go-gitea/gitea/blob/master/models/repo.go#L2000 is probably related.

See the pattern at the beginning of several related functions:

	if taskStatusTable.IsRunning(gitFsck) {
		return
	}
	taskStatusTable.Start(gitFsck)
	defer taskStatusTable.Stop(gitFsck)

It looks like the intention is to emulate some kind of mutex lock, but the problem is the read and lock is not combined into an atomic operation. They are separated into IsRunning() and Start() respectively. That could be problematic because there is no guarantee that, at the very moment after the call to IsRunning and before Start, another goroutine wouldn't be launched and calling into the same sequence.

[strk]

The code is full of such patterns, I've seen it used for example when setting a user email (to check that no other user has that same email). Fixes for all these cases are really welcome !

[typeless]

@strk I am still not sure that is the root cause of the problem despite it's not quite correct.

Before replacing it with something else, I would try to figure out what resources are shared and supposed to be concurrency-safe.

One thing does confuse me though. There are several tables of the database with duplicate information. For example, there is a watch_num column for each repository, but a table named watch has the the repo_id and watch_num mapping as well. As a result, there is a background goroutine for syncing the two, which I can not understand its purpose and think it'd better be simplified. See https://github.com/go-gitea/gitea/blob/master/models/repo.go#L2009. I guess that would probably be for performance reason, like speeding up some kind of queries. I am not familiar with database management, so please correct me if I am wrong.

[lunny]

I agree @typeless. We need a queue to do that jobs, and for SQLITE, we could add a config item to disable this check running and provide a button on admin panel to run it manually.

[typeless]

@lunny If possible (and reasonable), I'd like to eliminate those tables with redundant information and the 'syncing' thing altogether. I expect the queries serviced by those extra tables can be done with some slightly sophisticated SQLs and probably indexes can help with performance if that really matters.

P.S. Otherwise, keeping the tables and removing the columns with the same information in the other tables.

[lunny]

Is this resolved already?

[typeless]

I haven't seen the problem for a while after upgrade but I am not sure if it's 100% clean (there is a pending PR for issues probably related to this).

[lunny]

Which one? I find all the three links issues to this one are merged or closed.

[typeless]

@lunny #1470

[lunny]

database lock is still exist on sqlite3 and move this to v1.3

[lunny]

continue via #2040