Explain the sequence an UPDATE query goes through from memory to disk. And considering a crash at every stage how 'durability' is maintained.
- Double write buffer
- ib_logfile0, ib_logfile1 (redolog)
- ibdata (Single tablespace or multiple tablespace,innodb_file_per_table=1)
- binary log (WAL)
- general query log (assuming enabled)
- First data will be written in buffer pool which contains the page cache of tables that are used recently.
- If it is a READ request it is done in foreground like first it checks if the result is available in buffer pool , otherwise it hits the disk
- If it is a WRITE request, the change will be written in buffer pool of table.ibd file and then to ib_logfiles .
- After innodb_flush_log_at_trx_commit the changes are flushed to disk (table.ibd).
At what stage it will be written in binary log (contains both committed and uncommitted transaction) or general log (contains everything including SELECT and unsuccessful queries).
Note1: Not interested in Optimizer, Parser, Other Storage Engines
Note2: Kindly do not mark this as too broad, A short precise answer covering the 7 components mentioned will be enough