Under the default command line settings of MySQL, transactions are automatically committed, which means that the commit operation will be executed immediately after the SQL is executed alone.
If we want to cancel automatic submission, we can do it in two ways:
1) . turn off auto submit settings
In MySQL, you can use the SET autocommit statement to set the automatic submission mode of transactions. The syntax format is as follows:

SET autocommit = 0|1|ON|OFF;

Description of value:
Values of 0 and OFF: turn OFF automatic transaction commit. If Auto commit is turned OFF, the user will always be in a transaction. The current transaction will be ended and a new transaction will be restarted only after commit or rollback.
Values of 1 and ON: enable automatic transaction submission. If Auto commit is enabled, the transaction will be committed once for each SQL statement executed.

2) . show start a transaction

start transaction;

1. Grammar that must be mastered when writing transactions

Through the previous study, we know how to change MySQL automatic submission to manual submission.

1) Let's formally learn important grammar

START TRANSACTION Show open a transaction
COMMIT Commit transaction
ROLLBACK Rollback the previously executed transaction, which is the same as COMMIT Cannot be executed at the same time
SAVEPOINT point Create savepoint
RELEASE SAVEPOINT point Delete savepoint
ROLLBACK TO point Roll back to the savepoint location. Note: even if this is executed, it must be executed ROLLBACK or COMMIT To end the transaction
SET TRANSACTION Set the isolation level of the transaction
COMMIT WORK Commit the transaction, but it and COMMIT There is a difference, COMMIT WORK Used to control the behavior after the transaction ends
 yes CHAIN still RELEASE Yes, if so CHAIN The transaction becomes a chain transaction. The user can pass the parameters completion_type To control,
By default, this parameter is 0, which means there is no operation. Under this setting, COMMIT and COMMIT WORK Is completely equivalent.
When parameter completion_type When the value of is 1, COMMIT WORK Equivalent to COMMIT AND CHAIN,Indicates that a transaction with the same isolation level is automatically started immediately.
When parameter completion_type At 2, COMMIT WORK Equivalent to COMMIT AND RELEASE,When the transaction is committed, it will automatically disconnect from the server.

Note: transactions in the InnoDB storage engine are atomic, which indicates the following two situations: each statement constituting the transaction will be committed (become permanent), or all statements will be rolled back. This protection also extends to a single statement. A statement is either fully committed or fully rolled back (statement rollback). Therefore, if a statement fails and throws an exception, it will not cause the previously executed statement to be rolled back automatically. Therefore, it is up to the user to decide whether to commit or rollback it.

2) Case study

First, create a table from: and insert two pieces of data

create table bank(
internal_key int(11) auto_increment,
uname varchar(20) ,
baseAcctId varchar(50),
balance decimal(17,2),
primary key(internal_key)
);
insert into bank(uname,baseAcctId,balance) values('yanjd','88888888',100000000);
insert into bank(uname,baseAcctId,balance) values('yanjl','99999999',0);

Then we use transactions to perform the following operations:
A.yanjd transfers 100 yuan to yanjl (submitted using)

START TRANSACTION;
update bank set balance=balance-100 where baseAcctId='88888888';
update bank set balance=balance+100 where baseAcctId='99999999';
COMMIT;

Results before and after implementation:

B. But if we execute the following statement (using rollback):

START TRANSACTION;
update bank set balance=balance-100 where baseAcctId='88888888';
update bank set balance=balance+100 where baseAcctId='99999999';
ROLLBACK;

Before execution:

After execution:

C. Let's execute the following statement (using savepoints)

START TRANSACTION;
update bank set balance=balance-100 where baseAcctId='88888888';
SAVEPOINT time1;
update bank set balance=balance+100 where baseAcctId='99999999';
ROLLBACK TO time1;

Then perform a separate commit
You will find: