[Question]: What is the situation when the user table is locked for a long time

[shentingming]

Self Checks

• I have searched for existing issues search for existing issues, including closed ones.
• I confirm that I am using English to submit this report (Language Policy).
• Non-english title submitions will be closed directly ( 非英文标题的提交将会被直接关闭 ) (Language Policy).
• Please do not modify this template :) and fill in all the required fields.

Describe your problem

peewee.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction') ，I didn't have this problem before, but it keeps getting this error today. I think it's still the user table

[dosubot]

A table in a database, such as the user table, can become locked for a long time when a transaction holds a lock and does not release it promptly. This often happens if a transaction is left open (for example, due to a long-running operation, a crash, or a deadlock), or if multiple concurrent operations are competing for the same resource. In your case, the error peewee.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction') means that your query waited too long for a lock to be released and was aborted by the database.

In RAGFlow, user-related operations (such as updates and deletes) are wrapped in transactions using Peewee's DB.atomic() context manager. If one of these transactions takes too long or is blocked by another transaction, it can cause other operations to wait and eventually time out with the error you are seeing. Bulk operations or large batch updates can also contribute to long locks if they are not optimized or if there is high contention on the user table. The codebase does not use explicit advisory locks for user operations, so the locking is managed by the database engine itself, typically at the row or table level depending on the query and database configuration (source).

If this problem started recently, consider whether there have been changes to your workload, such as more concurrent user operations, larger batch jobs, or changes in transaction patterns. Also, check for any uncommitted or long-running transactions that might be holding locks.

If you are running on Mac, there are known issues with MySQL and Docker that can cause locking problems. Setting MACOS=1 in your docker/.env file or switching to MariaDB instead of MySQL has resolved similar issues for other users. If you switch from MariaDB to MySQL, you may need to manually delete the ./volumes/mysql_data directory and add --innodb_use_native_aio=0 to the MySQL container command in your docker-compose-base.yml (source).

To troubleshoot:

• Identify and terminate any long-running or stuck transactions on your database.
• Review your application logs for operations that might be holding locks.
• Optimize batch operations to reduce lock duration.
• If on Mac, try the environment variable or database switch as described above.

If you need more targeted help, providing details about your database system (MySQL, MariaDB, PostgreSQL), recent changes, and the specific operations being performed when the lock occurs would be helpful.

To reply, just mention my name @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}