Speculation: Users' Lost Sessions from the SQLite Perspective
Quote from thejerrybryan on 2024-12-01, 4:06 pmI'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.
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.
Quote from Tom Holden on 2024-12-01, 4:14 pmExactly. 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.
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.
Quote from Tom Holden on 2024-12-03, 9:47 amI don't see this behaviour in RM 9.1.6.0 so that is some comfort to those who have not upgraded to RM10!
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!
Quote from Tom Holden on 2024-12-03, 10:03 amNor, 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.
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.
Quote from thejerrybryan on 2024-12-06, 9:30 amI'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?
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?
Quote from Tom Holden on 2024-12-06, 12:35 pmI 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.
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.
Quote from kevync on 2024-12-06, 3:23 pmQuote from Tom Holden on 2024-12-01, 4:14 pmExactly. 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.
Quote from Tom Holden on 2024-12-01, 4:14 pmExactly. 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.
Quote from Tom Holden on 2024-12-09, 11:39 amUpdate 10.0.3 with the needed fix has been released but I'm sure everyone here already knows that!
Update 10.0.3 with the needed fix has been released but I'm sure everyone here already knows that!