Mysql read-write lock nanny-level graphic tutorial

Abstract: read lock will block write, but will not block read, and write lock will block Du Xiu.

This article is shared from the HUAWEI CLOUD community " Mysql nanny-level read-write lock graphic tutorial 丨 [Bloom! Database] ", author: Code Pipi Shrimp.

Prepare

创建mylock表
CREATE TABLE `mylock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
【手动增加表锁】 lock table 表名字1 read(write)，表名字2 read(write)，其它;
【查看表上加过的锁】  show open tables; 
【释放表锁】unlock tables;

Read lock

Read lock (shared lock): For the same piece of data, multiple read operations can be performed at the same time without affecting each other.

Shared locks are also called read locks, or S locks for short. As the name suggests, shared locks are the same data that can be shared by multiple transactions, and all of them can access the data, but they can only be read but cannot be modified.

1. Run show open tables, it can be seen that In_use is all 0, which means no lock has been added, and 1 means there is a lock.

2. After running lock table mylock read and lagu write to lock, show open tables shows that the lock is successful.

3. Open two queries, add read lock lock table mylock read in session1

All can be queried successfully.

4. Reading any table in session2 will not be affected

5. It does not work to query other tables or update tables in session1.

6. Update the table in session2, it will block.

Write lock

Write lock (exclusive lock): Before the current write operation is completed, it will block other write locks and read locks.

Exclusive locks are also called write locks, or X locks for short. As the name implies, exclusive locks cannot coexist with others. For example, if a transaction acquires an exclusive lock on a data row, other transactions can no longer acquire other locks on the row, including shared locks. And exclusive lock, but the transaction that obtains the exclusive lock can read and modify the data.

First unlock tables to release the lock, and then run lock table mylock write in session1 to add write lock

1. Successfully updated in session1

But the query fails

2. Query in session2 will block

Summarize

Mylsam will automatically add read locks to all tables involved before executing the query statement (select), and will automatically add write locks to the tables involved before performing addition, deletion, and modification operations.

MYSQL table-level lock has two modes:
1. Table Read Lock (Table Read Lock)
2. Table Write Lock (Table Write Lock)

in conclusion:

1. Reading the MyLSAM table (adding a read lock) will not block other threads' read requests to the same table, but will block write requests to the same table. Only when the read lock is released, will other processes' writes be performed operate.

2. The read operation (add read lock) to the MyLSAM table will block other threads from reading and writing the same table. Only when the write lock is released, will the read and write operations of other processes be performed.

In short, the read lock will block writing, but it will not block reading, and the write lock will block all Du Xiu.

Click to follow, and learn about Huawei Cloud's fresh technology for the first time~