MYSQL transaction, isolation level, lock

Four Characteristics of Transaction

When it comes to database transactions, yes, we first think of ACID, which is the four characteristics of transactions.
A Atomicity C Consistency I Isolation D Persistence

The problems brought about by it

What problems will these four characteristics of transactions bring?
Dirty reading, unrepeatable reading, illusory reading

1. Dirty Reading: One transaction reads uncommitted content from another transaction
2. Non-repeatable reading: inconsistent content is read twice in a transaction. The reason is that during the period of two reads before and after the transaction, another transaction modified the data.
3. Hallucination: One transaction A receives M-bar data when it performs range query, and the other transaction B inserts N-bar data within the scope conforming to transaction A, resulting in M+N-bar data when transaction A is queried again, resulting in hallucination.

Unrepeatable reading is similar to hallucination in that the former is for update and the latter is for insert.

Four isolation levels of MYSQL:

1. Read Uncommitted (READ UNCIMMITTED): You can read the uncommitted content of other transactions, which can cause dirty reading problems.
2. Read submitted (READ COMMITTED): You can read the commit content of other transactions, which can lead to unreadable problems. This level is commonly used.
3. REPEATABLE READ: It guarantees that the contents read before and after the transaction are identical. Even if another thing modifies the data.
4. SERIALIZABLE: Serialization is the highest isolation level of a transaction. When a transaction reads or modifies table data, it adds table level locks to prevent hallucination. At the same time, performance can be greatly affected.

These four isolation levels can be set according to their own business needs, the most commonly used is READ COMMITTED.

lock

The isolation level of transactions is mainly controlled by locks, which are described below.

Different storage engines handle locks differently. After MYSQL5 version, Innodb is adopted by default. InnoDB supports table locks and row locks.

Here are some experiments:

Get ready:
1. Formulation

CREATE TABLE
    user_info
    (
        userid VARCHAR(40) NOT NULL ,
        username VARCHAR(40),
        sex INT(1),
        certtype VARCHAR(10),
        certid VARCHAR(20),
        phoneno VARCHAR(11),
        age INT(3),
        PRIMARY KEY (userid),
        INDEX certtype_id (certid, certtype)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Prepare several pieces of data

INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('000007c2b80f11e99a9454ee755d5b38', 'demo', 1, 'Ind02', '539452823381361', '13756168529', 66);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('00000a39b81511e99a9454ee755d5b38', 'fru', 1, 'Ind02', '535322313695514', '14297850313', 38);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('00000ba7b81011e99a9454ee755d5b38', 'hlz', 1, 'Ind02', '535322313695514', '14027724002', 85);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('0000161bb81911e99a9454ee755d5b38', 'diu', 1, 'Ind02', '838336353039682', '14279925806', 38);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('0000197cb81811e99a9454ee755d5b38', 'dsf', 1, 'Ind02', '748854264383068', '14433861050', 42);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('00004862b81311e99a9454ee755d5b38', 'udi', 1, 'Ind02', '533297220741675', '13796868687', 88);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('000052bab81611e99a9454ee755d5b38', 'l', 1, 'Ind02', '119285593851735', '14114443115', 71);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('0000580eb81111e99a9454ee755d5b38', 'hhi', 1, 'Ind02', '644459799532275', '14241303402', 77);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('00005edfb81711e99a9454ee755d5b38', 'fwu', 1, 'Ind02', '792649599530407', '14394181380', 9);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('00007186b81211e99a9454ee755d5b38', 'irl', 1, 'Ind02', '112327407218820', '13599040961', 15);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('0000808fb81411e99a9454ee755d5b38', 'ufi', 1, 'Ind02', '185371452183808', '13841946470', 15);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('00008719b80e11e99a9454ee755d5b38', 'wre', 2, 'Ind02', '707119444112404', '13864141398', 69);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('00008c62b81011e99a9454ee755d5b38', 'udh', 1, 'Ind02', '978111704791068', '14234926100', 74);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('00008f70b80f11e99a9454ee755d5b38', 'hus', 1, 'Ind02', '948014209929867', '14153437874', 42);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('00009aecb81511e99a9454ee755d5b38', 'uey', 1, 'Ind02', '376089044265532', '14118677569', 96);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('0000a956b81911e99a9454ee755d5b38', 'off', 1, 'Ind02', '179309141989153', '13898109495', 45);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('0000c7acb81811e99a9454ee755d5b38', 'eed', 1, 'Ind02', '928275743432599', '13858352803', 0);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('0000c918b81a11e99a9454ee755d5b38', 'ggi', 1, 'Ind02', '705640666843988', '13662965102', 69);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('0000cbbcb81611e99a9454ee755d5b38', 'yfu', 1, 'Ind02', '56476028688695', '13849848820', 57);
INSERT INTO user_info (userid, username, sex, certtype, certid, phoneno, age) VALUES ('0000d9bdb81111e99a9454ee755d5b38', 'di', 1, 'Ind02', '134768997444556', '14454071981', 36);

3. Start the experiment

Open a transaction and update the username of this row of data to "fru1"

 start transaction; -- Open transaction
    Update user_info set username='fru1'where userid='00000a39b81511e99a9454ee755d5b38'; -- update data, lock data
    No submission or rollback

Open another session, that is, reopen a window. Execute the following statement to update the name to "fru2"

update user_info set username='fru2' where userid='00000a39b81511e99a9454ee755d5b38';

What is the effect?

The card is here and cannot be updated. After the previous transaction is committed, the latter transaction continues to execute. Results after implementation:

The update was successful. This is easy to understand. In the first transaction, the update statement has a row lock.
This understanding, let's put it aside for the time being and proceed to the next experiment.

Execute the next statement:

start transaction;
update user_info set username='fru1' where username='fru2';

Execute in another window:

update user_info set username='fru2' where userid='0000161bb81911e99a9454ee755d5b38';

The results of implementation are as follows:

Get stuck!! Locked? But why is it that the updated data is not locked?

As a result of:

In mysql, InnoDB implements row locks and not lock records, but lock indexes. If a statement operates on a primary key index, MYSQL locks that primary key index. If a non-primary key index is operated, MYSQL will first index the non-primary key index, and then lock the relevant primary key index through the non-primary key index. If the operation is not indexed, the table is locked.

So in the crud operation later, will the update statement pay attention to this? If I pay attention, my goal will be achieved.

Tags: MySQL Database Session

Posted on Tue, 06 Aug 2019 20:37:01 -0700 by cwiddowson