Be used in, develop database transactions and locks, in order to ensure that they can very good to use these knowledge, need you to have a deep understanding to them, otherwise you will encounter all sorts of strange phenomenon, investigate its reason, or to understand the knowledge, can not speak, also can use, say, could be don’t quite understand, so this article will combine the official copies of the mysql documentation, Summarize a complete set of applicable transaction and lock knowledge, including lock, transaction, isolation level and so on, and explain the relationship between them, in the concurrency, how to ensure the security of data, etc., due to the length may be too long, so it will be divided into several sections, temporary three sections.
The official copies of Mysql address: dev.mysql.com/doc/refman/…
The script for building the table sentence and initial data is at the bottom of this article;
Start with a few questions in this article
- What is the isolation level of Mysql database transactions?
- What about locks in Mysql data?
- What problem does locking in Mysql solve, and why do you use it?
- What is the relationship between the isolation level of Mysql database transactions and locks?
What is the isolation level of Mysql database transactions?
When using a Mysql database, the concept of isolation level is only used when dealing with transactions. It refers to the fact that different isolation levels can produce different expected results when SQL statements operate on data in multiple transactions.
To view the isolation level of the current session: SELECT @@tx_isolation;
Change the isolation level of the current session:
Set session transaction isolation level read UNcommitted;
Set session TRANSACTION ISOLATION level read COMMITTED;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
There are four isolation levels for mysql-InnoDB storage engine transactions: the default isolation level of mysql-InnoDB is REPEATABLE READ
| Transaction Isolation level | instructions | Use levels in practice |
|---|---|---|
READ UNCOMMITTED |
Uncommitted read | Very low |
READ COMMITTED |
Submit to read | high |
REPEATABLE READ |
Repeatable read | high |
SERIALIZABLE |
serial | Very low |
1.1 Differences in Isolation Levels
The author uses Dbeaver as the connection client for one of the transactions, and then installs the original mysql client locally. They connect to the same database, and then starts to operate. Of course, it is completely possible to open the two original clients to simulate, which can be selected according to their own preferences
Session1: Dbeaver client:
The table hopegaming_main.test_1234 is used to illustrate each of these, provided that we start the transaction in both sessions and then operate on it
1.1.1 READ UNCOMMITTED
As the name implies, one transaction reads uncommitted data from another transaction
Operations: Set the isolation level to READ Uncommitted, start a transaction in Session1, insert a single transaction into the table, and query data in session1 and Session2, respectively
Session1:
Operation script:
set session transaction isolation level read uncommitted;
begin;
select * from hopegaming_main.test_1234;
INSERT INTO hopegaming_main.test_1234 (id, name, trade_id, gender, birthday) VALUES('5', 'zhangsan', '123', 0, CURRENT_TIMESTAMP(6));
select * from hopegaming_main.test_1234;
Data before insertion
Data after insertion (transaction uncommitted)
session2:
Operation script:
set session transaction isolation level read uncommitted;
select * from hopegaming_main.test_1234; (Performed before insertion)
select * from hopegaming_main.test_1234; (Execute after insert)
Data before insertion
Data after insertion (transaction uncommitted)
Symptom analysis: You can see that the select statement in session2 reads data from uncommitted transactions in Session1. What is the problem? Once the transaction in session1 is rolled back, the data read in session2 will be garbage because it has been rolled back and the record will not exist. This is often referred to as dirty reads
1.1.2 READ COMMITTED
In order to solve the dirty read problem mentioned above, there is an isolation level for read commits, which, as the name implies, means that one transaction will only read data that has already been committed in another transaction.
Operations: Set the isolation level to READ COMMITTED, start a transaction in Session1, insert a single entry into the table, do not commit the transaction, query the data in session1 and Session2, commit the transaction, and check the data in session2
Session1:
Operation script:
set session transaction isolation level read committed
begin;
select * from hopegaming_main.test_1234;
INSERT INTO hopegaming_main.test_1234 (id, name, trade_id, gender, birthday) VALUES('5', 'zhangsan', '123', 0, CURRENT_TIMESTAMP(6));
select * from hopegaming_main.test_1234;
commit;
Data after insertion (transaction uncommitted)
session2:
Operation script:
set session transaction isolation level read committed
select * from hopegaming_main.test_1234; (Executed after insertion, transaction not committed)
select * from hopegaming_main.test_1234; (Execute after insert, transaction commit)
Data after insertion (transaction uncommitted)
Data after insertion (transaction committed)
Phenomenon analysis: You can see that in Session2, before the transaction commits in Session1, the data read is the same as the data read before the transaction commits, and after the transaction commits, the select statement is executed again, and the data read is the same as the data read before the transaction commits. This isolation level solves the dirty read problem. It does not read the uncommitted data. So what’s wrong with this isolation level? As you may have noticed, in session1, the data read from session2 is different before and after the transaction commit. Before the transaction commit, the data read from session2 is the same, and after the transaction commit, the data read from session2 is a set of new data. So in some businesses, the data read from session2 may be repeated, but the results are different. Since the read action may occur before and after the transaction is committed, data may be read inconsistently, which is often referred to as non-repeatable reads
1.1.3 REPEATABLE READ(REPEATABLE READ)
Read the above submitted isolation level in some business would be non-repeatable reads problem, so in order to solve this problem, there will be A repeatable read isolation level, it is said, data in A transaction, A transaction reads the B in B before, during and after the transaction commit A read the result is the same, this is repeatable read, the principle is that when A first read, form A snapshot, Transaction A reads this statement directly from the snapshot formed by the first read regardless of whether transaction B commits, so the data read from transaction A is consistent
Operations: Set the isolation level to REPEATable Read, start a transaction in Session1, insert a piece of data into the table, do not commit the transaction, query the data in session2, then commit the transaction in Session1, and check the data in the table again in Session2
Session1:
Operation script:
set session transaction isolation level repeatable read
begin;
select * from hopegaming_main.test_1234;
INSERT INTO hopegaming_main.test_1234 (id, name, trade_id, gender, birthday) VALUES('5', 'zhangsan', '123', 0, CURRENT_TIMESTAMP(6));
commit;
Session2:
Operation script:
set session transaction isolation level repeatable read
begin;
select * from hopegaming_main.test_1234; (Read before transaction commit)
select * from hopegaming_main.test_1234; (Read after transaction commits)
Data after insertion (transaction uncommitted)
Data after insertion (transaction committed)
Symptom analysis: You can see that the select statement in Session2 reads the same data before and after the transaction commit in Session1, which is called repeatable read. It solves the non-repeatable problem. What’s the problem with this isolation level? As discussed above, at the read-repeatable isolation level, the data read in session2 is the same before and after the transaction is committed in session1. For example, the data read in session2 is pre-insert data because the transaction in session1 is committed. In session2, the new data is actually stored in the database, whereas in Session2, the new data is read before the transaction was inserted. Once session2 commits the transaction, and then reads the new data again, it will create a sense of magic reading. This is often called magic reading. Magic read usually refers to the insertion and deletion of the time caused by the magic read, then how to solve the magic read? This leads to another isolation level, serial
1.1.4 SERIALIZABLE(serial)
The above repeatable read has the problem of phantom reading, so the concept of serialization has emerged to solve this problem
Actions: Set the isolation level to serializable, start a transaction in session1, insert a piece of data into the table, do not commit the transaction, query the data in session2, commit the transaction in Session1, and check the data in session2
Session1:
Operation script:
set session transaction isolation level serializable
begin;
select * from hopegaming_main.test_1234;
INSERT INTO hopegaming_main.test_1234 (id, name, trade_id, gender, birthday) VALUES('5', 'zhangsan', '123', 0, CURRENT_TIMESTAMP(6));
commit;
Session2:
Operation script:
set session transaction isolation level serializable
begin;
select * from hopegaming_main.test_1234; (Read before transaction commit)
select * from hopegaming_main.test_1234; (Read after transaction commits)
Data after insertion (transaction uncommitted)
We can only query the data in session1 after the transaction is committed, so there is no magic read, because it involves insert,delete, etc., and it can only be read after the transaction is committed, so this is called serialization. Serialization means that one transaction is executed before the other, so it can’t be parallel. This can severely degrade performance, so this isolation level is rarely used and would be difficult to use if not for some special business need
Phenomenon analysis:
Serial isolation level, cannot be processed in parallel, severely degrades performance, rarely used in production and can be skipped
Scripts that create table sentences and initial data
CREATE TABLE `hopegaming_main`.`test_1234` (
`id` varchar(30) NOT NULL COMMENT 'Id Number'.`name` varchar(100) DEFAULT NULL COMMENT 'name'.`trade_id` varchar(100) DEFAULT NULL COMMENT 'transaction id'.`gender` tinyint(4) DEFAULT NULL COMMENT 'gender'.`birthday` timestamp(6) NOT NULL COMMENT Date of birth,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_trade_id` (`trade_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO hopegaming_main.test_1234
(id.name, trade_id, gender, birthday)
VALUES('1'.'zhangsan'.'123'.0.CURRENT_TIMESTAMP(6)),
('2'.'zhaosi'.'124'.0.CURRENT_TIMESTAMP(6)),
('3'.'wangwu'.'125'.0.CURRENT_TIMESTAMP(6)),
('4'.'maqi'.'126'.0.CURRENT_TIMESTAMP(6));
Copy the code