Hello, Could you please explain the problem you observe more precisely? This is with MySQL only, right? (Tests with SQLite and PostgreSQL welcome). And what is blocked during the process more precisely (e.g. a specific table)? And with what consequence (e.g. that specific user cannot access FreshRSS)? I believe your suggested approach might create other problems, but we can try to find another way to fix. In particular, we have an intermediate entrytmp table (which is OK to block) before updating the entry table (for which the transaction should be fast).

When I enable the full-text crawling plugin, if the number of my articles reaches thousands, this transaction will last for hours, causing the _feed table to be locked.

I don't know about other databases and whether they lock after opening a transaction.

Can you post of the link to the extension you are using?

I cannot accept the approach of this PR as it risks breaking other things, such as the detection of gone articles, count of unread articles, etc. (However, you can put this code in the extension itself, or in another extension, if you accept the drawbacks)

It would be interesting to test whether the lock comes from articles that have been updated, as opposed to new articles.
The logic about updated articles has been improved a bit in the edge branch.

We should make sure that our transaction do not block the other operations. See https://dev.mysql.com/doc/refman/8.1/en/innodb-transaction-isolation-levels.html
In other words, we need a better investigation of what is blocking what more precisely. And also to know whether it is MySQL-specific.

Hello, Could you please explain the problem you observe more precisely? This is with MySQL only, right? (Tests with SQLite and PostgreSQL welcome). And what is blocked during the process more precisely (e.g. a specific table)? And with what consequence (e.g. that specific user cannot access FreshRSS)? I believe your suggested approach might create other problems, but we can try to find another way to fix. In particular, we have an intermediate entrytmp table (which is OK to block) before updating the entry table (for which the transaction should be fast).

When I enable the full-text crawling plugin, if the number of my articles reaches thousands, this transaction will last for hours, causing the _feed table to be locked.
I don't know about other databases and whether they lock after opening a transaction.

Can you post of the link to the extension you are using?

I cannot accept the approach of this PR as it risks breaking other things, such as the detection of gone articles, count of unread articles, etc. (However, you can put this code in the extension itself, or in another extension, if you accept the drawbacks)

It would be interesting to test whether the lock comes from articles that have been updated, as opposed to new articles. The logic about updated articles has been improved a bit in the edge branch.

We should make sure that our transaction do not block the other operations. See https://dev.mysql.com/doc/refman/8.1/en/innodb-transaction-isolation-levels.html In other words, we need a better investigation of what is blocking what more precisely. And also to know whether it is MySQL-specific.

When the transaction is started, the <user_name>_feed table will be blocked, and any related modification and update operations will be blocked. I used the xExtension-Readable plugin here, for getting the full text.

According to the logic of the feedController, the entry_before_insert hook will be executed whether it is updating an article or getting a new article, so both operations will be affected.

Both of the following may be time-consuming operations, making the transaction slow.

I am running a test at the moment with PostgreSQL on a use-case resembling yours, with a refresh process that takes several hours due to many entries calling entry_before_insert, and all is good, i.e. the rest of the interface is completely usable and not blocked.

I have also checked the code from https://github.com/printfuck/xExtension-Readable can cannot see anything significantly different than my own teste.

So what remains to be done is to compare with MySQL / MariaDB and SQLite.

I am running a test at the moment with PostgreSQL on a use-case resembling yours, with a refresh process that takes several hours due to many entries calling entry_before_insert, and all is good, i.e. the rest of the interface is completely usable and not blocked.

I have also checked the code from https://github.com/printfuck/xExtension-Readable can cannot see anything significantly different than my own teste.

So what remains to be done is to compare with MySQL / MariaDB and SQLite.

Thanks for your test, I test in sqlite, MySQL, PostgreSQL, where sqlite and MySQL block other operations like Toggle favorite or Toggle read. And PostgreSQL is fine.

If it is convenient, please help to test sqlite and MySQL.

@tqcq I had a quick test with SQLite already and identified one path, which was blocking the database, fixed with #5625
Feedback welcome, if you can give it a try. I has not yet debugged with MySQL.

@tqcq I have tested this evening with MySQL after the patch #5625 and I cannot reproduce any lock anymore. Additional tests welcome

#5648