There are so many locks in MySQL, I don't know where to start, reading this article is enough

MySQL has two core knowledge points, indexes and locks. The previous articles have explained the MySQL index implementation mechanism in detail. Today, let's learn about MySQL locks together.

1 Why lock?

When multiple transactions operate the same batch of data concurrently, without locking, the isolation of transactions cannot be guaranteed, resulting in data confusion.

Locking is to ensure the correctness of data under concurrent operations.

2 What are the classifications of locks?

According to the granularity of locks, it can be divided into: table locks, page locks, row locks, record locks, gap locks, and near-key locks

According to the attributes of the lock, it can be divided into: shared lock, exclusive lock

According to the locking mechanism, it can be divided into: optimistic locking, pessimistic locking

The following describes these locks in turn:

Table lock:

Both MyISAM and InnoDB engines support table locks.

Advantages: low overhead, fast locking, and no deadlock.

Disadvantages: The locking strength is high, the probability of lock conflict is high, and the concurrency is the lowest.

Locking method:

 # 对user表加读锁
lock table user read;
# 同时对user表加读锁，对order表加写锁
lock tables user read, order write;

When do you need a table lock?

1. When most of the data in the table needs to be updated
2. The transaction involves multiple tables, and the business logic is complex. Adding table locks can avoid deadlocks.

Page lock:

Advantages: Overhead and locking speed are between table locks and row locks.

Disadvantages: deadlocks will occur, the locking granularity is between table locks and row locks, and the degree of concurrency is general.

Currently, only the BDB engine supports page locks, and there are few application scenarios.

row lock:

Only the InnoDB engine supports row locks, and locks are added to indexes.

Advantages: High overhead, slow locking; deadlocks may occur.

Disadvantages: small locking granularity, low probability of lock conflict, and high concurrency.

In addition, record locks, gap locks, and near-key locks are all row locks.

Record Locks:

That is, lock a record.

 # 对id=1的用户加锁
update user set age=age+1 where id=1;

Gap Locks:

That is, a range is locked, but the critical data of the range is not included.

 # 对id大于1并且小于10的用户加锁
update user set age=age+1 where id>1 and id<10;

The lock range of the above SQL is (1,10).

Next-Key Locks:

It consists of record locks and gap locks, including both the record itself and the range, with left open and right closed intervals.

 # 对id大于1并且小于等于10的用户加锁
update user set age=age+1 where id>1 and id<=10;

Shared lock (also known as read lock, S lock):

Role: Prevent other transactions from modifying the current data.

Locking method:

Add the lock in share mode keyword at the end of the select statement.

 # 对id=1的用户加读锁
select * from user where id=1 lock in share mode;

Exclusive lock (also known as write lock, X lock):

Role: Prevent other transactions from reading or updating the current data.

Locking method:

Add the for update keyword at the end of the select statement.

 # 对id=1的用户加写锁
select * from user where id=1 for update;

Optimistic locking:

It is always assumed that others will not modify the current data, so every time the data is read, it will not be locked, but when the data is updated, the version is used to judge whether others have modified the data. The classes under the atomic package of Java use optimistic locking. (CAS) implemented.

It is suitable for scenarios with more reading and less writing.

Locking method:

1. read version

    select id,name,age,version from user id=1;

2. Update the data to determine whether the version has been modified.

    update user set age=age+1 where id=1 and version=1;

Pessimistic lock:

Always assume that others will modify the current data, so every time you read it, always lock it.

It is suitable for scenarios with more writing and less reading.

Locking method:

 # 加读锁
select * from user where id=1 lock in share mode;
# 加写锁
select * from user where id=1 for update;

Summary of the knowledge points of this article:

The article is continuously updated, and you can search for "One Light Architecture" on WeChat to read more technical dry goods as soon as possible.