Concurrency issue on POST /v3/access/users/authenticate

[nielsek]

GNS3 version: 3.0.0rc1

Error messages

> POST /v3/access/users/authenticate HTTP/1.1
> Host: 10.254.0.1:3080
> User-Agent: curl/8.5.0
> Accept: */*
> Content-Type: application/json
> Content-Length: 42
>
} [42 bytes data]
< HTTP/1.1 500 Internal Server Error
< date: Tue, 01 Oct 2024 16:25:24 GMT
< server: uvicorn
< content-length: 72
< content-type: application/json
<
{ [72 bytes data]
* Connection #0 to host 10.254.0.1 left intact
{"message":"Database error detected, please check logs to find details"}

2024-10-01 16:24:05 ERROR gns3server.api.server:164 Controller database error in /v3/access/users/authenticate (POST): (sqlite3.OperationalError) database is locked
[SQL: UPDATE users SET updated_at=? WHERE users.user_id = ?]
[parameters: ('2024-10-01 16:23:53.000000', '47e9c02dfba34b14b000b6a780ac8667')]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

2024-10-01 16:24:07 ERROR gns3server.api.server:164 Controller database error in /v3/access/users/authenticate (POST): (sqlite3.OperationalError) database is locked
[SQL: UPDATE users SET last_login=CURRENT_TIMESTAMP, updated_at=CURRENT_TIMESTAMP WHERE users.user_id = ? RETURNING updated_at]
[parameters: ('47e9c02dfba34b14b000b6a780ac8667',)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Reproducing the error

If you start around 7-8 while loops that continuously authenticates, then you won't have to wait long for the error to appear.
This has only been tested using 1 user account. It has not been tested if several different accounts also produces the same database lock.

while true; do curl -v -s -X POST "http://10.254.0.1:3080/v3/access/users/authenticate" -H "Content-Type: application/json"  -d '{"username": "admin", "password": "admin"}' 2>&1 | grep -B10 -A10 500 ; done

Real world observations

This issue has been observed while utilizing some Ansible based tooling, which will utilize the GNS3 API to add/remove/modify multiple VMs at the same time.

[grossmj]

I Googled around to understand the "database is locked" issue and according the Django doc https://docs.djangoproject.com/en/dev/ref/databases/#database-is-locked-errors

SQLite is meant to be a lightweight database, and thus can’t support a high level of concurrency. OperationalError: database is locked errors indicate that your application is experiencing more concurrency than sqlite can handle in default configuration. This error means that one thread or process has an exclusive lock on the database connection and another thread timed out waiting for the lock the be released.

Python’s SQLite wrapper has a default timeout value that determines how long the second thread is allowed to wait on the lock before it times out and raises the OperationalError: database is locked error.

If you’re getting this error, you can solve it by:

Switching to another database backend. At a certain point SQLite becomes too “lite” for real-world applications, and these sorts of concurrency errors indicate you’ve reached that point.

Rewriting your code to reduce concurrency and ensure that database transactions are short-lived.

Increase the default timeout value by setting the timeout database option

This will make SQLite wait a bit longer before throwing “database is locked” errors; it won’t really do anything to solve them.

So I have increased that timeout value and it seems I cannot reproduce the problem using the 7-8 while loops.

I believe the long term solution would be to use a real database for users who expect a heavy usage of their GNS3 server. Thanks to SQLAlchemy, it should be easy to add support for this quite quickly.