There is a sql like that
UPDATE xxx SET num = num -1 WHERE num > 0;
Will this be an atomic operation even if I have no transaction statement?
Can this sql ensure the num field always be non-negative?
In SQL databases transaction atomicity is implemented most frequently using write-ahead logging (meaning that the transaction log entries are written before the actual tables and indexes are updated).
MySQL 8.0 supports atomic Data Definition Language (DDL) statements. This feature is referred to as atomic DDL. An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic operation.
A transaction is an atomic set of database queries.
MyISAM is atomic, however not necessarily consistent, durable or isolated. See my answer. – Johannes H. MyISAM is not atomic, as the term generally applies to database systems.
If autocommit is enabled, yes it will be executed atomically. Every statement will be a single transaction unless autocommit is disabled.
It should be noted that autocommit is enabled by default, hence why START TRANSACTION is usually necessary to initiate a transaction.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With