I know the transaction can ensure that all the queries are committed or rolled back, but what is the point at which we can say ok, the transaction has been committed and all changes are durable? I think it is when commit flag is written into redo log (take mysql as an example), is it right?
1 Answer
For all intense and purposes, the transaction is commit - then commit statement is complete. At that point all changes are durable and will survive reboot even if the data is technically still in "dirty pages" and not in the table's pages.
When dirty pages become white? That depends on the RDBMS and each one has a lengthy discussion in documentation about this. For MySQL the rules for this are described here: https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-flushing.html
-
Good answer, but it applies to a single (non-replicated) server. Old replication is async. Semi-sync is well,... Galera involves the gcache. Etc. In those situations, then "maybe". Commented Dec 27, 2023 at 23:31
-
But there is another problem: when can we say commit statement is complete from mysql server's view? Notice: I know it is hard from client's view, but i think it is a determinate action for server, after executing a certain c++ statement for example. Commented Dec 28, 2023 at 2:03