sqlite3 with python: where is data stored before commit?

Question

I saw a question similar to mine, but for SQL databases in general. It said that when you insert data but before the commit, only the commands are saved and they are saved in RAM. Only after the commit will the RAM be emptied and the data be in the database.

However, I think I was noticing different behavior using sqlite3 with Python. I left my commit at the very end of the script. My program ran out of space and aborted partway, but it wasn't because of the RAM being full. It said that the disk space was full, which I confirmed was indeed full on my computer, a substantial part being taken up by my database file.

I know I need more disk space to create this database, which won't be a problem, but I was wondering, at least specifically with sqlite3 and Python (if the particular case matters), what is actually happening with my data/commands before the commit? In other words, where and how are they getting stored? Is it different for different databases? Should I ever be worried about using up all my RAM when working with databases by not committing until the end?

Answer 1

SQLite copies unmodified pages of data in a temporary rollback journal file, and applies your changes to in memory copies of those same pages. When you make enough changes so that memory becomes a problem, changes are flushed out to the original database. The rollback journal is used to roll back incomplete transactions.

How different databases handle the rollback journal depends heavily on the database implementation. An ACID compliant database will have to store uncommitted transaction data somewhere, and a disk-backed journal is an obvious choice.

The SQLite documentation includes an exhaustive explanation on how exactly it implements transactions.