Forum

Please or Register to create posts and topics.

Speculation: Users' Lost Sessions from the SQLite Perspective

PreviousPage 2 of 2

I'm sort of trying to  put this into my own language. As I understand it, SQLite by default operates in auto-commit mode. That means that no BEGIN TRANSACTION or COMMIT TRANSACTION is needed. However, consider the following sequence.

UPDATE some_table
SET some_column = some_value;
UPDATE some_other_table
SET some_other_column = some_other_value;

Suppose these two updates are interdependent. In other words, in the event of catastrophic failure such as a power failure, both updates should take place or neither update should take place. To assure that behavior, the sequence should be the following instead because auto-commit could allow the first UPDATE to take place without the second UPDATE taking place.

BEGIN TRANSACTION;
UPDATE some_table
SET some_column = some_value;
UPDATE some_other_table
SET some_other_column = some_other_value;
COMMIT TRANSACTION;

What I'm getting from this discussion is that RM must typically only depend on auto-commit mode and does not surround interdependent updates with a BEGIN TRANSACTION and COMMIT TRANSACTION pair. If RM did routinely use the BEGIN TRANSACTION and COMMIT TRANSACTION pair, then the bug in the Relationship Report would have created an error in other places in RM. Namely, if the BEGIN TRANSACTION operator were used then it would have failed because a transaction was already in progress due to the Relationship Report bug.

Exactly. As usual, you've described it better than me.

I can't imagine why the programmers would have a BEGIN TRANSACTION in the Rel Chart report. And, based on the timing of the journal file being created, on exiting from the report.

kevync has reacted to this post.
kevync

I don't see this behaviour in RM 9.1.6.0 so that is some comfort to those who have not upgraded to RM10!

Nor, in RM10, do I see the journal file appearing and persisting after running any other report involving relationships than the Relationship Chart report itself. If the latter is exited from settings without having generated a report, there is no journal file created. The Generate Report command has to be issued to stimulate the bug.

I'm going to expose my ignorance of SQLite's inner workings again.

Suppose you do a BEGIN TRANSACTION, followed by some sort updating operation, not yet followed by a COMMIT TRANSACTION. At that very instant in time, exactly where is the new data you updated?

It occurs to me that there are two possibilities.

1. The changed data has actually been written to the real database and has also been written to the rollback journal. So until the COMMIT TRANSACTION is executed, a ROLLBACK is possible. It could be a ROLLBACK initiated by an actual ROLLBACK command. Or it could be a ROLLBACK initiated by SQLite itself upon startup when it discovers an uncommitted transaction. In either case, the ROLLBACK would consist of using the rollback journal to undo the transaction. And the only thing a COMMIT would have to do would be to delete the rollback journal because the data is already in the real database.

2. The changed data has not actually been written to the real database. Rather, it has been written to some sort of temporary and possibly in memory database and also to the rollback journal. So until the COMMIT TRANSACTION is executed, a ROLLBACK is possible. It could be a ROLLBACK initiated by an actual ROLLBACK command. Or it could be a ROLLBACK initiated by SQLite itself upon startup when it discovers an uncommitted transaction. In either case, the ROLLBACK would consist of deleting the temporary database and the rollback journal because the data was never entered into the real database in the first place. If this is the correct scenario, then it would be the COMMIT that does the real work of writing the data to the real database and deleting the rollback journal.

It seems to me that option 1 is the most likely way it actually works, but there is some discussion suggesting that it's option 2. Does anybody know for sure?

kevync has reacted to this post.
kevync

I think, from my recent observations, that it is #2. While the manual-start transaction is in progress, all data changes are made only to the in-memory database and the operations logged in the rollback journal. You can check by running the Rel Chart report so that the journal file is created, make some edit or addition in RM, and check with SQLiteSpy if it's in the database file on the drive. The COMMIT causes the writing of the changes to the drive file atomically but the OS might fail to complete. SQLite does not delete the journal until the OS reports success and, if it returns an error instead, it can use the logged operations in it to rollback what got written to the drive. It's possible I've inferred how rollback works incorrectly - maybe the COMMIT copies the in-memory database over the drive file but that could risk an irrecoverable state should the write fail and the in-memory database be lost, e.g., a power interruption.

kevync has reacted to this post.
kevync
Quote from Tom Holden on 2024-12-01, 4:14 pm

Exactly. As usual, you've described it better than me.

I can't imagine why the programmers would have a BEGIN TRANSACTION in the Rel Chart report. And, based on the timing of the journal file being created, on exiting from the report.

I had that thought also -- the seems odd -- but maybe there is some reasoning if they do a temp table etc.

 

Update 10.0.3 with the needed fix has been released but I'm sure everyone here already knows that!

kevync has reacted to this post.
kevync

hopefully that is most of the needed fixes  and we can soon see Enchancements

PreviousPage 2 of 2