mysql innodb悲观乐观_innodb 悲观锁,乐观锁

CREATE TABLE `products` (

`id`int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(256) NOT NULL,

`quantity`intNOT NULL,

`cityid` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_of_cityid` (`cityid`,`id`)

) ENGINE=InnoDB;

insert into products (name,quantity,cityid) values ('牙刷',10, 1);

insert into products (name,quantity,cityid) values ('大米',10,2);

insert into products (name,quantity,cityid) values ('豆角',10,3);

insert into products (name,quantity,cityid) values ('苹果',10,4);

不安全的做法:

SELECT quantity FROM products WHERE id=3;

UPDATE products SET quantity = quantity -1 WHERE id=3;

第一个访问者                                                                                第二个访问者

A)SELECT quantity FROM products WHERE id=3;

C)SELECT quantity FROM products WHERE id=3;

B)UPDATE products SET quantity = quantity -1 WHERE id=3;

D)UPDATE products SET quantity = quantity -1 WHERE id=3;

假设两次访问前quantity数量为10

第一个访问者执行select后,得到 quantit7=10,

第二个访问者到达,由于CPU时间片分配,将控制权给了第二个访问者,通过select,得到quantity=10

再次时间片轮循,第一个访问者执行B SQL后,quantity=9

第二个访问者执行D SQL 后,quantity仍然为9,因为第二个访问者并不知道第一个访问者的存在, 这就出问题了

解决方法

最简单的就是加 悲观锁, 缺点:若锁的时候过长，其他用户无法访问,影响并发性,加锁，会增加额外开销

或者应用层利用乐观锁, 并发大的情况下较好，避免加锁

第一个访问者                                                                                第二个访问者

A)SELECT quantity FROM products WHERE id=3 for update;

C)SELECT quantity FROM products WHERE id=3 for update;

B)UPDATE products SET quantity = quantity -1 WHERE id=3;

D)UPDATE products SET quantity = quantity -1 WHERE id=3;

sql执行如下

A窗口

此时,在B窗口,再执行一遍select

被锁住, for update 悲观锁,也称为排他锁,不允许他人读/写

A窗口,执行commit

此时B窗口

即可看见 id=1的记录

另外,当select ... where for  update 中的where条件不是主键,哪怕是其他索引时,也会锁表,

A窗口

B窗口

当A窗口，commit后,B窗口者返回数据, 尽管cityid为索引,但也发生了表锁

当为主键时,才行锁

A窗口,不commit

B窗口取数据,马上返回数据

二.乐观锁

在表中加一个列 version, update更新后就加1

在访问前假设  version=10

第一次访问                                                   第二次访问

A) select quantity, version from products;

C) select quantity, version from products;

B) update products set version=version+1,quantity=quantity-1 where version=10　and id=1

D) update products set version=version+1,quantity=quantity-1 where version=10 and id=1

当第一个访问者执行B后,version已由10,变成了11

当第二个访问者执行D后,找不到version=10的记录,影响的记录为0,需要重试几次