Examples to explain shared locks and exclusive locks under MySQL and InnoDB

This article brings you relevant knowledge about shared locks and exclusive locks in mysql. I hope it will be helpful to everyone.

Shared Lock

Shared lock, S lock, read lock , are all his names.

And I like to call him Shared Read Lock.

A shared (S) lock permits the transaction that holds the lock to read.

#A shared lock allows the transaction holding the lock to read.

The sharing here is, Read and read sharing .

That is to say, whether it is row level or table level, if a shared read lock is placed on certain data, Other transactions can continue to read (that is, shared read locks are allowed to be held) , but cannot be written, that is, reading and writing are mutually exclusive.

By the way, let me introduce how to add a shared lock (shared read lock):

Upper table-level shared lock, that is, table-level shared read lock:

select  *  from table（表） lock in share mode ；

Upstream-level shared lock , that is, row-level shared read lock:

select  *  from table（表）where id = 10  
lock in share mode
 ；

Let’s be a bit more verbose here. Note that under InnoDB, you don’t just use row locks if you want to use row locks. Let’s review the triggering conditions of row locks again ( Mentioned at the beginning):

Exclusive Lock

Exclusive lock, write lock, X lock , are all his names.

And I like to call him Exclusive write lock.

An exclusive (X) lock permits the transaction that holds the lock to update or delete.

Exclusive (X) locks allow the transaction holding the lock to update or delete.

Exclusive, this word. Have you ever played basketball? I didn’t know how to play basketball in junior high school. I would not pass the ball while I was holding it. My classmates said to me, you are so lonely.

Yes, I am very alone. Just like this exclusive write lock (exclusive lock), it is very unique.

When a transaction adds an exclusive write lock (exclusive lock) to certain data, only the current transaction can modify or delete the data.

Other transactions cannot be read or written. Because this lock is very unique, you must wait until this very unique lock is used up (released) before other transactions can take advantage of it.

So, the exclusive write lock (exclusive lock) is mutually exclusive for reading and writing, and mutually exclusive for writing and writing.

By the way, let me introduce how to add an exclusive lock (exclusive write lock):

Upper table-level exclusive lock, that is, table-level exclusive write lock:

select * from table
 for update 
；

Upstream-level exclusive lock , that is, row-level exclusive write lock:

select * from table where id =10 
 for update
 ；

Let me be a little more verbose here. Note that under InnoDB, you don’t just use row locks if you want to use row locks. We will again trigger the row lock conditions. To review (mentioned at the beginning):

The above sql can achieve an upstream-level exclusive lock because it hits the index, and id is the index.

Perhaps after seeing this, you are still vague about shared locks & exclusive locks. You roughly know what read-read sharing, read-write mutual exclusion, write-write mutual exclusion and so on.

So, we need to look at these two locks from God’s perspective again,

Red Transaction Operation 1

Blue Transaction Operation Two

那么如果你看到这里，还是对 共享锁 & 排他锁还只是云里雾里。

那我只有动手了!

实战介绍，演示 所谓的读读共享、读写互斥、写写互斥 。

在演示读读共享、读写互斥、写写互斥前， 我必须点明一点！

在这篇文章里面，我介绍了一些上 共享锁（共享读锁）、排他锁（独占写锁）的方式 。 但是 可以看到写的查询sql 都是后面加了东西的 ， lock in share mode ，for update .... 等。

所以我想点明的一点是，

如果是使用 普通的查询 ，是 什么锁都没上的！

就好像平时我们经常写的

select * from table ；
select * from table where age=18；

select语句默认不会加任何锁类型

select语句默认不会加任何锁类型

select语句默认不会加任何锁类型

而排他锁，除了 select .... for update ,InnoDB引擎 默认的修改 、插入、删除（update,insert,delete）都是会给操作的相关数据 加 排他锁的 .

废话不多说，我们上才艺：

准备一些用于测试的数据。

建表：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `sex` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

搞点模拟数据：

（id主键索引）

第一个小实践：

我们不废话，我们直接上共享读锁， 看看是不是能 符合刚才我们的理论 读读共享，读写互斥！

1. 我们先给id=3这数据上个 共享读锁：

2.基于当前状况， 我们再执行一下查询语句，也是使用共享读锁的：

3.那么也是基于当前情况，我们再执行一下使用排他写锁的查询语句，可以发现 读写互斥了：

4.验证下，我们查看当前是否存在事务在等待锁：

可以从结果中看出 事务请求id 34847在等待锁：

我们再查询一下，那些事务在使用锁，
可以从结果看出，34844事务在使用S锁，也就是共享读锁；

而34847事务 在使用 X锁， 也就是排他写锁（但是由于共享读锁先上了，所以读写互斥了），所以造成了34847事务 在等待锁。

5.那么如果我们一直不 COMMIT 共享读锁， 34847事务 会永无止息地等待锁吗？   那么肯定是不可能允许这种一直等待的场景的：

所以mysql会有个等待锁资源超时的机制，这种情况就会直接返回查询失败的结果。

根据第一个小实践，我们得出一个很明显的结论：

当某数据上了 共享读锁 S 时， 只允许其他事务上共享读锁 S， 因为读读共享；

不允许其他事务上 独占写锁 X（除非把这个共享读锁S 释放掉），因为读写互斥。

第二个小实践：

1.我们直接给某行数据上个排他写锁 X (注意我们的事务是没有执行COMMIT的) ：

2. 我们接下来去 通过共享读锁去获取数据，看看会发生什么？

这就是 独占写锁 X 的 读写互斥、写写互斥 （写写互斥的场景就不展示了）.

再验证下，我们看下是不是存在事务在等待锁资源：

3. So if the exclusive write lock is never released, will other transactions keep waiting?

The same is true, it will wait for the timeout to return the query failure:

Add a little practice:

1. Still the same, first upload a certain data Exclusive write lock, no COMMIT:

2. Execute ordinary query, select:

You can see, Ordinary select statements can be obtained normally, why? Because we mentioned it earlier:

So I have to elaborate again. The so-called read-read sharing, read-write mutual exclusion, and write-write mutual exclusion are all for lock resources. That said, if you don't compete for lock resources, then there must be no mutual exclusion or mutual exclusion.

Recommended learning: mysql video tutorial

The above is the detailed content of Examples to explain shared locks and exclusive locks under MySQL and InnoDB. For more information, please follow other related articles on the PHP Chinese website!