@Vérace If the DBMS is to guarantee atomicity (the A of ACID) it has to have statement-level transactions. Imagine an update that touches 1Bn rows. The last-but-one row throws an exception and now all 1B rows have to rollback. Without a transaction to track what was updated and what the restore-to state is, it cannot complete nor can it rollback; no atomicity.
Having automatically started the transaction, when should the DBMS finish it? To me, the least surprising choice would be at the end of the statement. Sometimes this is not so, however. (Looking at you, SQL Server.)
Having done an unscientific Google search it seems SQL Server, PostgreSQL, Snowflake and Oracle default to read committed. For MySQL InnoDB / MariaDB it is repeatable read. I learnt last week to my surprise that Azure Synapse defaults to read UNcommitted, and Clickhouse does not support transactions at all!
@MichaelGreen Without a transaction to track what was updated and what the restore-to state is, it cannot complete nor can it rollback; no atomicity. - I totally get this - is there any part of what I wrote that conflicts with this?
Interestingly Firebird defaults to SNAPSHOT and appears (to my mind) to have truly SERIALIZABLE transactions in their top level - called SNAPSHOT TABLE STABILITY - see here.
It's really interesting to see that Firebird (a **deadly** and truly undertated db IMHO) has what appears to be the most thorough implementation of TI - the only thing it doesn't have is READ UNCOMMITTED - just like Oracle - but with an MVCC architecture it's not necessary !. MS SQL Server and MySQL do support R-U...
