DB transactions and write to disk?

Question

MySQL

A quote about one of the InnoDB settings:
innodb_flush_log_at_trx_commit - has three valid values: 0, 1, 2. With a value of 0, the log is flushed to disk once per second, regardless of ongoing transactions. If set to 1, the log is flushed to disk on every transaction. With a value of 2, the log is written with each transaction, but is never flushed to disk, leaving it to the conscience of the OS. The default is 1, which is the most reliable setting, but not the fastest. In general, you can safely use 2, data can be lost only in the event of an OS crash and only in a few seconds (depending on OS settings). 0 is the fastest mode, but data can be lost both when the OS crashes and when the MySQL server itself crashes (however, data is only for 1-2 seconds).
Question: Does this mean that with values ​​of 0 and 2 in the event of a crash, I will lose the transaction, or even with these settings, it will roll back if it crashes? And i.e. if I want guaranteed true transactions, then I need to set only 1?

Answer 1

if I want guaranteed true transactions, then I need to set only 1?

Yes.
And we should take into account that this is only what the DBMS can affect - i.e. call fsync. The underlying OS and hardware may not actually write data when responding to fsync. Usually fsync is honest, but it's worth checking either in the documentation (for a raid controller, for example) or with Google or synthetics (the results of benchmarks on different innodb_flush_log_at_trx_commit should differ significantly)
Values ​​0 and 2 - data on the transaction commit in WAL may not have time to write to disk and when the database is restored, these transactions will be canceled. After all, if there is no data about the commit in WAL, how can the DBMS understand whether the transaction was committed or the accident happened even before the commit and the transaction was aborted?