This is the 24th day of my participation in Gwen Challenge

Search “Program Yuan Xiaozhuang” on wechat, and you will get a full set of Python full stack tutorials, as well as the constantly updated e-books and interview materials prepared by Xiaozhuang

preface

I don’t know if you’ve ever thought about it, but when you’re transferring money to a bank, if the ATM system crashes (ultra-low probability…) , the money has been transferred, this time will not appear the other party did not receive the transfer but my money did not have the situation? Money is a sensitive topic, as long as it involves money, it will not be so hasty. Since it will not be lost, how to ensure the consistency of data in the case of system crash or network disconnection? That’s what we’re talking about today.

Introduction of the transaction

Let’s start with what a transaction is. A transaction is a working mechanism that accompanies a transactional business scenario. In simple terms, a transaction can contain multiple SQL statements that either execute successfully at the same time or fail at all.

Transactions ensure data security and prevent the failure of several data operations when multiple data operations are performed.

Characteristics of transactions

To ensure data security, transactions have the following properties, called ACID:

Automic: Atomicity, in a transaction unit (from transaction start to transaction commit), all standard transaction statements (DML) either succeed or roll back (to the state before transaction commit);

Consistency: data Consistency before, during, and after a transaction;

Isolatin: Isolation, MySQL supports multi-transaction concurrent work, one transaction can not be affected by other transactions;

Durability, when a transaction commits successfully, all data for that transaction operation needs to be kept forever.

Transaction life cycle management

The standard transaction statement is the DML database operation language (DML), including the INSERT UPDATE DELETE statement.

-- Start transaction
begin/start transaction;

DML operations on the database
insert...
update...
delete...

-- Transaction commit
commit;

-- Transaction rollback;
rollback;
Copy the code

For example, simulate the operation of transfer in MySQL.

create table user(
	id int primary key auto_increment,
	name char(16),
	balance int
);

insert into user(name,balance) values('python'.100), ('java'.100);

-- Start transactions first
start transaction;
-- Multiple SQL statements for writing transactions
update user set balance=900 where name='python';
update user set balance=1010 where name='java';
- the rollback
rollback;
-- Confirm transaction
commit;
Copy the code

The DML statement is automatically preceded by begin when it is executed, and the DML statement is automatically preceded by COMMIT when it ends. However, it is not safe to commit transactions automatically in transaction scenarios. You can modify the MySQL parameter to set the DML statement to not commit transactions automatically.

-- If autoCOMMIT =1 does not begin with begin, DML statements will automatically start with begin, and DML statements will end with COMMIT.
select @@autocommit;

Temporary: DML statements are not submitted automatically
set global autocommit=0;

-- It takes effect permanently. You need to modify the mysql configuration file and restart the mysql service
vim /etc/my.conf
[mysqld]
autocommit = 0
Copy the code

Implicit rollback is triggered when a transaction is closed abnormally, such as when the database is down or the transaction statement fails to execute.

Transaction isolation level

Mysql supports multi-transaction concurrency, which requires that each transaction is isolated between each other. The execution of a transaction cannot be affected by other transactions. The isolation of transactions can be divided into read uncommitted, read committed, unrepeatable read and serialization. You can view the default isolation level and modify the isolation level by using the following SQL statement:

select @@transaction_isolation; -- View the default isolation level
set global transaction_isolation='read_uncommited';  Parameter modification, which is temporary and only valid for the current session
Copy the code

Before going into the details of isolation levels, a few concepts need to be understood:

Dirty read: Another transaction reads uncommitted data that the current transaction is modifying, violating the isolation of the transaction. Dirty reads are generally not allowed in production services.

Phantom read: Data inserted by another transaction in a transaction window when the update operation is not completed or committed.

Unrepeatable read: the same operation is performed at different times in the same transaction unit to read data modified by other transactions. Simply speaking, the same transaction unit reads different data at different times. Businesses that require high transaction isolation and data consistency are not allowed.

With these concepts in mind, let’s discuss the isolation level of a transaction. There are four levels of isolation for a transaction:

RU(read uncommited) Read uncommitted: Problems: dirty reads, unrepeatable reads, phantom reads

RC(read commited) Read committed: Error: Unrepeatable read, phantom read

Repeatable read (RR), default level: Problem: Phantom read may occur, but phantom read can be prevented by other mechanisms. With RR, 99 percent of phantom readings can already be solved.

SR(serializable….) Serializable: Problems that occur: All of the above problems can be avoided, but are detrimental to the concurrency of transactions

conclusion

The article was first published on the wechat public account Program Yuan Xiaozhuang, and synchronized with nuggets and Zhihu.

The code word is not easy, reprint please explain the source, pass by the little friends of the lovely little finger point like and then go (╹▽╹)