Awesome explanation as always !! Hussein , can you please make a video on databases having non-relational data model and how do they store and retrieve data efficiently from disk (data pages) like document DB, key value stores, graph DB , time series DB and each of their uses cases ?
Another absolute masterpiece Hussain, loved it! Would love if you could make a follow up video showing WALs in action in a distributed DB (replica sets, etc).
I have a question. Let's say WAL is almost full and we are trying to flush the data from page to disk, but now the flushing is not successful as it crashes but simultaneously more logs are getting written in WAL, and once WAL will reach to it's maximum limit and still the first flush is not successful to disk does this means that the old logs from the WAL will be removed to have the new logs and eventually we will loose the changes does with that old change?
What happens if it crashes before transaction is committed, will the changes written to WAL be reverted. does wal has some kind of dirty flag for uncommitted changes? Also does each table has a separate wal file, or one wal per database?
That is where you have the the undo segments. So every time you start a transaction a undo segment is assigned to the Txn and it has the snapshot that guarantees read consistency. This is called MVCC in databases. This is how read statements get the correct fat when your transactions have an intent to write. Also as the transaction progresses and you make data changes (dmls) a time snapshot (SCN or CSN) is also maintained in undo and also at times this is written to redo ahead of time about every 3 seconds or other scenarios. Basically to put all these together redo or wal and undo are used for crash recovery. When instance starts it reads the redo and undo to see which statements were commuted but not written to disk and write them to disk. Which are the statements that are not committed that are in redo and sometimes even on the disk. You undo those changes and update the checkpoint. You can go deep. Oracle does this best. That is the reason oracle is what oracle is! When isolation, consistency and durability is the concern there is a reason banking systems and critical systems use oracle or mssql databases and pay for them rather than open source ones.
why we need to Apply Undo coz WAL is source of truth and if we apply WAL we are sure we have applied commited Trxns we need to apply Trxs which were not committed
