adam/Remote-Terminal-for-MeshCore
1
0 Fork 0
mirror of https://github.com/jkingsman/Remote-Terminal-for-MeshCore.git synced 2026-11-03 08:02:19 +01:00
Code Issues 26 Packages Projects Releases Wiki Activity

SQLite write conflicts #128

New Issue
Closed
opened 2026-10-05 01:58:46 +02:00 by adam · 13 comments
adam commented 2026-10-05 01:58:46 +02:00
Owner
Copy link
Copy source

Hi!
Today i saw write conflict in logs. Probably we need to add locking to sqlite write thread.

remoteterm-1  | 2026-04-12 08:56:57 - meshcore - ERROR - Error in event handler for EventType.RX_LOG_DATA: cannot commit transaction - SQL statements in progress
remoteterm-1  | Traceback (most recent call last):
remoteterm-1  |   File "/app/.venv/lib/python3.12/site-packages/meshcore/events.py", line 213, in _execute_callback
remoteterm-1  |     await callback(event)
remoteterm-1  |   File "/app/app/event_handlers.py", line 151, in on_rx_log_data
remoteterm-1  |     await process_raw_packet(
remoteterm-1  |   File "/app/app/packet_processor.py", line 288, in process_raw_packet
remoteterm-1  |     packet_id, is_new_packet = await RawPacketRepository.create(raw_bytes, ts)
remoteterm-1  |                                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
remoteterm-1  |   File "/app/app/repository/raw_packets.py", line 41, in create
remoteterm-1  |     await db.conn.commit()
remoteterm-1  |   File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 193, in commit
remoteterm-1  |     await self._execute(self._conn.commit)
remoteterm-1  |   File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 160, in _execute
remoteterm-1  |     return await future
remoteterm-1  |            ^^^^^^^^^^^^
remoteterm-1  |   File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 63, in _connection_worker_thread
remoteterm-1  |     result = function()
remoteterm-1  |              ^^^^^^^^^^
remoteterm-1  | sqlite3.OperationalError: cannot commit transaction - SQL statements in progress
Hi! Today i saw write conflict in logs. Probably we need to add locking to sqlite write thread. ``` remoteterm-1 | 2026-04-12 08:56:57 - meshcore - ERROR - Error in event handler for EventType.RX_LOG_DATA: cannot commit transaction - SQL statements in progress remoteterm-1 | Traceback (most recent call last): remoteterm-1 | File "/app/.venv/lib/python3.12/site-packages/meshcore/events.py", line 213, in _execute_callback remoteterm-1 | await callback(event) remoteterm-1 | File "/app/app/event_handlers.py", line 151, in on_rx_log_data remoteterm-1 | await process_raw_packet( remoteterm-1 | File "/app/app/packet_processor.py", line 288, in process_raw_packet remoteterm-1 | packet_id, is_new_packet = await RawPacketRepository.create(raw_bytes, ts) remoteterm-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ remoteterm-1 | File "/app/app/repository/raw_packets.py", line 41, in create remoteterm-1 | await db.conn.commit() remoteterm-1 | File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 193, in commit remoteterm-1 | await self._execute(self._conn.commit) remoteterm-1 | File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 160, in _execute remoteterm-1 | return await future remoteterm-1 | ^^^^^^^^^^^^ remoteterm-1 | File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 63, in _connection_worker_thread remoteterm-1 | result = function() remoteterm-1 | ^^^^^^^^^^ remoteterm-1 | sqlite3.OperationalError: cannot commit transaction - SQL statements in progress ``` <!-- github-sync-id:4247323985 -->
adam closed this issue 2026-10-05 01:58:46 +02:00
adam commented 2026-10-05 01:58:47 +02:00
Author
Owner
Copy link
Copy source

Whoops, good catch! Thanks.

Whoops, good catch! Thanks. <!-- github-comment-id:4232508235 -->
adam commented 2026-10-05 01:58:48 +02:00
Author
Owner
Copy link
Copy source

i believe problem may be not fixed
i received same error for image built from master @ commit 3b7e2737ee

RemoteTerm for MeshCore
v3.11.3·unknown

remoteterm-1  | 2026-04-15 10:58:51 - meshcore - ERROR - Error in event handler for EventType.RX_LOG_DATA: cannot commit transaction - SQL statements in progress
remoteterm-1  | Traceback (most recent call last):
remoteterm-1  |   File "/app/.venv/lib/python3.12/site-packages/meshcore/events.py", line 213, in _execute_callback
remoteterm-1  |     await callback(event)
remoteterm-1  |   File "/app/app/event_handlers.py", line 151, in on_rx_log_data
remoteterm-1  |     await process_raw_packet(
remoteterm-1  |   File "/app/app/packet_processor.py", line 288, in process_raw_packet
remoteterm-1  |     packet_id, is_new_packet = await RawPacketRepository.create(raw_bytes, ts)
remoteterm-1  |                                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
remoteterm-1  |   File "/app/app/repository/raw_packets.py", line 41, in create
remoteterm-1  |     await db.conn.commit()
remoteterm-1  |   File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 193, in commit
remoteterm-1  |     await self._execute(self._conn.commit)
remoteterm-1  |   File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 160, in _execute
remoteterm-1  |     return await future
remoteterm-1  |            ^^^^^^^^^^^^
remoteterm-1  |   File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 63, in _connection_worker_thread
remoteterm-1  |     result = function()
remoteterm-1  |              ^^^^^^^^^^
remoteterm-1  | sqlite3.OperationalError: cannot commit transaction - SQL statements in progress
i believe problem may be not fixed i received same error for image built from master @ commit 3b7e2737ee2305c0ba1a153472b63882475ff691 RemoteTerm for MeshCore v3.11.3·unknown ``` remoteterm-1 | 2026-04-15 10:58:51 - meshcore - ERROR - Error in event handler for EventType.RX_LOG_DATA: cannot commit transaction - SQL statements in progress remoteterm-1 | Traceback (most recent call last): remoteterm-1 | File "/app/.venv/lib/python3.12/site-packages/meshcore/events.py", line 213, in _execute_callback remoteterm-1 | await callback(event) remoteterm-1 | File "/app/app/event_handlers.py", line 151, in on_rx_log_data remoteterm-1 | await process_raw_packet( remoteterm-1 | File "/app/app/packet_processor.py", line 288, in process_raw_packet remoteterm-1 | packet_id, is_new_packet = await RawPacketRepository.create(raw_bytes, ts) remoteterm-1 | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ remoteterm-1 | File "/app/app/repository/raw_packets.py", line 41, in create remoteterm-1 | await db.conn.commit() remoteterm-1 | File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 193, in commit remoteterm-1 | await self._execute(self._conn.commit) remoteterm-1 | File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 160, in _execute remoteterm-1 | return await future remoteterm-1 | ^^^^^^^^^^^^ remoteterm-1 | File "/app/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 63, in _connection_worker_thread remoteterm-1 | result = function() remoteterm-1 | ^^^^^^^^^^ remoteterm-1 | sqlite3.OperationalError: cannot commit transaction - SQL statements in progress ``` <!-- github-comment-id:4251434992 -->
adam commented 2026-10-05 01:58:49 +02:00
Author
Owner
Copy link
Copy source

@jkingsman sorry for tagging, cannot reopen issue

@jkingsman sorry for tagging, cannot reopen issue <!-- github-comment-id:4251444457 -->
adam commented 2026-10-05 01:58:49 +02:00
Author
Owner
Copy link
Copy source

https://github.com/chrisdavis2110/remote-terminal-meshcore/commit/cde4d1744e5448baa11d0f5ee90fedf5b91c9633

i don't see there any changes to event_handlers.py or packet_processor.py, they could be missed

https://github.com/chrisdavis2110/remote-terminal-meshcore/commit/cde4d1744e5448baa11d0f5ee90fedf5b91c9633 i don't see there any changes to event_handlers.py or packet_processor.py, they could be missed <!-- github-comment-id:4251471444 -->
adam commented 2026-10-05 01:58:50 +02:00
Author
Owner
Copy link
Copy source

Ack; will take another look.

Ack; will take another look. <!-- github-comment-id:4254222988 -->
adam commented 2026-10-05 01:58:51 +02:00
Author
Owner
Copy link
Copy source

Okay I've pushed another commit that is a bit more meaningful of a change; moving all our DB traffic to a more context/locky method. TBH I'm not sure exactly what the problem here is -- of course, a DB concurrency issue, but I've had my brain and a bunch of agents scrambling over the codebase to try and repro this failure and I can't even do it in a contrived way, thus I cannot confirm my fix. Hopefully this does it; if not, I'll come back around for a more aggressive whack.

Okay I've pushed another commit that is a bit more meaningful of a change; moving all our DB traffic to a more context/locky method. TBH I'm not sure exactly what the problem here is -- of course, a DB concurrency issue, but I've had my brain and a bunch of agents scrambling over the codebase to try and repro this failure and I can't even do it in a contrived way, thus I cannot confirm my fix. Hopefully this does it; if not, I'll come back around for a more aggressive whack. <!-- github-comment-id:4264537007 -->
adam commented 2026-10-05 01:58:51 +02:00
Author
Owner
Copy link
Copy source

Here's an interesting possible cause for that exact error ("cannot commit transaction - SQL statements in progress"):

https://stackoverflow.com/questions/1049293/sqlite-error-cannot-commit-transaction-sql-statements-in-progress-using-java

=> it happens when a commit is executed while here are unfetched return values.

Here's an interesting possible cause for that exact error ("cannot commit transaction - SQL statements in progress"): https://stackoverflow.com/questions/1049293/sqlite-error-cannot-commit-transaction-sql-statements-in-progress-using-java => it happens when a commit is executed while here are unfetched return values. <!-- github-comment-id:4266206499 -->
adam commented 2026-10-05 01:58:52 +02:00
Author
Owner
Copy link
Copy source

Yes, the issue is definitely around in-flight data fetching; I just wasn't able to force the codebase into a reproduceable flow [that I had any confidence in actually being the root of the problem]. The fix I pushed should hopefully be the solution with some bigger-picture transaction isolation.

Yes, the issue is definitely around in-flight data fetching; I just wasn't able to force the codebase into a reproduceable flow [that I had any confidence in actually being the root of the problem]. The fix I pushed should hopefully be the solution with some bigger-picture transaction isolation. <!-- github-comment-id:4266233079 -->
adam commented 2026-10-05 01:58:53 +02:00
Author
Owner
Copy link
Copy source

Fingers crossed.

I just found it interesting that this issue is not only an issue of concurrent read access vs. write access but may also be related to unfetched return values from e.g. an INSERT statement that includes RETURNING.

But despite SQLITE3 having a bunch of locking mechanisms (like read locks that can be upgraded to write locks, see https://www.sqlite.org/lockingv3.html) - I've not yet digged into the python API to check how they leverage locking.

Fingers crossed. I just found it interesting that this issue is not only an issue of concurrent read access vs. write access but may also be related to unfetched return values from e.g. an INSERT statement that includes RETURNING. But despite SQLITE3 having a bunch of locking mechanisms (like read locks that can be upgraded to write locks, see https://www.sqlite.org/lockingv3.html) - I've not yet digged into the python API to check how they leverage locking. <!-- github-comment-id:4266300090 -->
adam commented 2026-10-05 01:58:54 +02:00
Author
Owner
Copy link
Copy source

But maybe this will be handy - a lightweight SqliteWorker library that streamlines all DB access into a threadsafe queue with only a single dedicated worker thread doing all DB access:

https://medium.com/@roshanlamichhane/sqlite-worker-supercharge-your-sqlite-performance-in-multi-threaded-python-applications-01e2e43cc406

But maybe this will be handy - a lightweight SqliteWorker library that streamlines all DB access into a threadsafe queue with only a single dedicated worker thread doing all DB access: https://medium.com/@roshanlamichhane/sqlite-worker-supercharge-your-sqlite-performance-in-multi-threaded-python-applications-01e2e43cc406 <!-- github-comment-id:4266314652 -->
adam commented 2026-10-05 01:58:54 +02:00
Author
Owner
Copy link
Copy source

Yeahhhh the whole time I was troubleshooting this I kept waffling on just cutting over to a connection pooler or some other more meaningful kind of sequence control that's less miserable to operate on.

Yeahhhh the whole time I was troubleshooting this I kept waffling on just cutting over to a connection pooler or some other more meaningful kind of sequence control that's less miserable to operate on. <!-- github-comment-id:4266324416 -->
adam commented 2026-10-05 01:58:55 +02:00
Author
Owner
Copy link
Copy source

I've not yet digged into the python API to check how they leverage locking

Basically not, from what I understand of sqlite. There are not any batteries included for this kind of thing beyond help from other libraries afaik.

> I've not yet digged into the python API to check how they leverage locking Basically not, from what I understand of sqlite. There are not any batteries included for this kind of thing beyond help from other libraries afaik. <!-- github-comment-id:4266328031 -->
adam commented 2026-10-05 01:58:55 +02:00
Author
Owner
Copy link
Copy source

I'm going to close this -- feel free to open a new issue if the problem recurs.

I'm going to close this -- feel free to open a new issue if the problem recurs. <!-- github-comment-id:4357363484 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Remote-Terminal-for-MeshCore#128