2021.09.14 update

0 foreword

This article was written in 2019, and this introduction was written in 2021 after a two-year break, fixing many of the errors in the article and adding more detailed steps and testing procedures.

This article mainly describes the four features of transactions and dirty read, magic read, unrepeatable read, lost update, there are four isolation levels, and in the four isolation levels of the dirty read, magic read, unrepeatable read were tested, in addition to the MySQL part of the lock (X lock, S lock, optimistic lock, pessimistic lock).

Test environment:

Database: used locallyDockerTo create theMySQL
Version:8.0.25
Engine:InnoDB

1 ACID

The four characteristics of transactions are ACID.

1.1a: Atomicity

Atomicity refers to the fact that a transaction either executes completely or not at all.

1.2C: Consistency

When the transaction completes, the data must be in a consistent state. If an error occurs during the execution of a transaction, it is rolled back to the state before the previous transaction was executed, so that the data remains in a consistent state. If the transaction fails and is not rolled back, some of the changes are written to the database, then the data is in an inconsistent state.

1.3I: Isolation

When multiple transactions are processed simultaneously, the execution of one transaction cannot be interfered with by another, and the internal operations of the transaction are isolated from other concurrent transactions.

1.4 D: Durability

After a transaction commits, changes to the data are permanent.

2 the MySQL lock

MySQL locks can be classified in many ways:

Press add lock mechanism cent: can divide for optimistic lock, pessimistic lock
According to compatibility, it can be divided into X lock and S lock
According to the lock granularity: table lock, row lock, page lock
According to the lock mode: it can be divided into record lock, gap lock, next-key lock, intention lock, insert intention lock

Here we mainly discuss S lock, X lock, optimistic lock and pessimistic lock.

2.1 S Lock and X Lock

S locking and X locking are two standard row locking mechanisms implemented by InnoDB engine. View the default engine available

show variables like '%storage_engine%';
Copy the code

The author’s MySQL version is 8.0.17, and the result is as follows:

Set up test table:

create table a
(
id int primary key auto_increment,
money int
);
Copy the code

2.1.1 S lock

S lock is also called shared lock. Read lock. Data can only be read but cannot be modified.

Lock S on table:

lock table a read;
Copy the code

Then you can see that there is a read lock on the change:

You can only read but not write.

2.1.2 X lock

X lock is also called exclusive lock. After a transaction locks a table, other transactions cannot add, delete, or change the table.

Set manual commit, open transaction, lock X:

set autocmmmit=0;
start transaction;
lock table a write;
Copy the code

To start another transaction, use select:

set autocommit=0;
start transaction;
select * from a;
Copy the code

You can see that with the X lock, another transaction will block even reading. This is because the transaction that opened the X lock blocks the select operation of the other transaction.

You can use

unlock table;
Copy the code

Release the lock so that another transaction can see when it is locked:

2.2 Optimistic and pessimistic locks

2.2.1 optimistic locking

Optimistic locking is a kind of loose locking mechanism that always assumes the best case and does not lock the data every time the operation is performed, but determines whether there are other operations to update the data during the update.

MySQL itself does not provide support for optimistic locking, so you need to implement it yourself. Common methods include version control and timestamp control.

Versioning: Versioning is adding a tableversionField with which data is readversionAdd 1 to the version number and compare the version number of the submitted data with the version number in the database. If the version number of the submitted data is greater than the version number in the database, the data will be updated
Timestamp control: Timestamp control is similar to version controlversionField is changed totimestampfield

For example, suppose at this point:

version=1Transaction A performs the operation
After transaction A updates the dataversion=2At the same time, transaction B also operates and updates the dataversion=2
Transaction A completes the operation first and is the first to put the databaseversionSet to 2
After transaction A completes, transaction B commits its ownversionAnd found in the databaseversionAs such, transaction B’s commit is not accepted

There is also a realization method called CAS algorithm, interested in their own search.

2.2.2 pessimistic locks

Pessimistic locking is always assuming the worst-case scenario that the data is locked for the entire data-processing state. Pessimistic locking implementations often rely on the locking mechanism of the database, which locks data every time before it is retrieved.

MySQL is pessimistic about locking when calling statements such as:

select * from a where xxx for update;
Copy the code

Turn off two transactions for auto-commit:

set autocommit=0;
start transaction;
Copy the code

The tests are as follows:

The select statement will block until the locked transaction commits (unlocks the pessimistic lock).

3 dirty read, phantom read, unrepeatable read with two types of lost updates

3.1 dirty read

Dirty read refers to that a transaction reads uncommitted data from another transaction, causing data inconsistency before and after the select.

For example, if transaction A modifies some data but does not commit it, then transaction B reads the data modified by transaction A but does not commit it, then transaction B forms A dirty read. Normal transaction A is followed by A rollback, and transaction B reads A temporary value.