API Database Locking

[Kedarius]

I've been playing with a little project, mainly for fun and learning new tools. JLCPCB now offers that I can have a "stock" of components at their factory for PCB assembly. From InvenTree point of view it is more like an external stock location. As far as I know there is no API to download the stock status/actions, but I can manually export XLSX file with current stock count.

So I've decided to write a small tool to take this file and update a specified dedicated stock location with it. It works relatively fine, but there are some questions I need to consider and decide. I.e. should I delete the stock that is not in the XLSX file? Should I try to detect stock movement somehow or just "delete all and import all",.... However, there are a few things I would like to discuss.

First is the most important and it relates to the API. If I do a lot of parallel calls, I get an HTTP 500 error database locked. I really think that this is not a good practice. a) the error gets logged and clutters the system server log, b) it should differ from other internal errors.
Of course, the simplest solution seems to be to serialize the calls, but that is not a solution. It only means that I do not block myself but if the instance would be a little more used, I could run into another users/api calls locking the database.
Also related question, is there a way how to delete 100 notifications at once (from the Bell UI?).

Another related question is about the external locations. Would it be possible to have them distinguished in the stock overview? For example, instead of a 78, show something like 30+48 ? The idea behind this is that it would not mix with parts I have at hand.

[matmair]

Sounds like you are using sqlite as a database. There is no way to prevent locking with sqlite because it only supports one connection data at a time (it is file based).

[wolflu05]

A lock is not a conflict. We could be more specific and send a 503 (service unavailable) or a 423, but I don't think a 432 is a good solution as it is part of a WebDAV thing. Same is explained here. One think we could do, is add some kind of retry on the Django site, but I'm not sure if that's possible.

[Kedarius]

I mean, I do not mind the 500 as it has the error in the response body. I just do not like it being logged and cluttering the system as it is not a "real error".
Or if you could suggest any other solution apart from using full-fledged DB?

[matmair]

A database lock is a serious issue and should be logged. The service is unavailable to the users.
I think we have very different expectations of availability and stability for a webservice service.
I see no way this system can be used by more than 1 user with SQLite - as stated in the first few paragraphs of the install docs:
. It will be a less than optimal experience if you run your production with SQLite.

[Kedarius]

Well, this instance is being used by 3 people in total :-) And if it goes down then it is not a big deal. And I did install it maybe even before the installation docs were created.... However I plan to use some simple bots like the mentioned one and I need a stable solution. I do not mind having the site down but I do mind having inconsistent data in it. So far the SQLite file worked fine but I may consider upgrading to a real DB just to be safe....

[matmair]

As long as it is just humans using it even SQLite works most of the time, the second machines start sending a lot of parallel requests you see the problems. Imports are always a huge problem on SQLite due to the long blocking.

For switching it should be enough to inv export-records, setup the database, change the config .yaml, inv migrate, inv import-records --clear

[SchrodingersGat]

As far as I know there is no API to download the stock status/actions, but I can manually export XLSX file with current stock count.

You can export this data in two ways:

1. From the admin interface
2. Via the API, or via the "stock table" (see image below)

First is the most important and it relates to the API. If I do a lot of parallel calls, I get an HTTP 500 error database locked.

As raised above, the sqlite database backend does not handle parallel requests well at all. That being said, can you please share the entire error message you are seeing?

Also related question, is there a way how to delete 100 notifications at once (from the Bell UI?).

Yes. Go to the notifications centre and select "Mark all as read":

[Kedarius]

As far as I know there is no API to download the stock status/actions, but I can manually export XLSX file with current stock count.

You can export this data in two ways:

