1. Concept of lock

• We have learned about multithreading before. How to ensure the accuracy of data in multithreading? Yes, through synchronization. Synchronization is equivalent to locking. What are the benefits of locking? When a thread is really manipulating data, other threads can only wait. When a thread finishes executing, release the lock. Only other threads can operate!

• Then the lock function in our MySQL database is similar. When we learned about transactions, we explained the isolation of transactions, which may lead to dirty reading, non repeatable reading and unreal reading. At that time, our solution was to modify the isolation level of transactions, but we do not recommend modifying the isolation level of databases. Therefore, the role of the lock can also solve the previous problems!

• Lock mechanism: a rule designed by a database to ensure the consistency of data and use various shared resources to become orderly when accessed concurrently.

• For example, when purchasing goods on e-commerce websites, there is only one commodity in the commodity table, and at this time, two people buy it at the same time, so who can buy it is a key problem.

  Transactions are used here to perform a series of operations:

  1. First take the data of the item from the commodity table

  2. Then insert the order

  3. After payment, insert the payment table information

  4. Update the quantity of items in the item table

  In the above process, the lock can be used to protect the commodity quantity data information and realize isolation, that is, only the first user is allowed to complete the whole purchase process, while other users can only wait, which solves the contradiction in concurrency.

• In databases, data is a resource shared and accessed by many users. How to ensure the consistency and effectiveness of concurrent data access is a problem that all databases must solve. Due to the characteristics of MySQL's own architecture, different storage engines have designed locking mechanisms facing specific scenarios, so the engines are different, The resulting locking mechanism is also very different.

2. Classification of locks

• Classification by operation:

  • Shared lock: also called read lock. For the same data, multiple transaction read operations can be locked at the same time without affecting each other, but data records cannot be modified.

  • Exclusive lock: also called write lock. Before the current operation is completed, the reading and writing of other operations will be blocked

• Classification by particle size:

  • Table level lock: during operation, the entire table will be locked. Low cost and fast locking; No deadlock; The locking force is large, the probability of lock conflict is high, and the concurrency is the lowest. Prefer MyISAM storage engine!

  • Row level lock: during operation, the current operation row will be locked. High overhead and slow locking; Deadlock will occur; The locking granularity is small, the probability of lock conflict is low, and the concurrency is high. Prefer InnoDB storage engine!

  • Page level lock: the granularity of lock, the probability of conflict and the cost of locking are between table lock and row lock. Deadlock will occur, and the concurrency performance is general.

• Classification by usage:

  • Pessimistic lock: every time you query data, you think others will modify it. It is very pessimistic, so you lock it during query.

  • Optimistic lock: every time you query the data, you think others will not modify it. You are very optimistic, but you will judge whether others have updated the data during this period

• Locks supported by different storage engines

  Storage engine	Table lock	Row level lock	Page level lock
  MyISAM	support	I won't support it	I won't support it
  InnoDB	support	support	I won't support it
  MEMORY	support	I won't support it	I won't support it
  BDB	support	I won't support it	support

3. Demonstrate InnoDb lock

• Data preparation

  -- establish db13 database
  CREATE DATABASE db13;
  
  -- use db13 database
  USE db13;
  
  -- establish student surface
  CREATE TABLE student(
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	NAME VARCHAR(10),
  	age INT,
  	score INT
  );
  -- Add data
  INSERT INTO student VALUES (NULL,'Zhang San',23,99),(NULL,'Li Si',24,95),
  (NULL,'Wang Wu',25,98),(NULL,'Zhao Liu',26,97);

