Click “like” to see, form a habit, wechat search [dime small assistant] pay attention to more original technical articles. This article has been included in GitHub org_Hejianhui /JavaStudy.

preface

  • MySQL indexes underlying data structures and algorithms
  • MySQL Performance Optimization Principles – Previews
  • MySQL Performance Optimization – Practice 1
  • MySQL Performance Optimization – Practice 2

Previously we talked about MySQL database underlying data structure and algorithm, MySQL performance optimization some content. In this article, we will focus on the row lock and transaction isolation level of MySQL.

Lock definition

A lock is a mechanism by which a computer coordinates concurrent access to a resource by multiple processes or threads.

In databases, in addition to the contention for traditional computing resources (such as CPU, RAM, I/O, etc.), data is also a resource that needs to be shared by users. How to ensure the consistency and validity of concurrent data access is a problem that all databases must solve, and lock conflict is also an important factor that affects the performance of concurrent data access.

Classification of lock

  • In terms of performance, it can be divided into optimistic locking (achieved by version comparison) and pessimistic locking.
  • Database operation types are as follows:Read lock 和 Write lock(Both are pessimistic locks)
    • Read lock (shared lock) : Multiple read operations can be performed simultaneously for the same data without affecting each other.
    • Write lock (exclusive lock) : It blocks other write locks and read locks until the current write operation has completed.
  • The granularity of database operations is divided into table locks and row locks.

For an in-depth understanding of locking, see Understanding Locking in Java.

The lock on the MySQL

  • Record Locks

  • Gap Locks

  • Next-key Locks

  • Shared and Exclusive Locks

  • Intention Shared and Exclusive Locks

  • Insert Intention Locks

  • Auto-inc Locks

  • Predictive locks, which are mainly used for spatial indexes that store spatial data.

In the next article, I’ll focus on row locking and transaction isolation levels.

Table locks

Each operation locks the entire table.

  • Low overhead, fast lock;
  • No deadlocks occur;
  • The lock granularity is large and the probability of lock conflict is the highest.
  • The concurrency is lowest.

Basic operation

Example table, as follows:

# building tableSQL
CREATE TABLE mylock (
	id INT(11) NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(20) DEFAULT NULL.PRIMARY KEY(id)
) ENGINE = MyISAM DEFAULT CHARSET =utf8; # insert dataINSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('1'.'a'); 
INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('2'.'b'); 
INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('3'.'c'); 
INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('4'.'d');
Copy the code
  • Manually add table locks
lock tableTable name Read (write) indicates the table name2 read(write);
Copy the code
  • View the locks added to the table
show open tables;
Copy the code
  • Delete table locks
unlock tables;
Copy the code

Case Study – Add read lock

LOCK TABLE mylock read;
Copy the code

Both the current session and other Seesions can read this table;

An error is reported when a locked table is inserted or updated in the current session, while any other session is inserted or updated.

Case Study – Write lock

LOCK TABLE mylock WRITE;
Copy the code

The current session can add, delete, change, or query this table without any problem, and all operations on this table by other sessions are blocked.

Conclusion the case

MyISAM automatically locks all tables involved before executing a SELECT statement. Before performing the add, delete, modify, or query operation, a write lock is automatically added to the involved table.

  • Read operations (read locks) on MyISAM tables do not block other processes’ read requests to the same table, but block write requests to the same table. Write operations of other processes are performed only after the read lock is released.
  • Write operations on MyISAM table (add write lock) will block other processes’ read and write operations on the same table. Only after the write lock is released, other processes can perform read and write operations on the same table.

Summary: Read locks block writes, but not reads; Write locks block both reads and writes.

Row locks

Each operation locks one row of data.

  • High overhead, slow lock;
  • Deadlocks occur;
  • The lock granularity is minimum and the probability of lock conflict is minimum.
  • The concurrency is the highest.

Key differences between InnoDB and MyISAM:

  • Support transactions
  • Row-level locking is supported

Row locks support transactions

Transaction and its ACID properties

A transaction is a logical processing unit consisting of a set of SQL statements, and a transaction has the following four properties, often referred to simply as the ACID property of the transaction.

  • Atomicity: A transaction is an atomic unit of operation in which all or none of the modifications to data are performed.
  • Consistent: Data must be Consistent at the beginning and completion of a transaction. This means that all relevant data rules must be applied to transaction modifications to preserve data integrity; At the end of the transaction, all internal data structures (such as B+ tree indexes or bidirectional linked lists) must also be correct.
  • Isolation (Lsolation) : Database systems provide isolation mechanisms to ensure that transactions are executed in a “standalone” environment that is not affected by external concurrent operations. This means that intermediate states during transaction processing are not visible to the outside world and vice versa.
  • Durable: Changes to data after transactions are finished are permanent and can be maintained even in the case of system failures.

Problems with concurrent transaction processing

  • Lost Update

