mysql系列之锁

mysql系列之锁

MyISAM - 表级锁，InnoDB 支持行锁。

锁的类型

• 共享锁 -S Lock， 允许事务读一行数据
• 排他锁 - X Lock, 允许事务删除或更新一行数据

S与S兼容，X与任何锁都不兼容。

还有意向锁intentionlock: IX ,IS - check mongodb wiredtiger 意向锁

一致性非锁定读

通过MVCC 多版本控制来读取当前执行时间数据库中的行数据。如果读取的数据行正在执行delete或者update，这时读操作不会等待锁的释放，会读取行的一个快照。

快照是指该行之前版本，通过undo ( 记录事务中回滚数据)。

innoDB 默认读取方式。

但不同隔离级别，读取方式不同：

• read commited： 读取最新快照

• repeatable read： 读取事务开始时的行数据。

一致性锁定读

读取显式加锁

• SELECT ... FOR UPDATE (加X锁)
• SELECT ... LOCK in SHARE MODE （加S锁）

锁的算法

• record lock ，单个行记录上的锁
• gap lock， 间隙锁，锁定一个范围
• next-key lock： 解决phantom problem 锁record + gap 锁

phantom problem

同一事务，连续执行两次同样的sql返回结果不同。

create table t (a INT primary key); t 有1，2，5三个值。

事务T1： select * from t where a > 2 for update; 返回5； 此时另一个事务插入了4并提交，此时T1 再次查询查到4 和5。

next-key locking: 锁住 大于2 这个范围，这个范围任何插入都不允许。

锁的问题

脏读

事务没提交，一个事务可以读取到另一个未提交事务的数据，违反了隔离性。

脏读发生的条件是隔离级别为 read uncommitted， 生产环境较少见，innoDB 默认隔离是read repeatable

不可重复读取

一个事务内多次读取，事务没结束时，另外一个事务也访问同一数据并做了DML，导致第一个事务的两次读到的数据不一样。（区别： 不可重复读读到是已提交的数据，不同于脏读读到的是未提交的数据）

事务A，第一次读取到1， 此时另一个事务B开始，插入了2并提交事务，事务A再次读取，会看到1和2. 如果事务隔离级别不允许重复读，使用next-key lock 锁范围。

但一般不可重复度可以接受，因为读到的是已经提交数据。

丢失更新

一个事务的更新会被另一个事务的更新覆盖。

例子： 用户账户10000元，使用两个网银转账，第一次转9000执行较慢，同时另一个网银转1元，余额更新覆盖为999。

因此需要将操作变为串行化，读取时加排他锁 (select for update)。

阻塞

事务中锁等待另一个事务的锁释放。

innodb_lock-wait_timeout 控制等待超时时间，默认超时不会自动回滚。

死锁

两个或两个以上的事务在执行过程中，争夺资源互相等待。

串行不会死锁，死锁只存在于并发情况。

AB-BA 死锁，innodb回滚一个事务。

---

MVCC

数据库并发：

• 读读
• 读-写，隔离性问题，读到未提交事务的数据，幻读
• 写-写，丢失更新

解决冲突，锁-可能有死锁； 或多版本并发控制（MVCC）解决读写冲突的无锁并发控制。

锁： 2PL （两段锁）

MVCC： 为事务分配单向增长的时间戳，保存版本，版本与事务时间戳相关，读操作只读取快照。读被写阻塞。

乐观并发控制（OCC）： 解决写写冲突，假定事务间竞争没那么多，先修改，提交事务前，检查事务开始后有没有新更新提交，如果有就放弃并重试。类似自旋锁，解决低并发，冲突少的场景。

多版本控制可以结合锁+MVCC，或MVCC+OCC 。

InnoDB 只可以对读无锁，对写仍然是悲观锁。每行数据表中保留一份，更新时上行锁，同时旧版数据写undolog；表和undo log 中行数据都记录事务ID，检索是，读取来自当前事务已提交事务的行数据。

---

乐观锁与悲观锁

并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

悲观锁

Pessimistic Concurrency Control （PCC）

修改前加排它锁：

• 加锁失败，说明该记录正在被修改，可以决定如何处理，等待或抛出异常
• 加锁成功，执行修改，释放锁

//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;

select…for update 添加排它锁，id为1的记录添加行锁。

注意： 行锁是基于索引的，如果一条sql 不使用索引，则添加表锁

缺点： 影响并发。

乐观锁

Optimistic Concurrency Control （OCC），假定冲突不会发生，只有在数据提交更新时才会正式对数据冲突进行检测。

可以通过记录版本（或时间戳）

读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

CAS（内存位置（V）、预期原值（A）和新值(B)） 的思想， 适用于数据竞争小的场景

Optimistic Locking is not a database feature, not for MySQL nor for others: optimistic locking is a practice that is applied using the DB with standard instructions.

场景：

1. SELECT data from a row having one ID field (iD) and two data fields (val1, val2)
2. optionally do your calculations with data
3. UPDATE data of that row

no locking

- SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId;
 - {go on with your other code}

OPTIMISTIC LOCKING

notes： without transaction

- SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId
           AND val1 = @oldVal1
           AND val2 = @oldVal2;
 - {if AffectedRows == 1 }
 -     {go on with your other code}
 - {else}
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

关键点在于： update会比较之前读到的值和当前db存储的值，判断affected rows 可以知道这期间是不是有修改已经发生。

Transaction Way

- SELECT iD, val1, val2
      FROM theTable
      WHERE iD = @theId;
- {code that calculates new values}
- BEGIN TRANSACTION;
- UPDATE anotherTable
      SET col1 = @newCol1,
          col2 = @newCol2
      WHERE iD = @theId;
- UPDATE theTable
      SET val1 = @newVal1,
          val2 = @newVal2
      WHERE iD = @theId
          AND val1 = @oldVal1
          AND val2 = @oldVal2;
- {if AffectedRows == 1 }
-     COMMIT TRANSACTION;
-     {go on with your other code}
- {else}
-     ROLLBACK TRANSACTION;
-     {decide what to do since it has gone bad... in your code}
- {endif}

trx方式是在collision被发现时，可以rollback 整个操作； 而不带trx的需要自己决定如果处理collision，包括如何rollback，rollback的范围。

Versioning OPTIMISTIC LOCKING

- SELECT iD, val1, val2, version
      FROM theTable
      WHERE iD = @theId;
- {code that calculates new values}
- UPDATE theTable
      SET val1 = @newVal1,
          val2 = @newVal2,
          version = version + 1
      WHERE iD = @theId
          AND version = @oldversion;
- {if AffectedRows == 1 }
-     {go on with your other code}
- {else}
-     {decide what to do since it has gone bad... in your code}
- {endif}

可以使用一个单独的字段（version），读取时读取version，更新时比较version，更新可以执行时同时更新version 。

https://stackoverflow.com/questions/17431338/optimistic-locking-in-mysql

---

https://mp.weixin.qq.com/s/EA_DsTQ17muMQXekmuZrZQ

https://mp.weixin.qq.com/s/Za4SArS7iAq7YiXgyKSeHA

https://www.zhihu.com/question/27876575/answer/71836010