To the read record S lock ( Shared lock ) Outside, Other operations, It's all about X lock ( Exclusive lock ). among, Except for the first sentence, Add. also, After reading, You also need to ensure that other concurrent transactions cannot modify the current record, Lock the read record. select * from table where ? for update Īll the above statements, All belong to the current reading, Read the latest version of the record.select * from table where ? lock in share mode.The current reading ： Special reading operations, Insert / to update / Delete operation, Belongs to the current reading, It needs to be locked. In a support MVCC Concurrency control system, Which reads are snapshot reads ？ Which operations are the current reading ？ With MySQL InnoDB For example ： The current reading, The latest version of the record is read, also, The record returned by the current read, It's locked, Ensure that other transactions do not modify this record concurrently. Read the snapshot, Read the visible version of the record ( It could be a historical version ), Don't lock it. Stay MVCC Concurrency control, There are two types of read operations ： Read the snapshot (snapshot read) And current reading (current read). Reading more and writing less OLTP Application, It is very important that there is no conflict between reading and writing, It greatly increases the concurrent performance of the system, That's why at this stage, Almost all RDBMS, All supported MVCC. MySQL InnoDB Storage engine, The implementation of concurrency is based on concurrency protocol -MVCC ( Multi-Version Concurrency Control) ( notes ： And MVCC Relative, It's lock based concurrency control ,Lock-Based Concurrency Control).MVCC The biggest benefit, I believe it's familiar ： Read without lock, Read write conflict. All of the following in this article, It's all based on InnoDB Storage engine, The performance of other engines, There will be a big difference. Notes ：MySQL It is a database system supporting plug-in storage engine. this paper, Get ready MySQL/InnoDB The lock problem of, To carry out a more in-depth analysis and discussion, It mainly introduces an idea, Use this idea, Get any one of them SQL sentence, Can complete analysis of this statement will add what lock ？ What are the risks ？ Even analyzing a deadlock scenario on the line, Understand the causes of deadlocks. meanwhile, I often get it on Weibo MySQL Lock related private messages, Let me help solve some deadlock problems. In the course of my work, Colleagues often ask questions in this regard. MySQL/InnoDB Lock analysis of, It has always been a difficult topic. Come from ： He Dengcheng's technology blog One 、 background

Gap analysis questions