MySQL lock

1. Concept of lock

• 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 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, using the lock can 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 the database, data is a resource shared and accessed by many users. How to ensure the consistency and effectiveness of data concurrent 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 the data record 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; There will be 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; Small locking granularity, low probability of lock conflict and high concurrency. 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 sentence LOCK 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 of 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. Only after window 1 commits the transaction can it be modified successfully)
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 lock)
UPDATE student SET NAME='Li sisi' WHERE id = 2;

-- modify id The name of Wei 3 is Wang Wuwu(Note: modification failed, InnoDB If the engine does not use indexed columns. It will be promoted to table lock)
UPDATE student SET NAME='Wang Wuwu' WHERE id = 3;

-- Commit transaction
COMMIT;

• Exclusive lock

-- Standard grammar
SELECT sentence 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 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(No problem with general query)
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 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. Only after window 1 commits the transaction can it be modified successfully)
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',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 Add read lock to table
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 pessimistic 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 locks 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, we will judge whether the data has been modified during this period.
  • It needs to be implemented by the user himself. There will be no concurrent preemption of resources. Only when submitting the operation can we check whether it violates the 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 lock, which increases the complexity and brings additional risks to the business. At this time, you can choose pessimistic lock.

• 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 it is the same, update it. If it is different, this data has changed.
    • The user decides how to deal with it according to this notice, such as restarting or abandoning this update.

    -- establish city surface
    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 No+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 executing the update.
    • If it is the same, update it. If it is different, this data has changed.

6. Summary of lock

• 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 suggestions

  • 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.

  • Reduce the filtering conditions of range based data retrieval as much as possible.

  • 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 the 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.