This is the 7th time THAT I participated in the August Text Challenge.More challenges in August

Because recently encountered Spring transaction management related problems, so summarize and record the problem

What is a transaction

If a service is complete, multiple SQL statements need to be executed. It involves many increase, delete, change and check. A transaction, in short, is the execution of all of these SQL statements as a whole that either succeeds or fails. If the execution fails, you need to roll back to the state before the SQL execution.

Four major features of transactions

Atomicity, consistency, isolation, persistence.

3 Application scenarios of transactions

1 bank transfer business simulation

The rainbow cat transfers 1000 yuan to the blue rabbit, and this business, at least two statements need to be executed, that is, the rainbow cat deducts 1000 yuan statement, the blue rabbit increases 1000 yuan statement.

-- Create account table :id,name,balance
create table account(
	id int primary key auto_increment,  -- The primary key grows automatically
	name varchar(20),  - the account name
	balance int   -- The balance of the account
);  
 
-- Add data: Rainbow cat and blue rabbit 1000 each
insert into account values(null.'hm'.2000), (null.'lt'.2000);

select * from account;

-- Simulated function of rainbow cat transferring 1000 yuan to blue rabbit:
-- At least two SQL statements must be executed
-- 1. Update the Rainbow cat account, and the rainbow cat is reduced by 1000
-- 2. Update blue rabbit account, blue rabbit add 1000
Implement article 1
update account set balance=balance- 1000. where name='hm';
Statement 1 May be executed successfully, but statement 2 May not be executed, or the execution may fail
update account set balance=balance+1000 where name='lt';

-- Query the result after the transfer
select * from account;

-- Restore the money of 2 accounts to 1000
update account set balance = 2000;

Copy the code

If the statement that deducted $1000 from the rainbow cat is executed, some other unexpected problem occurs and the server crashes. The statement adding $1000 to the blue rabbit is not executed, and the data is in trouble, which is unacceptable for banking institutions.

So, we want to make sure that if one statement goes wrong, the whole transfer fails. Data was restored to the state before the statement was executed. The transfer succeeds if only two statements succeed. This is how transactions are used.

Commit transaction

Transactions are committed manually and automatically.

By default, each DML statement (add, delete, change) in Mysql tasks is a single transaction, so the above transfer is 2 transactions executed.

1 Manual Submission

Manually commit transactions, that is, manually start, close, and roll back transactions.

1 Perform related commands

2 Manually commit transactions

1 Starting a Transaction

2 execute multiple SQL statements related to the business (at this time, the data is not written to the database table, all operations are completed in a log file)

3 If all SQL statements are normal, commit the transaction

4 If a failed statement exists, the transaction is rolled back

3 case

1 commit transaction

Simulated rainbow cat to blue rabbit transfer 1000 yuan successful case column.

1 Log in to the Mysql database using the Dos console

2 Perform business processes

start transaction;

select * from account;

update account set balance=balance- 1000. where name='hm';

update account set balance=balance+1000 where name='lt';

select * from account;

commit;
Copy the code

-1 Start transaction - 2 Rainbow cat account -1000-3 Blue Rabbit account +1000-4 Use Navicat to view database, data unchanged - 5 Commit transaction on the console - 6 Use Navicat to view database, data changedCopy the code

Before navICAT transaction commits:

After the NavICAT transaction commits:

2 Rollback the transaction

Failed case of simulated rainbow cat transferring 1000 yuan to blue rabbit.

1 Log in to the Mysql database using the Dos console

2 Perform business processes

start transaction;

select * from account;

update account set balance=balance- 1000. where name='hm';

update account set balance=balance+1000 where name='lt';

select * from account;

rollback;

select * from account;
Copy the code

• 1 Starting a Transaction
• 2 Rainbow cat account -1000
• 3 Blue Rabbit account +1000
• 4 Use Navicat to view the database. The data remains unchanged.
• 5 Roll back the transaction on the console
• 6 Use Navicat to view the database and change the data.

Before navICAT transaction commits:

After the NavICAT transaction commits:

2 Automatic Submission

Automatic submission off and on.

1 Default submission mode

By default, Mysql considers each DML statement (add, delete, modify) to be an independent transaction, so in the above example, Mysql considers two transactions to be executing.

The transaction is automatically started before each statement starts execution and committed after execution so that we cannot roll back.

In order to complete the relevant business, we must be able to have a rollback function. You can set the transaction commit mode of Mysql. The default is automatic submission, which we can turn off manually.

2 cases

1 Automatically commit transactions

select * from account;

update account set balance=balance- 1000. where name='hm';

select * from account;
Copy the code

• Update rainbow Cat account -1000
• 2 Use Navicat to view data

2 Disable automatic commit transactions

• 1 Disable automatic commit transactions
• Update rainbow Cat account -1000
• 3 Use Navicat to view data
• 4 Commit transaction
• 5 Use Navicat to view data
• 6 Enable automatic transaction submission

Select @@autocommit;

set @@autocommit=0;

update account set balance=balance- 1000. where name='hm';

commit;

set @@autocommit=1;
Copy the code

Before navICAT transaction commits:

After the NavICAT transaction commits:

Rollback of transaction

Overview of transaction rollback

Transaction rollback: Rollback to the initial state before the transaction was executed. If you do not want to roll back to the original state, you can roll back to a step in the entire process.

Save the rollback point ahead of time and roll back to the saved location.

2 Rollback case

1 Case Procedure

• 1 Starting a Transaction
• 2. Reduce 500 yuan from the account of Rainbow Cat
• 3 Set the rollback point
• 4. Reduce 500 yuan from the account of Rainbow Cat
• 5 Go to the rollback point
• 6 Commit transaction

start transaction;

select * from account;

update account set balance=balance- 500. where name='hm';

savepoint back_point_one; 

update account set balance=balance- 500. where name='hm';

rollback to back_point_one;

commit;

select * from account;
Copy the code

Before navICAT transaction commits:

After the NavICAT transaction commits: