MySQL transaction details

Posted by mallen on Mon, 25 Oct 2021 10:05:50 +0200

introduction

Database transaction refers to a series of operations performed as a single logical unit of work, either complete or not at all.
Under normal circumstances, these operations will proceed smoothly and finally succeed. However, if there is a problem in any link in this series of processes, all information in the database must remain the same as before the first operation. Otherwise, the information in the database will be chaotic and unpredictable.

1, Concept of transaction

MySQL transactions are mainly used to process data with large amount of operations and high complexity. For example, in the personnel management system, to delete a person, you need to delete both the basic data of the person and the information related to the person, such as mailbox, article, etc. In this way, these database operation statements constitute a transaction.

  • Transaction is a mechanism and an operation sequence, which contains a set of database operation commands, and all commands are submitted or revoked to the system as a whole, that is, this set of database commands are either executed or not executed.

  • Transaction is an inseparable work logic unit. When performing concurrent operations on the database system, transaction is the smallest control unit.

  • Transactions are applicable to the scenario of database systems operated by multiple users at the same time, such as banks, insurance companies and securities trading systems.

  • Transaction is to ensure data consistency through transaction integrity.

2, ACID characteristics of transactions

ACID refers to the four characteristics that a transaction should have in a reliable database management system (DBMS):
Atomicity
Consistency
Isolation
Durability
These are several characteristics of a reliable database.

1. Atomicity

A transaction is an inseparable work unit. Operations in a transaction either occur or do not occur.
A transaction is a complete operation, and the elements of a transaction are inseparable.
All elements in a transaction must be committed or rolled back as a whole. If any element in the transaction fails, the entire transaction fails.

case:
A to B When transferring 100 yuan, only the deduction statement is executed and submitted. At this time, if there is a sudden power failure, A The account has been deducted, B The account did not receive the increase,
Disputes will arise in life. In this case, transaction atomicity is required to ensure that transactions are either executed or not executed.

2. Consistency

It means that the integrity constraint of the database is not destroyed before the transaction starts and after the transaction ends.
When the transaction completes, the data must be in a consistent state. Before the transaction starts, the data stored in the database is in a consistent state.
In an ongoing transaction, data may be in an inconsistent state.
When the transaction completes successfully, the data must return to the known consistent state again.

case:
For bank transfer transactions, no matter whether the transaction is successful or failed, it should be guaranteed that after the transaction is completed, it will be in the table A and B The total amount of deposits is the same as before the transaction is executed.

3. Isolation

In a concurrent environment, when different transactions manipulate the same data at the same time, each transaction has its own complete data space.

All concurrent transactions that modify data are isolated from each other, indicating that the transaction must be independent and should not depend on or affect other transactions in any way.
A transaction that modifies data can access the data before another transaction that uses the same data starts, or after another transaction that uses the same data ends.

The execution of a transaction cannot be disturbed by other transactions. The interaction between transactions can be divided into several types, namely:

  • ① Dirty reading (reading uncommitted data): dirty reading refers to reading uncommitted data of other transactions. Uncommitted means that these data may be rolled back, that is, they may not be saved in the database, that is, nonexistent data. The data that is read and must eventually exist is dirty reading
Case list:
Such as transactions B Data was modified during execution X,Before submission,affair A Read it X,And affairs B But rolled back,Such transaction A Dirty reading is formed. 
in other words,The data read by the current transaction is the data that other transactions want to modify but failed to modify.
  • ② Non repeatable reading (read several times before and after, and the data content is inconsistent): two identical queries in a transaction return different data. This is caused by the submission of other transaction modifications in the system during query.
Case list:
affair A The first query gets a row of records row1，affair B After submitting the modification, the transaction will be deleted A Obtained by the second query row1，But the column contents have changed.
select * from member;
1 zhangsan  20 branch
select * from Member;
1 zhangsan  30 branch
  • ③ Phantom reading (multiple times before and after reading, the total amount of data is inconsistent): a transaction modifies the data in a table, which involves all data rows in the table. At the same time, another transaction also modifies the data in the table. This modification is to insert a new row of data into the table. Then, the user who operates the previous transaction will find that there are still unmodified data rows in the table, as if there was an illusion.
Case list:
Hypothetical transaction A Changes have been made to the contents of some rows, but the transaction has not been committed yet B Inserted transaction with A The same record line as the record before the change, and in the transaction A Submitted before submitting,
At this time, in business A In the query, you will find that it seems that the change just made has no effect on some data, but it is actually a transaction B Just inserted, it makes users feel very magical. They feel that there is an illusion. This is called phantom reading

select * from member；
Six records were queried

alter table member change

select * from member;
10 records were queried (6 data were updated and 4 data were not updated)
  • ④ Lost update: two transactions read the same record at the same time. A modifies the record first, and B also modifies the record (B does not know that a has modified it). After B submits the data, B's modification result overwrites a's modification result.
Case list:
A  30 ->40 Transaction completed first
B  30 ->50 Post transaction completion
B The result of the transaction will be overwritten A The final value is 50

4. Persistence

After the transaction is completed, the changes made by the transaction to the database will be permanently saved in the database and will not be rolled back.
Whether the system fails or not, the result of transaction processing is permanent.
Once the transaction is committed, the effect of the transaction will be permanently retained in the database.

3, Mysql and transaction isolation level

1. Level

  • read uncommitted
    Read uncommitted data: dirty reads are not resolved
    Dirty reading is allowed. As long as other transactions modify the data, even if it is not committed, this transaction can see the modified data value.
    That is, the number of uncommitted transaction modifications in other sessions may be read.

  • read committed
    Reading submitted data: dirty reading can be solved
    Only submitted data can be read.
    Most databases such as Oracle are at this level by default (no repeated reading).

  • repeatable read
    Reread read: it can solve dirty read and non repeatable read; mysql is repeatable by default.
    No matter whether other transactions modify and commit data, the data values seen in this transaction are always unaffected by other transactions

  • serializable: serialization
    It can solve dirty reading, unrepeatable reading and unreal reading; Equivalent to locking table
    For fully serialized reads, table level shared locks need to be obtained for each read, and reads and writes will block each other.

The default transaction level of mysql is repeatable read, while Oracle and SQL Server are read committed.

2. Scope of action

The scope of transaction isolation level is divided into two types
① Global level: valid for all sessions
② Session level: valid only for the current session

  • Query global transaction isolation level
show global variables like '%isolation%';
SELECT @@global.tx_isolation;
  • Query session transaction isolation level
show session variables like '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;
  • Set global transaction isolation level
set global transaction isolation level read committed;
  • Set session transaction isolation level
set session transaction isolation level read committed;

4, Transaction control statement

BEGIN or START TRANSACTION: Open a transaction explicitly.

COMMIT or COMMIT WORK: Commit the transaction and make all changes made to the database permanent.

ROLLBACK or ROLLBACK WORK: Rollback ends the user's transaction and undoes any uncommitted changes in progress.

SAVEPOINT S1: use SAVEPOINT It is allowed to create a rollback point in a transaction. There can be multiple rollback points in a transaction SAVEPOINT；"S1"Represents the name of the rollback point.

ROLLBACK TO [SAVEPOINT] S1: Roll back the transaction to the marker point.

Case:
create database SCHOOL;
use SCHOOL;
create table info(  
id int(10) primary key not null,  
name varchar(40),  
money double  
);

insert into info values(1,'A',1000);  
insert into info values(2,'B',1000); 
select * from info;

1. Test commit transactions

begin;
update info set money= money - 100 where name='A';
select * from info;

commit;
quit

mysql -u root -p
use SCHOOL;
select * from info;

2. Test rollback transaction

begin;
update info set money= money + 100 where name='A';
select * from info;

rollback;
quit
mysql -u root -p
use SCHOOL;
select * from info;

3. Test multipoint rollback

begin;
update info set money= money + 100 where name='A';
select * from info;
SAVEPOINT S1;
update info set money= money + 100 where name='B';
select * from info;
SAVEPOINT S2;
insert into info values(3,'C',1000);

select * from info;
ROLLBACK TO S1;
select * from info;

4. Use set to set control transactions

SET AUTOCOMMIT=0; —— Prohibit automatic submission of set
AUTOCOMMIT=1; —— Enable automatic submission. Mysql defaults to 1 SHOW VARIABLES LIKE
‘AUTOCOMMIT’; —— View the autocommit value in Mysql

If Auto commit is not enabled, all mysql operations connected to the current session will be treated as a transaction until you enter rollback commit; The current transaction is over.
Before the end of the current transaction, the operation results of any current session cannot be read when a new mysql connection is made.
If Auto commit is enabled, mysql will treat each sql statement as a transaction, and then automatically commit. Of course, whether it is turned on or not, begin;
commit|rollback; Are independent affairs.

use SCHOOL;
select * from info;
SET AUTOCOMMIT=0;
SHOW VARIABLES LIKE 'AUTOCOMMIT';
update info set money= money + 100 where name='B';
select * from info;
quit

mysql -u root -p
use SCHOOL;
select * from info;

summary

  • ACID characteristics of transaction:
    Atomicity
    uniformity
    Isolation - dirty read, non repeatable read, unreal read, lost update
    persistence
  • MYSQL and transaction isolation level:
    read uncommitted uncommitted
    read committed
    Repeatable read repeatable read
    serialization

Topics: Java Database MySQL

Hot Topics

©2024 Programmer Think