1. From the [admin interface](https://docs.inventree.org/en/stable/settings/export/)

2. Via the API, or via the "stock table" (see image below)

Sorry about the confusion. In this case I meant the JLCPCB API. The whole meaning of that sentence should be "JLCPCB does not provide any API to download stock status or stock actions (i.e. parts arrival or parts being used in assembly) but at least they provide downloadable XLSX file with current stock status. That is why I decided to write a small tool to 'synchronize' the state of dedicated stock location in InvenTree according to this XLSX file...."
So the idea is basically to compare the stock location with XLSX file and add/remove inventory according to the difference.

As far as I know there is no API to download the stock status/actions, but I can manually export XLSX file with current stock count.

You can export this data in two ways:

1. From the [admin interface](https://docs.inventree.org/en/stable/settings/export/)

2. Via the API, or via the "stock table" (see image below)

Sure, here it is:
Type: OperationalError
Path: /api/stock/
Info: database is locked
When: 18. listopadu 2023 16:15
Data:
Traceback (most recent call last):

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/backends/sqlite3/base.py", line 423, in execute
return Database.Cursor.execute(self, query, params)

sqlite3.OperationalError: database is locked


The above exception was the direct cause of the following exception:


Traceback (most recent call last):

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/rest_framework/views.py", line 506, in dispatch
response = handler(request, *args, **kwargs)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/rest_framework/generics.py", line 242, in post
return self.create(request, *args, **kwargs)

File "/data/inventree/inventree/InvenTree/./stock/api.py", line 752, in create
item = serializer.save()

File "/data/inventree/inventree/InvenTree/./InvenTree/serializers.py", line 192, in save
super().save(**kwargs)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/rest_framework/serializers.py", line 212, in save
self.instance = self.create(validated_data)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/taggit/serializers.py", line 116, in create
tag_object = super().create(validated_data)

File "/data/inventree/inventree/InvenTree/./InvenTree/serializers.py", line 207, in create
return super().create(initial_data)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/rest_framework/serializers.py", line 962, in create
instance = ModelClass._default_manager.create(**validated_data)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/models/manager.py", line 85, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/models/query.py", line 453, in create
obj.save(force_insert=True, using=self.db)

File "/data/inventree/inventree/InvenTree/./stock/models.py", line 479, in save
super(StockItem, self).save(*args, **kwargs)

File "/data/inventree/inventree/InvenTree/./InvenTree/models.py", line 66, in save
super().save(*args, **kwargs)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/mptt/models.py", line 1014, in save
super(MPTTModel, self).save(*args, **kwargs)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/models/base.py", line 739, in save
self.save_base(using=using, force_insert=force_insert,

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/models/base.py", line 776, in save_base
updated = self._save_table(

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/models/base.py", line 881, in _save_table
results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/models/base.py", line 919, in _do_insert
return manager._insert(

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/models/manager.py", line 85, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/models/query.py", line 1270, in _insert
return query.get_compiler(using=using).execute_sql(returning_fields)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
cursor.execute(sql, params)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/backends/utils.py", line 98, in execute
return super().execute(sql, params)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/backends/utils.py", line 79, in _execute
with self.db.wrap_database_errors:

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)

File "/data/inventree/inventree-env/lib64/python3.10/site-packages/django/db/backends/sqlite3/base.py", line 423, in execute
return Database.Cursor.execute(self, query, params)

django.db.utils.OperationalError: database is locked

The sad thing is that up until now the sqlite backend worked fine and is very convenient. But without serialization I did get 6-8 of the API calls through out of 24. And that scares me a little - the installation and usage of this InvenTree site is really small but I need to have consistent data in it :-)

[SchrodingersGat]

sqlite is super quick and convenient to setup (which is why we use that for our out-of-the-box "developer" setup) - however you will soon run into exactly the issue you are seeing here.

If you are doing lots of parallel reads it should be fine, but parallel writes will absolutely run afoul of sqlite's limitations.

From the error message you show, intercepting this and throwing a custom error code would be tricky - there are multiple places in the code where a locked database could throw an error...

[SchrodingersGat]

Ref: https://docs.djangoproject.com/en/4.2/ref/databases/#database-is-locked-errors

[SchrodingersGat]

you could try increasing your database timeout as per the above link - but switching to a more robust database solution early would be a better approach IMO

[Kedarius]

Thanks for your reply. I will switch to postgres and see what happens :-) after all SQLlite makes it super easy to backup and restore.

[Kedarius]

Is there any documentation how to migrate database? I did find only https://docs.inventree.org/en/latest/start/migrate/#migrating-data-to-newer-version which basically covers the

For switching it should be enough to inv export-records, setup the database, change the config .yaml, inv migrate, inv import-records --clear

as @matmair said. And it did work fine but it did not transfer my API tokens. Thats not a big issue, but I wonder if there is anything else that does not get transferred?

[wolflu05]

You can find all excluded models for importing/exporting here.

InvenTree/tasks.py

Lines 35 to 50 in 23ea746

	excludes = [
	"contenttypes",
	"auth.permission",
	"users.apitoken",
	"error_report.error",
	"admin.logentry",
	"django_q.schedule",
	"django_q.task",
	"django_q.ormq",
	"users.owner",
	"exchange.rate",
	"exchange.exchangebackend",
	"common.notificationentry",
	"common.notificationmessage",
	"user_sessions.session",
	]