Mysql MVCC and locks

So before we start, let’s just prepare a table table_atm

id	name	age
1	Dija Altman	18
2	Tero Altman	19
3	Seven Altman	16

1 Transaction isolation level

1.1 Why Transaction Isolation

1.1 What is transaction isolation

For concurrent access to data resources, the extent to which transactions interact with each other is specified. We call this transaction isolation

1.2 Consequences of no transaction isolation

What unexpected things can happen if multiple transactions access the same data simultaneously?

1. Dirty reads. The transaction reads data that transaction B has not committed yet. For instance

A time line	Thread A	Thread B
1	begin transaction
2		begin transaction
3		update table_atm set age = 22 where id = 1
4	select age from table_atm where id = 1
5	commmit
6		roolback

If thread B’s transaction is rolled back, it is equivalent to A reading A non-existent record, which is A dirty read

2. Dirty write

A time line	Thread A	Thread B
1	begin transaction
2		begin transaction
3		update table_atm set age = 22 where id = 1
4	update table_atm set age = 25 where id = 1
5	commmit
6		roolback

As shown in the above table, A managed to change the data, but B rolled back, resulting in the change hours of A missing

3. Unrepeatable read

A time line	Thread A	Thread B
1	begin transaction
2	select name fron table_atm where id = 1	begin transaction
3		update table_atm set age = 22 where id = 1
4	select name fron table_atm where id = 1	commmit
5	commmit

Thread A reads different data twice

4. Phantom read

A time line	Thread A	Thread B
1	begin transaction
2	select name fron table_atm where id >= 1	begin transaction
3		Insert into table_atm (name, age) VALUES(' asotman ', 19)
4	select name fron table_atm where id >= 1
A read three pieces of data the first time, and read four pieces of data the second time

1.3 Four isolation levels

1. Read uncommittedREAD UNCOMMITTED
2. Reading has been submittedREAD COMMITTED
3. Repeatable readREPEATABLE READ
4. serializationSERIALIZABLE

Isolation level	Dirty read	Unrepeatable read	Phantom read
Read uncommitted	Y	Y	Y
Reading has been submitted	N	Y	Y
Repeatable read	N	N	Y
serialization	N	N	N

2 MVCC

Version 2.1 of the chain

Innodb rows record two indispensable fields in the middle stream

1. trx_idTransaction ID, to which the transaction ID will be assigned when any transaction changes the record row
2. roll_pointerRoll back the pointer. When a change is made to a row, the original version of the data is writtenundo log,roll_pointerThis field points toundo logThe corresponding raw data in

For example, let’s make the following change for table_ATM

A time line	operation
1	update table_atm set age = 21 where id = 1
2	update table_atm set age = 22 where id = 1
3	update table_atm set age = 23 where id = 1

So the version chain with id=1 is shown below. In essence, the various versions of the data are concatenated through the implementation of linked lists

2.2 the view

So this view is not the same as a db view, so let’s not confuse it but let’s make it clear that there are four important fields in the view

The field name	instructions
m_ids	The collection of all transactions that are currently active when the view is generated
min_trx_id	The smallest set of all transactions currently active at the time the view was generatedtrx_id
max_trx_id	Notice, this one is notm_idsIs the transaction ID that will be allocated to the next transaction when the view is generated
creator_trx_id	Represents the transaction ID of the generated view, which may be 0. (The transaction ID is assigned only when the INSERT, DELETE, UPDATE statements are executed, otherwise it defaults to 0 in a read-only transaction.)

So how do we determine which version of data is accessible to the current view based on these four fields?

1. First determine which records to accesstrx_id(Later referred to as goalstrx_id) is held with the current view creator_trx_idSame, if same means same transaction,visible.
2. The targettrx_idIs less thanmin_trx_id? If so, it indicates that the transaction for that version of the target record was committed before the view was generated,visible
3. The targettrx_idWhether greater than or equal tomax_trx_id? If so, it indicates that the transaction for that version of the target record was committed after the view was generated,invisible
4. The targettrx_idThe size of themin_trx_id 和max_trx_idIn between. The targettrx_idinm_idsCollection, indicating that the version of the transaction was active when the view was generated,invisible. Otherwise, the version of the transaction has been committed.visible

If the data of a certain version is not visible to the current view, it is necessary to find the data of the next version according to the version chain, and then make the above judgment until it finds the visible data or traverses the whole version chain but fails to find the appropriate data

2.4 View creation window (based on different isolation levels)

To prepare, define two transactions

• Transaction 1

A time line	operation
1	BEGINFor exampletrx_id = 50
2	update table_atm set age = 21 where id = 1
3	update table_atm set age = 22 where id = 1
4	update table_atm set age = 23 where id = 1

• Transaction 2

A time line	operation
1	beginAssignable by exampletrx_id = 55
2	Do some other operations that may not be on the data row, mainly to assign an active transaction ID without committing ha

Version of the chain

