Shulou(Shulou.com)06/01 Report--

First of all, let's talk about the four characteristics of database transactions.

1 ACID

The four main characteristics of transactions are ACID (not "sour"...).

1) A: atomicity (Atomicity)

Atomicity means that a transaction is either fully executed or not executed at all.

(2) C: consistency (Consistency)

When the transaction is completed, the data must be in a consistent state. If there is an error during the execution of the transaction, it will be rolled back to the state before the transaction was not executed, so that the data is in a consistent state. If there is no rollback after the transaction error, some of the modifications are written to the database, and the data is inconsistent.

3) I: isolation (Isolation)

When dealing with multiple transactions at the same time, the execution of one transaction can not be disturbed by another transaction, and the internal operation of the transaction is isolated from other concurrent transactions.

(4) D: Durability

After the transaction is committed, the modification of the data is permanent.

2 Mysql lock

Mysql locks can actually be classified in many forms:

According to the locking mechanism, it can be divided into optimistic lock and pessimistic lock. According to compatibility, it can be divided into X lock and S lock. According to the lock granularity, it can be divided into table lock, row lock and page lock. According to the lock mode, it can be divided into record lock, gap lock, next-key lock, intention lock and insert intention lock.

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

(1) S lock and X lock

S lock and X lock are two standard row locking mechanisms implemented by InnoDB engine. View the default engine that can be used

Show variables like'% storage_engine%'

The author's mysql version is 8.0.17, and the results are as follows:

First build a test library and test table, very simple, the table has two fields.

Create database test;use test;create table a (id int primary key auto_increment,money int)

Ⅰ .s lock

S lock is also called shared lock, read lock, data can only be read can not be modified.

Play with it, lock it!

Lock table a read

And then.

You can only read, you can't change it, you can delete it, and you can't add it.

Ⅱ .X lock

X lock is also called exclusive lock, write lock. After one transaction locks the table, other transactions cannot lock it and add, delete, check and modify it.

Set manual commit, open transaction, and lock X.

Set autocmmmit=0;start transaction;lock table a write

When opening another transaction, use the select statement.

Set autocommit=0;start transaction;select * from a

This is blocking the select operation because the X lock has never been released.

Also can not add a lock, it is also blocking.

Go back to the original locked transaction, um, nothing happened, normal reading and writing.

After releasing the lock:

Unlock table

The interruption time can be seen in another transaction.

(2) optimistic lock and pessimistic lock Ⅰ. Optimistic lock

Optimistic locking is always assumed to be the best-case scenario, and it will not be locked every time it is operated, but it will determine whether there are other operations to update the data when updating, which is a loose locking mechanism.

Mysql itself does not provide optimistic lock support, which needs to be implemented by ourselves. there are two common methods: version control and timestamp control.

Version control

Version control is to add a version field to the table, read it together with the version field when reading the data, then update it, add the version number by 1, and then 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, it will be updated.

For example, suppose that version=1,An operates at this time, and version=2 after updating the data, while B also operates. After updating the data, version=2,A completes the operation first, and takes the lead in setting the version in the database to 2. At this time, B commits, and the version of B, like the version in the database, does not accept the submission of B.

Timestamp control

Timestamp control is similar to version control. Change version field to timestamp field.

There is also an implementation method called CAS algorithm, the author does not know much about this, interested can search their own. Ⅱ. Pessimistic lock

Pessimistic lock always assumes the worst case, the data is locked in the whole data processing state, and the implementation of pessimistic lock often depends on the locking mechanism of the database. It will be locked every time before getting the data.

Mysql will put pessimistic locks on some statements, such as (first turn off autocommit and open transactions):

Set autocommit=0;start transaction

Both transactions do this, and then one of the transactions enters:

Select * from a where xxx for update

Enter the same in another transaction:

The statement is blocked until the locked transaction commit (unlocks the pessimistic lock).

In another transaction, you can see that the transaction is blocked for 2.81s.

* lock in share mode.

Pessimism will also be added.

4 dirty reading, phantom reading, unrepeatable reading and two kinds of missing updates (1) dirty reading

Dirty reading means that one transaction reads the uncommitted data of another transaction, resulting in data inconsistency before and after select.

For example, transaction A modifies some data, but does not commit, and transaction B reads, transaction B forms a dirty read, the subsequent operation of general transaction An is rollback, and transaction B reads temporary values.

Transaction A transaction B begins transaction update X, the old value Xnotify 1, the new value Xquote 2 reads XMagazine 2 (dirty read) rollback Xtransactions 1 ends transaction (Xero1) ends transaction (2) Phantom Reading

Phantom reading does not mean that the same transaction executes the same select statement twice to get different results, but it means that there is no record in select, but when it is ready to insert, it is found that the record already exists and cannot be inserted, which leads to phantom reading.

Transaction A transaction B starts a transaction select some data is empty, ready to insert a new data to insert a new data commit, end the transaction to insert the data, and find that the insertion failed, because transaction B has inserted the same data and the transaction (3) cannot be read repeatedly

Unrepeatable reading means that one transaction reads the committed data of another transaction, resulting in data inconsistency before and after select.

For example, transaction A modifies some data and commits, while transaction B reads, and transaction B becomes unrepeatable.

Transaction A transaction B start transaction read Xreply 1 read Xreply 1 update Xsession 2 commit, end transaction read Xreply 2 end transaction (4) the first type of missing update

The first kind of missing update is that two transactions update one data at the same time. After one transaction updates and commits, the other transaction rolls back, resulting in the loss of committed updates.