When two or more transactions select the same row and then update the row based on the value originally selected, a lost update problem occurs because each transaction is unaware of the existence of the other transactions, and the last update overwrites the updates made by the other transactions.

  • Dirty Reads

A transaction is modifying a record whose data is in an inconsistent state before the transaction is completed and committed. Another transaction reads the same record, and if left unchecked, a second transaction reads the dirty data and performs further processing on it, resulting in uncommitted data dependencies. This phenomenon is known figuratively as “dirty reading”.

Summary: Transaction A reads data that has been modified but not committed by transaction B, and acts on that data. At this point, if transaction B rolls back, transaction A reads invalid data and does not meet consistency requirements.

  • Non-repeatable Reads

“Non-repeatable reads” occur when a transaction reads previously read data again at some point after reading some data, only to find that the read data has been changed or some records have been deleted.

Conclusion: Transaction A has read the modification data that transaction B has committed, which does not comply with isolation.

  • Phantom Reads

The phenomenon of a transaction re-reading previously retrieved data under the same query criteria, only to find that other transactions insert new data that meets their query criteria, is called “phantom read.”

Conclusion: Transaction A has read the new data submitted by transaction B, which does not comply with isolation.

Transaction isolation level

“Dirty reads”, “unrepeatable reads”, and “phantom reads” are all database read consistency problems, which must be solved by the transaction isolation mechanism provided by the database.

The more stringent the transaction isolation of the database, the less concurrency side effects, but the higher the cost, because transaction isolation essentially serializes transactions to a certain extent, which is obviously contradictory to “concurrency.”

At the same time, different applications have different requirements on read consistency and transaction isolation. For example, many applications are not sensitive to “unrepeatable reads” and “phantom reads” and may be more related to the ability of concurrent data access.

View the transaction isolation level of the current database

show variables like 'tx_isolation';
Copy the code

Sets the transaction isolation level

set tx_isolation='REPEATABLE-READ';
Copy the code

The database version is 5.7, and the isolation level is Repeatable-Read. Different database versions and isolation levels greatly affect the execution results of statements. So you need to specify the version and isolation level

Row lock and isolation level case study

Transaction control statement

  • BEGIN 或 START TRANSACTION; Explicitly start a transaction;
  • COMMIT; You can also useCOMMIT WORKBut they are equivalent. COMMIT commits transactions and makes all changes to the database permanent;
  • ROLLBACK; Yes availableROLLBACK WORKBut they are equivalent. A rollback ends the user’s transaction and undoes any uncommitted changes in progress;
  • SAVEPOINT identifier; Savepoints allow you to create a SAVEPOINT in a transaction. You can have multiple Savepoints in a transaction.
  • RELEASE SAVEPOINT identifier; Deletes a savepoint for a transaction. When no savepoint is specified, the statement throws an exception.
  • ROLLBACK TO identifier; Roll back the transaction to the marker point;
  • SET TRANSACTION; Used to set the isolation level of the transaction. The InnoDB storage engine provides transaction isolation levelsREAD UNCOMMITTED,READ COMMITTED,REPEATABLE READandSERIALIZABLE.

Transaction processing method

There are two main methods for MYSQL transaction processing:

  1. Use BEGIN, ROLLBACK, and COMMIT

    • BEGIN Starts a transaction
    • ROLLBACK ROLLBACK transaction
    • COMMIT transaction confirmation

  2. MySQL > alter MySQL autocommit mode with SET

    • SET AUTOCOMMIT=0Disable automatic submission
    • SET AUTOCOMMIT=1`` Enabling Automatic submission

Example table, as follows:

CREATE TABLE `user` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR (255) DEFAULT NULL,
	`balance` INT (11) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('zhangsan'.'450');
INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('lisi'.'16000');
INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('wangwu'.'2400');
Copy the code

Row locking demo

When a session starts transaction update and does not commit it, another seesion will block updating the same record, and updating different records u will block.

Read uncommitted

(1) Open client A, set the committed transaction mode to Read uncommitted, and query the initialized value of user

set tx_isolation='read-uncommitted';
Copy the code

(2) Open another client B and update table user before client A commits the transaction(3) At this time, although the transaction of client B has not been committed, the updated data of client A can be queried on client B(4) Once the transaction of client B is rolled back for some reason, all operations will be cancelled, so the data queried by client A is actuallyDirty data.(5) Execute the update statement on client Aupdate user set balance = balance - 50 where id = 1;Zhangsan’s balance is not 350, but 400. If you think this is naive, in the application, we will use 400-50=350, not knowing the other session rollback, to solve this problem by reading the committed isolation level.

Reading has been submitted

(1) Open client A, set transaction mode to Read COMMITTED, and query all records of user

set tx_isolation='read-committed';
Copy the code

(2) Open another client B and update table user before client A commits the transaction(3) At this moment, the transaction of client B has not been submitted, and client A cannot query the updated data of CLIENT B.Solve the dirty read problem. (4) Transaction submission of client B(5) Client A executes the same query as the previous step, and the result is inconsistent with the previous step, that is, the problem of unrepeatable read occurs.