And at this point, transaction ID 50 and 55 are not committed

1. READ COMMITViews are generated before data is read

In the case of uncommitted transactions, we execute the following SQL

A time line	operation
1	BEGIN
2	select * from table_atm where id = 1

The select statements are executed in the following order

• Generate the viewReadViewAnd active at this timemidsfor[50 zhongguo kuangye daxue].min_trx_id= 50.max_trx_id = 56,creator_trx_id = 0
• Find the visible data from the version chain, as showntrx_idTransactions with = 50 are not visible because they are active in the current view
• trx_id= 40 transaction not presentm_idsRange, and thanmin_trx_idSmall, so visible

Then we commit the transaction with trx_id = 50

A time line	operation
1	BEGINFor exampletrx_id = 50
2	update table_atm set age = 21 where id = 1
3	update table_atm set age = 22 where id = 1
4	update table_atm set age = 23 where id = 1
5	commit

A time line	operation
1	BEGIN
2	select * from table_atm where id = 1
3	select * from table_atm where id = 1Let’s call itselect2statements

• To performselect2Statement generates a new view in whichm_ids 是[55] .min_trx_id = 55,max_trx_id = 56,creator_trx_id = 0
• Pick the data visible from the version, the latest versiontrx_idPhi is equal to 55, contained in phim_idsInside, not visible, continue to look for the previous version chaintrx_idPhi is equal to 50, less than thismin_trx_id. visible

---

Summary :READ COMMIT creates a new view for each query

2. READ REPEATABLEGenerate the view the first time it is read

We do the same

A time line	operation
1	BEGIN
2	select * from table_atm where id = 1

The select statements are executed in the following order

• Generate the viewReadViewAnd active at this timemidsfor[50 zhongguo kuangye daxue].min_trx_id= 50.max_trx_id = 56,creator_trx_id = 0
• Find the visible data from the version chain, as showntrx_idTransactions with = 50 are not visible because they are active in the current view
• trx_id= 40 transaction not presentm_idsRange, and thanmin_trx_idSmall, so visible

Then we commit the transaction with trx_id = 50

A time line	operation
1	BEGINFor exampletrx_id = 50
2	update table_atm set age = 21 where id = 1
3	update table_atm set age = 22 where id = 1
4	update table_atm set age = 23 where id = 1
5	commit

A time line	operation
1	BEGIN
2	select * from table_atm where id = 1
3	select * from table_atm where id = 1Let’s call itselect2statements

• To performselect2Statement is active when no new view is generatedmidsfor[50 zhongguo kuangye daxue].min_trx_id= 50.max_trx_id = 56,creator_trx_id = 0
• Find the visible data from the version chain, as showntrx_idTransactions with = 50 are not visible because they are active in the current view
• trx_id= 40 transaction not presentm_idsRange, and thanmin_trx_idSmall, so visible

---

Summary :READ REPEATABLE is a new view created when the transaction starts its first query

3 the lock

3.1 Principle of database lock

A lock in mysql is simply a piece of information in memory. Trx_id (is_WAITING); is_WAITING (trx_ID)

• trx_idWhich transaction generated the lock
• is_waiting Whether the current transaction is waiting.

1. A simple diagram of a transaction locking a record

2. Schematic diagram of two transactions locking recordsAs shown in the figure if

• trx_id=11The transaction holds the lock and starts executing,trx_id=21Transaction to also want to change, first to determine whether the record line has a lock, if there is a queue, at the same timeis_waitingIf the value is true, the lock is waiting to be released. when trx_id=11After the transaction is committed, subsequent queued transactions are notified to operate

3.2 Classification of locks

There are two main types of locks in Mysql

• The Shared lock. Also calledS lockWhen a transaction reads a record, it must first fetch itS lock
• An exclusive lock. Also calledX lockTransaction to change a recordX lock

The compatibility of locks is shown as follows

compatibility	S	X
S	Y	N
X	N	N

• S lockYou can andS lockCompatible. This is the only compatible way
• S lockwithX lockAre not compatible
• X lock 与 S lockAre not compatible

3.2.1 selectLocks generated by statements

1. S lock

SELECT … The LOCK IN SHARE MODE statement places an S LOCK on the record, allowing other transactions to continue acquiring the S LOCK, but not the X LOCK. SELECT * from syslock; SELECT * from syslock; SELECT * from syslock; The FOR UPDATE statement locks the record with X. Then other transactions cannot acquire either the S lock or the X lock. Other transactions must wait for the lock to be released if they want to acquire it

3.2.2 updateThe lock

In general, an UPDATE produces an X lock

3.2.3insertThe lock

Insert statements create implicit locks that guarantee that the inserted statement will not be read by anything else before committing the transaction

3.2.4 next-keyThe lock

3.2.5 Lock gap clearance

A gap lock locks the space between two values

3.2.6 row locks

For example, if transaction A updates A row and transaction B updates the same row, transaction B must wait for transaction A to complete before performing the operation