Transaction A, transaction B begins transaction, transaction starts, transaction reads Xtransactions 1 reads Xtransactions 1 modifies Xstores 2 modifies Xtransactions 3 commits, ends transactions rollback ends transactions (Xblocks 1) Xtransactions 1 Magi X is supposed to be committed 3 (5) type II missing updates

The second kind of missing update is that two transactions update one data at the same time, and the data committed by the first updated transaction will be overwritten by the data committed by the later updated transaction, that is, the data committed by the first updated transaction will be lost.

Transaction A transaction B starts transaction read Xblocks 1 reads Xblocks 1 updates Xblocks 2 commits transactions, ends updates Xblocks 3 commits transactions, Xblocks 3, transaction A updates loses, ends 5 blocking protocols and isolation levels

The locking protocol is some rules made when using X lock or S lock, such as the duration of the lock, the locking time of the lock and so on. Different blocking protocols correspond to different levels of isolation. There are four isolation levels of transactions, from low to high are Read uncommitted,Read committed,Repeatable read,Serializable, respectively, corresponding to the corresponding blocking protocol levels.

(1) first-level blockade agreement

The first-level blocking protocol corresponds to the Read uncommitted isolation level, Read uncommitted, read uncommitted, and one transaction can read uncommitted data from another transaction, which is the lowest level. The first-level locking protocol essentially adds an X lock before the transaction modifies the data and is not released until the end of the transaction, which includes normal termination (commit) and abnormal termination (rollback).

The first-level blocking protocol will not cause update loss, but may cause dirty reading, phantom reading, and non-repeatable reading.

Set the manual commit and transaction isolation level to read uncommited, and open the transaction (remember to set the transaction level before opening the transaction).

Set autocommit=0;set session transaction isolation level read uncommitted;start transaction

There is an extra t in the middle line that can be ignored.

a. Cause dirty reading

Modify the value in the table in one transaction without committing, and another transaction can select to the uncommitted value.

There is a dirty reading.

b. Cause illusory reading

Insert a piece of data into a transaction and commit.

There is no select in another transaction, prepare insert, but insert indicates that it already exists. Cause illusion.

c. Raise a non-repeatable read

Before the operation is submitted:

Another transaction is modified and committed:

Read again:

Causes a non-repeatable read.

(2) second-level blockade protocol

In essence, the two-level locking protocol is based on the first-level protocol (adding an X lock when modifying the data), adding an S lock when reading the data, and releasing the S lock immediately after reading, which can avoid dirty reading. But there may be unrepeatable reading and illusion. The second-level blocking protocol corresponds to the isolation level of Read committed and Repeatable Read.

Set the isolation level first

Set session transaction isolation level read committed; Ⅰ .Read committed

Read committed, read commit, read commit can avoid dirty reading, but may lead to phantom reading and unrepeatable reading.

a. Avoid dirty reading

Open a transaction and update the value, in which the money=100 (after update)

In another transaction, money is the unupdated value, which avoids dirty reading.

Note that dirty reads are actually not allowed at the read committed isolation level, but mysql does not block queries, but instead returns backups of unmodified data, a mechanism called MVCC (multi-version concurrency control).

b. Cause illusion

Insert data into a transaction and commit it.

Non-existent data cannot be inserted into another transaction, resulting in phantom reading.

c. Raise a non-repeatable read

Before the transaction is modified and committed:

Transaction modification and commit:

It appears that it cannot be read repeatedly.

Ⅱ .Repeatable read

Repeatable read is stricter than Read committed and is the default level of Mysql. The reading process is more affected by MVCC, which can prevent unrepeatable and dirty reading, but phantom reading is still possible.

a. Avoid dirty reading

Modify data in a transaction without committing.

In another transaction, the results of the two select remain the same, and there are no dirty reads.

b. Avoid unrepeatable reading

A transaction modifies the data and commits it.

The result of select in another transaction has not changed, that is, there is no unrepeatable read.

c. Cause illusion

Similarly, a transaction inserts a piece of data and commits it.

A phantom reading occurs when another transaction is inserted.

(3) three-level blockade agreement

The three-level locking protocol, on the basis of the first-level locking protocol (adding an X lock when modified), adds an S-lock when reading the data (similar to the second-level), but the S-lock is not released until the end of the transaction, which can avoid illusory reading, dirty reading and unrepeatable reading. The isolation level corresponding to the three-level blockade protocol is Serializable.

Set the Serializable isolation level first

Set session transaction isolation level serializablea. Avoid dirty reading

After setting the transaction isolation level, open the transaction and update, and find blockage. So as to avoid dirty reading.

b. Avoid illusions

Block directly during insertion to avoid phantom reading.

c. Avoid unrepeatable reading

In the case of dirty reading, you can see that update is blocked and transactions cannot be committed, thus avoiding unrepeatable reads.

6 two-stage locking protocol

The transaction must be divided into two phases to lock and unlock the data. the locking protocol at both ends is called 2PL (not 2PC), and all locking is performed before unlocking.

(1) add lock

The lock will be updated or

Select * for update*** lock in share mode

At the same time

(2) unlock

Unlocking occurs at the end of the transaction, which includes rollback and commit.

Reference link

1:ACID1

2:ACID2

Lock 1 of 3:mysql

4: optimistic lock and pessimistic lock 1

5: optimistic lock and pessimistic lock 2

6: optimistic lock and pessimistic lock 3

7:mysql modifies transaction isolation level

8:mysql three-level lock and two-stage lock

9: database blockade protocol

10:mysql transaction isolation mechanism 1

11:mysql transaction isolation mechanism 2

12:mysql Phantom Reading

13:mysql dirty reading, unrepeatable reading and phantom reading

14:mysql two-stage lock 1

15:mysql two-stage Lock 2

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.