mysql lock and transaction isolation level

--Set transaction isolation level read uncommitted
set tx_isolation='read-uncommitted';
show variables like 'tx_isolation';

--Open things
begin;
select * from account;

Open the second query window, the updated result is 449, and the things are not submitted.

begin;
update account set balance = balance-1 where id =1;
select * from account;

Returning to the first query window, we read that the value of balance with id=1 is 449. In the second window, we can still read things that have not been submitted. This is read uncommitted. We can set the isolation level to read submitted.

2.2 read submitted

2.2.1 test dirty reading problem

First window

set tx_isolation='read-committed';
show variables like 'tx_isolation';

--Prevent other things from being submitted first
commit;
begin;
select * from account;

Second window

commit;
begin;
update account set balance = balance -50 where id = 1;
select * from account;

The update operation is not submitted, and the updated data cannot be read in the first query operation, as shown in the following figure

2.2.2 non repeatable reading

In the above operation, after the second window submits the transaction, the first window can see the updated value, which is the problem of non repeatable reading. A simple summary is the problem of inconsistent data queried by the first read operation before and after the update operation. You can set the isolation level to solve the problem of repeatable read.

2.2.3 repeatable

First window

--Set readability level
set tx_isolation='repeatable-read';
show variables like 'tx_isolation';

commit;
begin;
select * from account;

Second window

commit;
begin;
update account set balance = balance -50 where id = 1;
select * from account;

Go back to the first window and query again. The result is still 400, which ensures the consistency of things.

2.2.4 mvcc (to be supplemented)

MVCC: multi version concurrency control, read committed and repeatable read levels are all useful to MVCC. I still don't understand the principle. I'll add later...

2.2.5 unreal reading

First window query

set tx_isolation='repeatable-read';
show variables like 'tx_isolation';

commit;
begin;
select * from account;

The second window inserts a data submission with id=4

begin;
insert into account values(4,'lily',700);
select * from account;
commit;

In the first window, the data of the account table remains unchanged:

The first window updates the data with id=4. Re query can find the data with id=4. Unreal reading is the data not queried for the first time. After the update operation of other things, there are different results from the data queried for the first time.

update account set balance=888 where id = 4;
select * from account;

2.2.6 serialization (to be supplemented)

The first window sets serialization and queries table data

set tx_isolation='serializable';
show variables like 'tx_isolation';

commit;
begin;
select * from account;

In the second window, if you insert a piece of data with id=5, the table will be locked to prevent unreal reading. However, the concurrency is very low. After a period of time, mysql will automatically release the lock and roll back the transaction. The mechanism is not clear

2.2.7 can gap lock be used to solve unreal reading at the default repeatable reading level?

The first window sets the repeatability and uses the gap lock

set tx_isolation='repeatable-read';
show variables like 'tx_isolation';

commit;
begin;
update account set name = 'zhuge' where id > 1 and id <=3;
select * from account;

The second window updates the data with id=1, which can be updated successfully.

commit;
begin;
--1
update account set name = 'lilei' where id =1;
select * from account;

Deadlock occurs when updating the data with id=2. Verification can prevent unreal reading.

update account set name = 'lilei' where id =2;
select * from account;

2.2.8 if the index is not used for updating, will there be deadlock?

The update operation without index will rise from row lock to table lock, which will lead to deadlock of other operations. This operation is very dangerous.

--First window
set tx_isolation='repeatable-read';
show variables like 'tx_isolation';

commit;
begin;
update account set balance = 800 where name = 'lilei';
select * from account;

--Create a second query window
--Update any row of the table
update account set name = 'lilei' where id =1;

2.2.9 another way to add shared lock and exclusive lock

--Read lock (shared lock)
select * from account where id = 2 lock in share mode;
--Write lock (Exclusive lock)
select * from account where id = 2 for update;

3. Things

Thing is a program processing unit composed of a group of SQL statements. It has four characteristics: ACID

Atomicity (Sql is either submitted or not submitted), consistency (business consistency is mainly guaranteed by the other three features), isolation (other threads are isolated and invisible in the concurrent environment), persistence (data can be permanently saved);

Transaction isolation level: read uncommitted, read committed, repeatable read, serializable.

The isolation level corresponds to three problems: dirty reading (when the update of the same record has not been submitted, another thing updates it), non repeatable reading (when the same read operation reads different results, the second thing reads the modified data), and unreal reading (the newly added data in the middle is found before and after the same query operation, resulting in inconsistent query results).

3.1 viewing and setting the isolation level of things

The default transaction isolation level of mysql is as follows: REPEATABLE-READ

Often see the transaction isolation level of the current database: show variables like 'tx_isolation';
Set transaction isolation level: set tx_isolation='REPEATABLE-READ';

3.2 troubleshooting deadlock

3.2.1 viewing status quantity

--View the five common constants
show status like 'innodb_row_lock%';

The description of each state quantity is as follows: 
Innodb_row_lock_current_waits: The number of currently pending locks 
Innodb_row_lock_time: Total lock time from system startup to now 
Innodb_row_lock_time_avg: Average time per wait 
Innodb_row_lock_time_max: The longest waiting time from system startup to now 

Innodb_row_lock_waits:Total waiting times since system startup 
For these five state variables, the more important ones are: 
Innodb_row_lock_time_avg (Average waiting time) 
Innodb_row_lock_waits (Total waiting times) 
Innodb_row_lock_time(Total waiting time)

3.2.1 viewing deadlock log

After viewing the log, copy the value of the status field into the text, and search DEADLOCK to see which sentence is locked for analysis.

--To view recent deadlock log information:
show engine innodb status;;

Posted by sangprabv on Sat, 06 Nov 2021 19:40:11 -0700

Programmer Group