• Shared lock

  --Standard grammar
  SELECT statementlock in share mode;

  -- Window 1
  /*
  	Shared lock: data can be queried by multiple transactions, but cannot be modified
  */
  -- Open transaction
  START TRANSACTION;
  
  -- query id Data record for 1. Join shared lock
  SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
  
  -- Query data records with a score of 99. Join shared lock
  SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;
  
  -- Commit transaction
  COMMIT;

  -- Window 2
  -- Open transaction
  START TRANSACTION;
  
  -- query id Data record for 1(General query, you can query)
  SELECT * FROM student WHERE id=1;
  
  -- query id Data record for 1 and add a shared lock(You can query. Shared locks are compatible with shared locks)
  SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
  
  -- modify id The name of 1 is Zhang Sansan(It cannot be modified, and a lock will occur. The modification can be successful only after window 1 commits the transaction)
  UPDATE student SET NAME='Zhang Sansan' WHERE id = 1;
  
  -- modify id The name of 2 is Li Sisi(Successfully modified, InnoDB The engine defaults to row locks)
  UPDATE student SET NAME='Li sisi' WHERE id = 2;
  
  -- modify id The name of Wei 3 is Wang Wuwu(be careful: InnoDB If the engine does not use indexed columns. Will be promoted to a table lock)
  UPDATE student SET NAME='Wang Wuwu' WHERE id = 3;
  
  -- Commit transaction
  COMMIT;

• Exclusive lock

  --Standard grammar
  SELECT statement FOR UPDATE;

  -- Window 1
  /*
  	Exclusive lock: locked data cannot be queried or modified by other transactions
  */
  -- Open transaction
  START TRANSACTION;
  
  -- query id Data record for 1 and add an exclusive lock
  SELECT * FROM student WHERE id=1 FOR UPDATE;
  
  -- Commit transaction
  COMMIT;

  -- Window 2
  -- Open transaction
  START TRANSACTION;
  
  -- query id Data record for 1(Normal query is OK)
  SELECT * FROM student WHERE id=1;
  
  -- query id Data record for 1 and add a shared lock(Cannot query. Because exclusive locks cannot coexist with other locks)
  SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
  
  -- query id Data record for 1 and add an exclusive lock(Cannot query. Because exclusive locks cannot coexist with other locks)
  SELECT * FROM student WHERE id=1 FOR UPDATE;
  
  -- modify id The name of 1 is Zhang San(It cannot be modified, and a lock will occur. The modification can be successful only after window 1 commits the transaction)
  UPDATE student SET NAME='Zhang San' WHERE id=1;
  
  -- Commit transaction
  COMMIT;

• Note: Lock compatibility

  • Shared locks are compatible with shared locks

  • Shared lock and exclusive lock conflict

  • Exclusive lock and exclusive lock conflict

  • Exclusive lock and shared lock conflict

4. Demonstrate MyISAM lock

• Data preparation

  -- establish product surface
  CREATE TABLE product(
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	NAME VARCHAR(20),
  	price INT
  )ENGINE = MYISAM;  -- Specify storage engine as MyISAM
  
  -- Add data
  INSERT INTO product VALUES (NULL,'Huawei mobile phone',4999),(NULL,'Mi phones',2999),
  (NULL,'Apple',8999),(NULL,'ZTE',1999);

• Read lock

  --Standard grammar
  --Lock
  LOCK TABLE table name READ;

  --Unlock (unlock all tables in the current session)
  UNLOCK TABLES;

  -- Window 1
  /*
  	Read lock: all connections can only read data and cannot be modified
  */
  -- by product Table add read lock
  LOCK TABLE product READ;
  
  -- query product surface(query was successful)
  SELECT * FROM product;
  
  -- Modify the price of Huawei mobile phone to 5999(Modification failed)
  UPDATE product SET price=5999 WHERE id=1;
  
  -- Unlock
  UNLOCK TABLES;

  -- Window 2
  -- query product surface(query was successful)
  SELECT * FROM product;
  
  -- Modify the price of Huawei mobile phone to 5999(Cannot be modified. The modification can be successful only after window 1 is unlocked)
  UPDATE product SET price=5999 WHERE id=1;

