1, innodb core feature transaction
1. What is a transaction
# What is a transaction Opening a transaction can contain multiple transactions sql Statement, so sql Statements either succeed or fail at the same time, which becomes the atomicity of the transaction # effect It ensures the security of data operation When you operate multiple pieces of data, some operations may fail A set of data operation execution steps, which are regarded as a unit of work: 1)Used to group multiple statements 2)It can be used when multiple clients access data in the same table concurrently All steps succeed or fail 1)If all steps are OK, perform the following steps 2)If the step is incorrect or incomplete, cancel
2. How to use transactions
# Transaction related keywords # 1. Open transaction start transaction # 2. Rollback (return to the state before the transaction is executed) rollback # 3. Confirm (cannot roll back after confirmation) commit # Summary: When you want to sql Statements ensure the consistency of data at the same time, either succeed or fail at the same time, so you can consider using transactions
3. Transaction presentation
#1. Create a table mysql> create table jiaoyi(id int primary key auto_increment,name varchar(10),money int); #2. Insert two pieces of data mysql> insert into jiaoyi values(1,'qiudao',100); Query OK, 1 row affected (0.01 sec) mysql> insert into jiaoyi values(2,'oldboy',200); Query OK, 1 row affected (0.00 sec) #3. Open another window mysql> select * from jiaoyi; The data is consistent with the viewing results in the original window #4. Start a transaction (logical) mysql> start transaction； #Open transaction # begin is also an open transaction mysql> begin; mysql> update jiaoyi set money=0 where name='qiudao'; mysql> select * from jiaoyi; mysql> update jiaoyi set money=400 where name='oldboy'; mysql> select * from jiaoyi; #Another window cannot see the data change before the commit is executed mysql> commit; #After the commit is executed, everyone else can see the data changes #5. Start a transaction (illogical) mysql> start transaction； mysql> update jiaoyi set money=-100 where name='qiudao'; mysql> select * from jiaoyi; +------+--------+-------+ | id | name | money | +------+--------+-------+ | 1 | qiudao | -100 | | 2 | oldboy | 400 | +------+--------+-------+ 2 rows in set (0.00 sec) # 6. Since the value of money cannot be negative, the operation failed and rolled back according to logical judgment mysql> rollback; #Rollback transaction mysql> select * from jiaoyi; +------+--------+-------+ | id | name | money | +------+--------+-------+ | 1 | qiudao | 0 | | 2 | oldboy | 400 | +------+--------+-------+ 2 rows in set (0.00 sec)
4. Popular understanding of affairs
#The concept of database with the emergence of "transaction". What do we understand as a "deal"? 1)Exchange of things (ancient times) 2)Exchange of money, cash and physical goods (Modern 1) 3)Exchange of virtual currency and physical goods (Modern 2) 4)Virtual currency and virtual physical exchange (modern 3) What is the "transaction" in the database? 1)How to ensure the "harmony" of "transaction"? 2)ACID
5. The life cycle of a successful transaction
start transaction； #begin start transaction sql1 #In real transaction processing, the transaction is started only after the first sql statement is executed sql2 sql3 ... commit; # If you use begin to manually start editing transactions, you can query the data at the other end as long as you commit manually after editing
6. The life cycle of a failed transaction
start transaction； sql1 sql2 sql3 ... rollback; # If you use begin to manually start editing transactions, after editing and as long as you do not commit manually, the other end will not be able to query data
7. Attribute ACID of the transaction
ACID Four characteristics #ACID # A: Atomicity A transaction is an inseparable unit. Many operations contained in a transaction either fail or succeed at the same time All statements are successfully executed or cancelled as a unit. # C: Consistency Transactions must change from a database consistency state to another consistency state. Consistency is closely related to root atomicity, If the database is in a consistent state at the beginning of a transaction, it will remain in a consistent state during the execution of the transaction. # 1: I solated The execution of one transaction cannot be disturbed by other transactions,Transactions do not interact. # D: Persistent It can also be called permanence. Once a transaction is committed and executed successfully, it means that the data modification in the database is permanent Other subsequent operations or faults should not have any impact on it After the transaction completes successfully, all changes are accurately recorded in the database. Your changes are not lost. We can analyze that among the four characteristics of transactions, all operations will go to disk, so persistence is the purpose of transaction operations, atomicity is the basis of transactions, isolation is a strategy and means to achieve data security, and the final maintenance is data consistency, which is the most important in transactions. Among the four features, isolation is a means to achieve consistency. ACID Among the four features, the most difficult to understand is not consistency, but transaction isolation. Authoritative database experts have studied four levels of transaction isolation for transaction isolation. The four transaction isolation levels are to solve the problems caused by high concurrency of data (dirty reading, non repeatable reading and unreal reading).
8. Transaction control statement
1) Auto submit
# 0. Submit manually mysql> commit； #1. View automatic submission mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.01 sec) #2. Temporary closure set autocommit =0; #3. Permanent closure [root@db01 ~]# vim /etc/my.cnf [mysqld] autocommit=0
2) Implicit commit of transactions
1)In the current version, you do not need to manually open a transaction start transaction；，As long as you enter DML Statement, the transaction will be opened automatically. 2)In some cases, transactions are implicitly committed # Implicit commit trigger 1.The transaction was not executed commit If used DDL perhaps DCL The previous transaction is automatically committed 2.The transaction was not executed commit If you execute it manually begin，The previous transaction is automatically committed 3.The transaction was not executed commit If the lock table is executed( lock tables)Or unlock( unlock tables)，The previous transaction is automatically committed 4.load data infile(Import data) will automatically commit the previous transaction 5.select for update 6.stay autocommit=1 The previous transaction will be submitted automatically when update .... commit; insert into .... begin; update .... create table .... delete ....
3) Control statement
begin(or start transaction): Explicitly start a new transaction, recommended begin #Open transaction savepoint: Assign a location in the transaction process for future reference #Temporary Archive commit: Permanently record changes made by the current transaction #Commit transaction rollback: Cancel changes made by the current transaction #RollBACK roolback to savepoint: Cancel in savepoint Subsequent changes #Back to archive point release savepoint: delete savepoint identifier #Delete temporary Archive set autocommit: Disables or enables the default for the current connection autocommit pattern #Automatic submission of temporary switch PS: Permanently open or close autocommit，In the configuration file( my.cnf)Insert a row: autocommit=1 # On state autocommit=0 # Off state
2, Isolation level of transaction
Problems caused by transaction concurrency The isolation of transactions was mentioned earlier. If you want to improve the throughput of the system, when there are multiple tasks to be processed, multiple transactions should be executed at the same time, which is transaction concurrency. Since transactions are executed concurrently, conflicts will inevitably occur during the same data operation. Let's see what problems will occur. # 1. Update lost Lost Update，When two transactions update the same row of data, both parties do not know the existence of the other party, and it is possible to overwrite the modification of the other party. For example, if two people edit a document at the same time, the last person who changes it will always overwrite the previous person's changes. # 2. Dirty reading -- cause -- caused by low security level Dirty Reads，When a transaction modifies a piece of data during execution, another transaction just reads the data and performs other operations based on the data. Because the previous transaction has not been committed, if it is further processed based on the modified data, irreparable losses will be incurred. # 3. Non repeatable reading Non-Repeatable Reads，Similarly, two transactions are operating on the same data. If a data is read at the beginning of the transaction, another transaction modifies the data. When the transaction reads the data again, it is found that it has changed. That is, it is impossible to read a piece of data repeatedly. # 4. Unreal reading Phantom Read，As in the above scenario, at the beginning of a transaction, no data was found according to a certain query condition. As a result, due to the influence of another transaction, the data was found when the transaction was checked again. This is like an illusion, which is called Unreal reading. # 1. When the security isolation level of the transaction is set to be low, the corresponding security level is low and the processing efficiency is relatively high # 2. When the transaction security isolation level is set to be high, the corresponding security level is high and the processing efficiency is relatively low
1. Four isolation levels
Firstly, the problem of update loss should be solved by the application layer, because the database has no way to control users not to update a piece of data. But the other three problems can be solved. Since there is a solution, why set so many isolation levels? Just now, if we want to improve performance and throughput, we have to pay some price. If we want to achieve no side effects at all, we just need to queue transactions for execution. One by one, there will never be the problem of dirty reading and unreal reading, but this will lead to very slow database processing. Then what shall I do? The only thing the official can do is to provide you with various levels of processing methods, which you can choose according to specific business scenarios, so there is an isolation level. # 1. Read uncommitted Reading uncommitted is actually that a transaction can be read without committing. Obviously, this isolation level will lead to reading other uncommitted data. Once further processing is made based on the read data and another transaction finally rolls back the operation, the data will be disordered and difficult to track. In general, reading uncommitted levels can lead to dirty reads. # 2. Read committed As the name suggests, it can only be read after the transaction is submitted. Suppose you take the bank card to consume. Before you pay, you see that the card has 2000 yuan. At this time, your wife is shopping on Taobao and completes the payment ahead of you. At this time, when you pay again, you will be prompted that the balance is insufficient, but it is clear that you see that the money in the card is enough. These are two transactions. When two transactions are executed, the transaction A At the beginning of reading the card, there were 2000 yuan. At this time, the transaction B Run out of Cary's money, business A When I finally reconfirmed the balance, I found that there was no money in the card. Obviously, read submission can solve the problem of dirty reading, but it can't solve the problem of non repeatable reading. Sql Server，Oracle The default isolation level for is Read committed. # 3. Repeatable read It can be seen from the name. Its emergence is to solve the problem of non repeatable reading, transaction A Once execution begins, regardless of the transaction B How to change data, transaction A What you always read is the value it just started reading. Then the problem comes, suppose the transaction B hold id The data of 1 is changed to 2, transaction A I don't know id Changes occur when transactions A When adding data, it is found to be 2 id It already exists. This is fantasy reading. MySQL The default isolation level is Repeatable read. # 4. serializable This is the most invincible existence. All transactions are executed one by one, because there is no concurrent scene. There are no unreal reads, dirty reads and unrepeatable reads. But again, the basic concurrency will be very poor. Finally, the isolation level should be selected according to your business scenario. There is no best, only the most suitable. The more strict the transaction isolation level is, the more computer performance will be consumed and the lower the efficiency will be. Generally, setting it to allow non repeatable reading can solve most problems.
The default data isolation level used by Mysql is REPEATABLE READ, which can be read repeatedly and can be read unreasonably.
2. Example demonstration of common problems of transaction concurrency
2.1 dirty reading
Dirty read: for example, two transactions operate on the same database record in parallel, A Transaction can read B Transaction uncommitted data.
1) Modify auto commit transaction switch
# Temporarily valid mysql Database transaction switch Turn on auto commit transactions: set autocommit = 1; Turn off auto commit transactions: set autocommit = 0 ; View transaction switch: show variables like '%autocommit%';
2) Modify the transaction isolation level of the database
# Global --- here we show how to set global mysql> set global transaction isolation level read uncommitted; mysql> show variables like '%iso%'; +-----------------------+------------------+ | Variable_name | Value | +-----------------------+------------------+ | transaction_isolation | READ-UNCOMMITTED | | tx_isolation | READ-UNCOMMITTED | # Exit and re-enter # Current session set session transaction isolation level read uncommitted;
3) Terminal 1 queries user specified information (no transaction is submitted here)
# 1. Turn off auto commit transactions set autocommit = 0; # 2. Query specified user select * from jiaoyi where id=1; # 3. Modify specified user balance update jiaoyi set money = 500;
4) Terminal 2 queries user information
---------- window B ------------------------------------------
# 1. Turn off auto commit transactions
set autocommit = 0;
# 2. Query specified user
select * from jiaoyi where id=1;
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | qiudao | 500 |
+------+--------+-------+
1 row in set (0.00 sec)
Obviously, business A The transaction has not been committed after the data has been modified B Transactions can be read A There is no data for the committed transaction. This is dirty reading. Data in the case of dirty reading is not desirable, so generally there is no database transaction level set to allow dirty reading.
# Summary: dirty reading means that transaction A reads the data modified by transaction B but does not commit the transaction.
(affair B unexecuted commit Time, but business A But I read it)
2.2 non repeatable reading
After learning dirty reading, let's see what is non repeatable reading. Such as transactions A When the same record is read multiple times in the same transaction, the transaction B Transaction modified A The data is being read and the transaction is committed, but the transaction A Read transaction B The submitted data leads to inconsistent data read twice.
1) Modify transaction isolation level
# Global --- here we show how to set global set global transaction isolation level read committed; # Current session set session transaction isolation level read committed;
2) Window 1 opens the transaction to query the data of the specified user
---------- window A ------------------------------------------ # 1. Turn off auto commit transactions set autocommit = 0; # 2. Query specified user select * from jiaoyi where id = 1; Window 2 executes the transaction # 1. Query specified user select * from jiaoyi where id = 1; # 2. Commit transaction commit;
3) Window 2 executes transactions at the same time
Window 1 starts the transaction to query the information of the specified user, and then window 2 starts the transaction to query the data of the specified user, modify the data, submit the transaction, and then return to window 1 to query the information of the specified user; The operation contents of window 2 are as follows
# 1. Turn off auto commit transactions set autocommit = 0; # 2. Query specified user select * from jiaoyi where id = 1; # 3. Modify specified user balance update money set money = 300 where id=1; # 4. Commit transaction commit; affair A In the two queries, the data queried are different, that is, they cannot be read repeatedly. Mysql By default, the isolation level of non repeatable reading is adopted. To sum up in one sentence, non repeatable reading is a transaction A Read transaction B The data of the transaction has been committed, resulting in inconsistent data information read twice.
2.3 unreal reading
Above, I learned what can't be read repeatedly. In mysql database, it is not allowed to read repeatedly.
# Global --- here we show how to set global set global transaction isolation level repeatable read; # Current session set session transaction isolation level repeatable read;
2) Window 1
# 1. Turn off auto commit transactions set autocommit = 0; select * from jiaoyi where id = 5; # At this time, window 1 does not query the data with id 5. When preparing to insert, window 2 inserts a piece of data with id 5. # 2. No query results found, please insert insert into jiaoyi values (5, 'cm',200); # 3. Then query the with id 5 select * from money where id = 5; commit;
3) Window 2: here, transaction A queries the record with the specified id of 5. After no query result is found, transaction B inserts A record with the id of 5 and submits the transaction;
# 1. Turn off auto commit transactions set autocommit = 0; # 2. Insert a record insert into jiaoyi values (5,'cm',200); # 3. Commit transaction commit; In the above transaction A In, insert without committing the transaction id Records with 5 will always report errors and primary key conflicts, but how to query again id No records of 5 can be queried; This is because in MySql In the design of, the log before modification is queried in the transaction. Namely Undo log.
3. Modify isolation level
#View isolation level mysql> show variables like '%iso%'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | tx_isolation | READ-UNCOMMITTED | +---------------+------------------+ 1 row in set (0.00 sec) #Configure isolation level [root@db01 ~]# vim /etc/my.cnf [mysqld] transaction_isolation=read-uncommit
2, Log of transactions
1.redo log
redo，As the name suggests, "redo log" is a kind of transaction log.
# 1) Function In transaction ACID In the process, the realization is“ D "The role of persistence. REDO: It records the change process of memory data pages characteristic:WAL(Write Ahead Log)Log write first # 2) REDO working process Execution steps: update t1 set num=2 where num=1; 1)First will t1 In the table num=1 The data page of the row is loaded into memory buffer page 2)MySQL Instance in memory num=1 Change the data page to num=2 3)num=1 become num=2 The process of change will be recorded, redo Memory area, i.e redo buffer page in Commit transaction execution steps: commit; 1)When knocked down commit The moment of command, MySQL Will redo buffer page Write to disk area redo log 2)When the write is successful, commit return ok
2.undo log
1) Function
undo，As the name suggests, "rollback log" is a kind of transaction log. In transaction ACID In the process, the realization is“ A"Atomic action. of course CI The characteristics are also similar to undo of # PS: undo buffer cache It will write data to disk in real time, that is, whether it is executed commit affair id: txid Log version number: lsn Visible during physical backup
3, Locks in transactions
1. What is a lock? Function of lock
# Lock, as the name suggests, means locking. When modifying data, lock the data # In the process of transaction ACID characteristics, "lock" and "isolation level" work together to realize the role of "I" isolation.
2. Examples
data id=1 Transaction 1 set id=2 Transaction 2 set id=3 #1. Create a table create table test(id int); #2. Insert data insert into test values(1); #3. Open two windows to open transactions begin; update test set id=2 where id=1; begin; update test set id=3 where id=1;
3. Type of lock
Exclusive lock: ensure data consistency during multi transaction operations.(When I modify the data, others cannot modify it) Shared lock: ensure that data query will not be blocked during multi transaction work. Optimistic lock: during multi transaction operations, data can be modified at the same time. Whoever commits first will modify successfully. Pessimistic lock: during multi transaction operations, only one person can modify the data.
3. Multi version concurrency control (MVCC)
1)Only modify class operations (exclusive locks) are blocked, and query class operations (shared locks) are not blocked 2)Optimistic lock mechanism (whoever submits first shall prevail)
4. Lock granularity
MyISAM: Table lock InnoDB: Row level lock
5. Isolation level of transactions
1)Four isolation levels: 1.rc: read committed Allow transactions to view committed changes made by other transactions 2.ru: read uncommitted(Independent submission), uncommitted reading, allowing transactions to view uncommitted changes made by other transactions; 3.rr: repeatable read Repeatable reading InnoDB Default level for #After the commit is submitted, you may not see the data changes and must reconnect 4.serializable: Serialization:，Completely isolate the results of one transaction from other transactions #If a transaction is not committed, the query cannot be performed # When I change my wechat avatar, you can't read my information. When I look at your circle of friends, you can't send or look at your circle of friends 2)Modify isolation level #View isolation level mysql> show variables like '%iso%'; #Modify isolation level to RU [mysqld] transaction_isolation=read-uncommit mysql> use oldboy mysql> select * from stu; mysql> insert into stu(id,name,sex,money) values(2,'li4','f',123); #Modify isolation level to RC [mysqld] transaction_isolation=read-commit
6. Dirty reading, unreal reading, non repeatable reading
1.Dirty read:( RU Level will occur) When a transaction is executed, it is not committed, and the value is read by others, but the transaction is rolled back. The newly unique data is called dirty data 2.Unreal reading:( RR Level can solve this problem) # It can be understood as: the current state of the operating end When a transaction deletes all data in the database, but the transaction B At this time, a new record is inserted when the transaction A After deletion, another piece of data is found, which is like an illusion. This is called Unreal reading. # To put it bluntly, the current data of the current user has not been refreshed to the latest, or part of it has been refreshed, and there are residues that have not been completely refreshed, resulting in inconsistent data 3.Non repeatable: # Current state of the operated end Execute a transaction to read data twice. After reading a piece of data for the first time, a transaction modifies the read data and commits it. The first transaction reads the data again, and the two reads get different results. # To put it bluntly, the current data of the current user has not been refreshed to the latest, resulting in inconsistent data