Repeatable read

(1) Open A client client A, set the current transaction mode to REPEATable Read, and query all records of table USER.

set tx_isolation='repeatable-read';
Copy the code

(2) Before the transaction commit of client A, open another client B, update table user and commit.(3) All records of the user table are queried on client A. The query result of step (1) is consistent with that of step (1).(4) On client A, run the following commandupdate user set balance = balance - 50 where id = 1, balance does not change to 400-50 = 350, and the value of Zhangsan’s balance is calculated with 350 in step (2), so it is 300, and the consistency of data is not damaged. Used under the isolation level of repeatable readsMVCCConcurrency Control (multi-version concurrency Control) Insert, update, and delete update the version number and are the current read (current version).

Let’s talk about MVCC next.

(5) Re-open client B, insert a new data and submit it.(6) Client A queries all records of table user, and no new data is found, so there is no phantom read.(7) Verify that phantom reading is performed on client Aupdate user set balance = 8888 where id = 4;, the update succeeds, and the new data on client B is queried again.

serialization

(1) Open client A, set the current transaction mode to SerialIZABLE, and query the initial value of user

set tx_isolation='serializable';
Copy the code

(2) Open a client B and set the current transaction mode toserializableMySQL > insert table locked; insert table lockedserializableThis isolation level is extremely low concurrency and is rarely used in development.

Conclusion the case

InnoDB storage engine implements row-level locking. Although InnoDB storage engine may have a higher performance loss in terms of locking mechanism than table level locking, it is far superior to MyISAM table level locking in terms of overall concurrent processing capability. When system concurrency is at its highest, InnoDB’s overall performance is significantly better than MyISAM’s.

However, InnoDB’s row-level locking also has its vulnerable side. When used incorrectly, InnoDB’s overall performance can not only be as good as MyISAM’s, but can even be worse.

Row lock analysis

Analyze row lock contention on the system by checking the innodb_ROW_lock state variable:

show status like 'innodb_row_lock%';
Copy the code

The description of each state quantity is as follows:

  • Innodb_row_lock_current_waits: Number of locks currently being waited for
  • Innodb_row_lock_time: total lock time since system startup
  • Innodb_row_lock_time_avg: average time spent on each wait
  • Innodb_row_lock_time_max: Time spent waiting for the longest time since system startup
  • Innodb_row_lock_waits: Total number of waits since system startup

For these 5 state variables, the most important ones are:

  • Innodb_row_lock_time_avg (average wait time)
  • Innodb_row_lock_waits
  • Innodb_row_lock_time (total wait time)

Especially when the number of waits is very high and the waiting time is not small, we need to analyze why there are so many waits in the system, and then make optimization plans according to the analysis results.

A deadlock

set tx_isolation='repeatable-read';
Copy the code
Session_1 executed:select * from user where id=1 forupdate; Session_2 executed:select * from user where id=2 forupdate; Session_1 executed:select * from user where id=2 forupdate; Session_2 executed:select * from user where id=1 for update;
Copy the code

To view recent deadlock logs:

show engine innodb status\G;
Copy the code

In most cases mysql can automatically detect deadlocks and roll back the transaction that caused the deadlock, but in some cases mysql cannot automatically detect deadlocks

Optimization Suggestions

  1. As far as possible, all data retrieval is done through the index, to avoid non-index row locks to upgrade to table locks;
  2. Reasonable design index, minimize the scope of lock;
  3. Reduce the range of retrieval conditions as much as possible to avoid gap locking;
  4. Try to control transaction size, reduce the amount of locked resources and length of time, and try to put the SQL related to transaction locking in the last transaction execution;
  5. The lowest possible level of transaction isolation.

Question and answer

  1. The default MySQL level isrepeatable-readIs there any way to solve this problem?

Gap Lock can solve illusionary problems in some cases. It is a locking mechanism introduced by Innodb to solve illusionary problems under repeatable commit. Update user set name = ‘HJH’ where id > 10 and id <= 20; , no other Session can insert or modify any data in the gap contained in the range lock.

For example, there are 3 entries in the user table.id > 2 and id <=3The third record is locked, and other session pairs cannot operate on the third record.

  1. A non-indexed lock can be upgraded to a table lock. The lock is mainly added to an index, but row locks may change if non-indexed fields are updated.

Client A performs:update user set balance = 800 where name = 'zhangsan';  Modifying or deleting any row in this table will be blocked on client BInnoDB rows are locked for indexes, not records. The index must not fail, otherwise it will be upgraded from a row lock to a table lock.

  1. Locking a row can also be usedLocal in share mode 和 For Update (exclusive lock), such as:select * from test_innodb_lock where a = 2 for update;Other sessions can only read the row, and changes are blocked until the session commits the locked row.

Some pictures from the network, copyright to the original author, delete.Copy the code