• Write lock

  --Standard grammar
  --Lock
  LOCK TABLE table name WRITE;

  --Unlock (unlock all tables in the current session)
  UNLOCK TABLES;

  -- Window 1
  /*
  	Write lock: other connections cannot query and modify data
  */
  -- by product Add write lock to table
  LOCK TABLE product WRITE;
  
  -- query product surface(query was successful)
  SELECT * FROM product;
  
  -- Modify the amount of Xiaomi mobile phone to 3999(Modified successfully)
  UPDATE product SET price=3999 WHERE id=2;
  
  -- Unlock
  UNLOCK TABLES;

  -- Window 2
  -- query product surface(Cannot query. The query can be successful only after window 1 is unlocked)
  SELECT * FROM product;
  
  -- Modify the amount of Xiaomi mobile phone to 2999(Cannot be modified. The modification can be successful only after window 1 is unlocked)
  UPDATE product SET price=2999 WHERE id=2;

5. Demonstrate pessimistic lock and optimistic lock

• Pessimistic lock concept

  • It is very pessimistic. It is conservative about the operation that the data is modified by the outside world and believes that the data will be modified at any time.

  • The data needs to be locked in the whole data processing. Pessimistic locks generally rely on the locking mechanism provided by relational databases.

  • The row lock and table lock we learned before are pessimistic whether they are read-write locks.

• Optimistic lock concept

  • I'm very optimistic. Every time I operate the data, I think no one will modify it, so I don't lock it.

  • However, when updating, it will judge whether the data has been modified during this period.

  • It needs to be implemented by the user. There will be no concurrent preemption of resources. Only when submitting the operation can you check whether it violates data integrity.

• Pessimistic lock and optimistic lock use premise

  • When read operations are far more than write operations, locking an update operation will block all read operations and reduce throughput. Finally, you need to release the lock. The lock needs some overhead. At this time, you can choose optimistic lock.

  • If the read-write ratio gap is not very large, or the system does not respond in time, or the throughput bottleneck, do not use optimistic locks, which increases the complexity and brings additional business risks. You can choose pessimistic lock at this time.

• Implementation of optimistic lock

  • Version number

    • Add a version column to the data table, and increase the value of this column by 1 after each update.

    • When reading data, read out the version number, and compare the version number when updating.

    • If they are the same, update them. If they are different, the data has changed.

    • The user decides how to handle it according to this notification, such as starting over or abandoning this update.

      --Create city table
      CREATE TABLE city(
          id INT PRIMARY KEY AUTO_INCREMENT, -- City ID
      NAME VARCHAR(20), -- city name
      VERSION INT -- version number
      );

      --Add data
      INSERT INTO city VALUES (NULL, 'Beijing', 1),(NULL, 'Shanghai', 1),(NULL, 'Guangzhou', 1),(NULL, 'Shenzhen', 1);

      --Change Beijing to Beijing
      -- 1. Query Beijing version
      SELECT VERSION FROM city WHERE NAME = 'Beijing';
      -- 2. Change Beijing to Beijing, version number + 1. And compare the version number
      UPDATE city SET NAME = 'Beijing', VERSION=VERSION+1 WHERE NAME = 'Beijing' AND VERSION=1;

• Time stamp

  • Basically the same as the version number method, add a column to the data table. The name doesn't matter. The data type needs to be timestamp

  • Insert the latest time into this column after each update.

  • When reading data, read out the time, and compare the time when updating.

  • If they are the same, update them. If they are different, the data has changed.

6. Lock summary

• Table lock and row lock

  • Row lock: the granularity of lock is finer, and the performance loss of row lock is larger. High concurrent processing capability. InnoDB engine supports by default!

  • Table lock: the granularity of the lock is coarse, and the performance loss of adding table lock is small. Low concurrent processing capacity. InnoDB and MyISAM engine support!

• InnoDB lock optimization recommendations

  • Try to complete the data query through the column with index, so as to avoid that InnoDB cannot add row lock and upgrade to table lock.

  • The index shall be designed reasonably. The index shall be as accurate as possible, and the locking range shall be reduced as much as possible to avoid unnecessary locking.

  • Minimize range based data retrieval filtering conditions.

  • Try to control the size of transactions and reduce the amount of locked resources and the length of locked time.

  • In the same transaction, try to lock all resources required at one time to reduce the probability of deadlock.

  • For business parts that are prone to deadlock, you can try to use upgrade locking granularity to reduce deadlock through table level locking