Start a transaction with begin, commit it with commit, or rollback it with rollback.
In the previous article, Songo also talked about how things work and related details, so you can review them:
- MVCC water is a bit deep, but it’s really cool to understand!
- When was the consistency view created?
- MySQL transaction isolation level
Normally, when we start a transaction, we need to commit or rollback to end the transaction. However, some operations will automatically commit the transaction for us. If you are not familiar with implicit transactions, you may encounter some strange problems in the transactions that use them.
1. The DDL operations
The first is that DDL operations commit transactions implicitly, which Songo mentioned in his previous article. Let’s review again:
All DDL statements cause the transaction to commit implicitly, in other words, the transaction is committed before you execute the DDL statement. This means that transactions with DDL statements cannot be rolled back in the future.
Let me give you a simple example:
Let’s take a look at my test logic here:
- First, there are four total records.
- Start a transaction.
- Execute a delete statement.
- Alter table, add a new field.
- Rolled back.
- Query the data again.
By step 6, we found that there were only three data queried, indicating that the rollback in step 5 did not take effect. The reason is that the transaction has already been implicitly committed before ALTER is executed.
Therefore, in daily development, it is best not to mix DDL statements in transactions. DDL statements and DML statements are written separately.
In the above example, if you remove the fourth step of alter, then the rollback can be successful, this small friends to test, I will not demonstrate.
DDL operations are not only alter, but other operations such as CREATE and DROP can also cause transactions to commit implicitly.
2. The DCL operation
DCL is a Data Control Language (DCL) used to GRANT or REVOKE privileges to databases. Even DCL operations.
Let me give you a simple example:
As you can see from the test steps in section 1, with the exception of a GRANT statement in step 4, the final transaction rollback will fail because the transaction has already been committed.
Of course, in addition to GRANT and REVOKE, other operations that create, update, or delete users also cause transactions to commit implicitly. Mainly include:
- CREATE USER…
- DROP USER…
- ALTER USER…
- SET PASSWORD…
3. A new transaction is started
If you start a new transaction before a transaction is committed, the previous transaction will commit implicitly. Here’s an example:
This is easy to understand, not to say.
4. Various lock operations
Locking and unlocking tables also causes transactions to commit implicitly. As follows:
Lock the lock tables such as SQL table_name read | write, will cause the transaction implicit commit, unlock the SQL as unlock tables can also lead to transactions are implicit commit.
In addition to table locks, some global locks such as FTWRL can also result in implicit commits of transactions, as follows:
5. Slave machine operation
MySQL master/slave:
- MySQL8 Master/slave replication stomp guide
Some of the operations we perform on the slave such as start slave, Stop slave, reset slave, and change master to also commit transactions implicitly.
6. Perform other table operations
Other operations such as Flush Privileges, optimize Table, repair Table, etc. also result in implicit commits of transactions.
LOAD DATA implicitly commits transactions, but Songo does not commit transactions.
LOAD DATA does not seem to cause transactions to commit implicitly.
Best practices
How am I supposed to remember all those implicit submissions? In fact need not back, as long as you remember in the transaction only add and DELETE (INSERT/DELETE/UPDATE/SELECT), would not be wrong!