MySQL8.0的锁定读(for share、for update、NOWAIT、skip locked) 和 非锁定读

I.基础定义

MySQL InnoDB 事务模型中读取分为：
1.Consistent Nonlocking Reads(一致性非锁定读)
2.Locking Reads(锁定读)

II.Consistent Nonlocking Reads(一致性非锁定读)

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time.
简单来说就是我们平时普通的SQL查询

SELECT * FROM `testid` where id =180;

II.Locking Reads(锁定读)

If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried. InnoDB supports two types of locking reads that offer extra safety:
SELECT ... FOR SHARE
SELECT ... FOR UPDATE

普通SELECT 语句无法提供足够的保护。其他事务可以更新或删除您刚刚查询的相同行。额外提供了for share和 for update 两种锁定读。

SELECT * FROM `testid` where id =180 for share ;
SELECT * FROM `testid` where id =180 for UPDATE ;

III.提高锁定读的并发性

If a row is locked by a transaction, a SELECT ... FOR UPDATE or SELECT ... FOR SHARE transaction that requests the same locked row must wait until the blocking transaction releases the row lock.
如果想要锁定读的数据正在被锁定，则当前事务必须阻塞等待，直到其他事务释放行锁。

提供两种方式来避免阻塞：NOWAIT 、SKIP LOCKED

1.NOWAIT 
如果请求的行被锁定，则失败并出现错误。

[Err] 3572 - Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

2.SKIP LOCKED
发现行被锁定，就跳过该条数据。
 

I.实验数据库基础信息

CREATE TABLE `testid` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `user_id` varchar(50) DEFAULT NULL COMMENT '用户Id',
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ;



INSERT INTO `testid` VALUES ('180', 'aaa', '2021-03-01 0