MySQL 實戰 45 講

Backend 讀書會

08/11

15:00~16:30

@Gather Town

DB Lock in Laravel

<?php

// shared lock
Product::whereId(10)->sharedLock()->first();

> SELECT * FROM products WHERE od = 10 FOR SHARE;

// lock for update
Product::whereId(10)->lockForUpdate()->first();

> SELECT * FROM products WHERE od = 10 FOR UPDATE;

  • Shared Lock and LockForUpdate

DB Lock in Laravel

<?php

$product = Product::whereId(10)
    ->lockForUpdate()
    ->first();

if ($product->amount <= 0) {
    $product->amount--;
    $product->save();
}

DB Lock in Laravel

<?php

DB::transaction(function () {
    $product = Product::whereId(10)
        ->lockForUpdate()
        ->first();

    if ($product->amount <= 0) {
        $product->amount--;
        $product->save();
    }
});

Phantom Read

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`), KEY `c` (`c`)
 ) ENGINE=InnoDB;
insert into t values
(0,0,0),(5,5,5),
(10,10,10),(15,15,15),
(20,20,20),(25,25,25);

Phantom Read

begin;
select * from t where d=5 for update;
commit;

  • Will this operation lock the whole table?

Phantom Read

  • Will this operation lock the whole table?

Phantom Read

  • Phantom Read means inconsistent results in the same transaction.
  • ​Consistent Read is default behavior for the transaction.
  • Consistent Read is based on undo log and MVCC.
  • Locking Read is used in transaction locks.
  • ​Locking Read will read all latest records.

Phantom Read

Phantom Read

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
update t set d=100 where d=5;/*所有d=5的行，d改成100*/

Phantom Read

  • If we block all related rows

Phantom Read

  • If we block all related rows

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

update t set d=100 where d=5;/*所有d=5的行，d改成100*/

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

Phantom Read

  • Gap Lock

Phantom Read

  • Next-Key Lock

  • Next-Key Lock

How Does Lock Work?

  • Principles

    • 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

    • 原则 2：查找过程中访问到的对象才会加锁。

    • 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

    • 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

    • 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

  • Next-Key Lock

mysql> insert into t values(30,10,30);

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

  • Next-Key Lock

How Does Lock Work?

Discussion

Made with Slides.com