3.2.7 2 phase lock

Follow the example above

A time line	Thread A	Thread B
1	begin transaction
2	update table_atm set age = 25 where id = 1	begin transaction
3		update table_atm set age = 22 where id = 1
4
5	commmit
6		commmit

In InnoDB transactions, row locks are added as needed and released at the end of the transaction. This is the two-phase locking protocol and we also need to know that if we need to lock multiple rows in a transaction, we need to put the operations that affect concurrency last and lock them as late as possible

3.2.8 A deadlock

When different threads in a concurrent system depend on each other for resources, the threads involved are all waiting for each other to release locks, causing several threads to enter an infinite loop

A time line	Thread A	Thread B
1	begin transaction
2	update table_atm set age = 25 where id = 1;	begin transaction
3		update table_atm set age = 25 where id = 2
4	update table_atm set age = 25 where id = 2;
5		update table_atm set age = 22 where id = 1
6	commmit
7		commmit

3.2.9 next-key lock

We combine the row lock and gap lock into a next-key lock, which is a front open and rear closed pickup

3.3 How to determine which data is locked

First, summarize the locking principle

1. Principle 1: The basic unit of locking isnext-key lock , next-key lockIt’s a front open and a back closed interval
2. Rule 2: Lock only objects accessed during lookup
3. Optimization 1: query on an index with equivalent value, lock unique index (including primary key index),next-key lockDegradation ofRow locks
4. Optimization2: an equivalent query on an index, traversed right if the last value does not meet the equivalent condition,next-key lockDegradation ofClearance lock
5. One bug: Range queries on unique indexes access up to the first value that does not meet the criteria

To illustrate each principle, let’s first prepare a table table_atm and add a normal index to age

id	name	age
5	Dija Altman	5
10	Tero Altman	10
15	Seven Altman	15
20	Esther Altmann	20

3.3.1 Equivalent Query

Thread A	Thread B	Thread C
begin transaction
update table_atm set age = 25 where id = 7
	Insert into table_atm values(8, 'jack ', 30)
		update table_atm set age = 22 where id = 13

• According to principle 1: Thread A should queryid=7Is then updated, so it locks (5,10).
• According to Optimization 2: equivalent query, when the last condition is not satisfied when traversed to the right, it is degraded toGap lock, so the lock interval of thread A is (5,10).

The conclusion is that

1. The lock interval for thread A is 5 comma 10.
2. Thread B must wait for A to release the lock before inserting thread B because of A’s lock interval
3. Thread C is updateid=10Is not in the locked range and can be executed normally

3.3.2 Non-unique Index equivalent lock

Thread A	Thread B	Thread C
begin transaction
select id from table_atm where age = 10
	Update table_atm set name = leo-altmann WHERE = 10
		Insert into table_atm values(8, 'jack ', 30)

As shown above

1. According to principle 1: the interval that thread A locks is(5, 10]
2. ageIt’s a normal index. I found itage=10After this row, we continue looking for the first row that does not meet the criteria, so we continue locking(10, 15]
3. According to Optimization 2: wait querynext-keyTo lockgapLock, so thread A ends up locking(5, 15)
4. According to principle 2: only access to the object to lock, thread A using normal index to complete the query condition, did not use the primary key index, so forid=10Is there a lock on the primary key index of

conclusion

• Thread ALock the(5, 15), and theage=10Added a read lock
• Thread B can continue executing because thread A only queries the primary key ID and does not lock the primary key index

tips

• A shared lock locks only normal indexes, while an S lock locks all rows that meet the criteria on the primary key index
• The lock is added to the index,

3.3.3 Range Query of primary key indexes

Thread A	Thread B	Thread C
begin transaction
select id from table_atm where id >= 10 and id < 11
	Insert into table_atm values(8, 'jack ', 30)
	Insert into table_atm values(13, 'autterman ', 35)
		Update table_atm set name = leo-altmann WHERE = 15

Analysis of the

1. Thread A starts executing, first of allid>=10According to principle 1, the interval is locked(5, 10], according to optimization 1, the equivalent query will degenerate into a row lock, so the final lock isid=10
2. Thread A starts executingid<11, lock the interval according to principle 1(10, 15]

conclusion

1. The final interval that thread A locks is zero[10]
2. Thread B can insert the first SQL successfully and wait for the second insert to be released
3. Updates to thread C also wait for the lock to be released

3.3.4 Non-unique Index Range Query

Thread A	Thread B	Thread C
begin transaction
select * from table_atm where age >= 10 and age < 11
	Insert into table_atm values(8, 'jack ', 30)
		Update table_atm set name = leo-altmann WHERE = 15

Analysis of the

• Use normal indexes when querying, as per principle 1age>=10Will be locked(5, 10].age<11Will be locked(10, 15)

conclusion

1. Thread A is locked (5,15)

2. Both threads B and C block and wait for the lock to be released