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
MySQL 實戰 45 講 - Backend 讀書會
By Albert Chen
