乐观锁和悲观锁

乐观锁和悲观锁

悲观锁与乐观锁是两种常见的资源并发锁设计思路，也是并发编程中一个非常基础的概念。

• 悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
• 乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
• 两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

悲观锁和乐观锁大部分场景下差异不大，一些独特场景下有一些差别，一般我们可以从如下几个方面来判断：

• 响应速度：如果需要非常高的响应速度，建议采用乐观锁方案，成功就执行，不成功就失败，不需要等待其他并发去释放锁
• 冲突频率：如果冲突频率非常高，建议采用悲观锁，保证成功率，如果冲突频率大，乐观锁会需要多次重试才能成功，代价比较大
• 重试代价：如果重试代价大，建议采用悲观锁

【备注】以上述商品扣减为例，悲观锁和乐观锁的实现分别为：

悲观锁实现

要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。我们可以使用命令设置MySQL为非autocommit模式【即使用事务】：

           set autocommit=0;
           设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
           //开始事务
           begin;/begin work;/start transaction; (三者选一就可以)
           //查询出商品信息
           select status from items where id=10000 for update;
           //根据商品信息生成订单
           insert into orders (id,item_id) values (null,10000);
           //修改商品status为2
           update items set status=2 where id=10000;
           //提交事务
           commit;/commit work;

注：上面的begin/commit为事务的开始和结束，因为在前一步我们关闭了mysql的autocommit，所以需要手动控制事务的提交，在这里就不细表了。

乐观锁实现

以mysql InnoDB存储引擎为例，还是拿之前的例子商品表items表中有一个字段status，status=1表示该商品未被下单，status=2表示该商品已经被下单，那么我们对每个商品下单前必须确保此商品的status=1。假设有一件商品，其id为10000；

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

当然了，乐观锁也是要精心挑选的，主要的目的就是避免锁的失败率过高又要规避ABA问题。关于锁力度太大导致接口操作失败率过高。
商品库存扣减时，尤其是在秒杀、聚划算这种高并发的场景下，若采用version号作为乐观锁，则每次只有一个事务能更新成功，业务感知上就是大量操作失败。
若挑选以库存数作为乐观锁

update item
set
    quantity=quantity-#sub_quantity#
where
    item_id = #id#
    and quantity-#sub_quantity# > 0

通过挑选乐观锁，可以减小锁粒度，从而提升吞吐～
乐观锁需要灵活运用,现在互联网高并发的架构中，受到fail-fast思路的影响，悲观锁已经非常少见了。

for Update详解

举个例子: 假设商品表单products 内有一个存放商品数量的quantity ，在订单成立之前必须先确定quantity 商品数量是否足够(quantity>0) ，然后才把数量更新为1。
不安全的做法:

SELECT quantity FROM products WHERE id=3; 
UPDATE products SET quantity = 1 WHERE id=3;

为什么不安全呢?
少量的状况下或许不会有问题，但是大量的数据存取「铁定」会出问题。
如果我们需要在quantity>0 的情况下才能扣库存，假设程序在第一行SELECT 读到的quantity 是2 ，看起来数字没有错，但是当MySQL 正准备要UPDATE 的时候，可能已经有人把库存扣成0 了，但是程序却浑然不知，将错就错的UPDATE 下去了。
因此必须透过的事务机制来确保读取及提交的数据都是正确的。
于是我们在MySQL 就可以这样测试:

SET AUTOCOMMIT=0; 
BEGIN WORK; 
SELECT quantity FROM products WHERE id=3 FOR UPDATE;
// 此时products 数据中id=3 的数据被锁住(注3)，其它事务必须等待此次事务 提交后才能执行
// `SELECT * FROM products WHERE id=3 FOR UPDATE` 如此可以确保quantity 在别的事务读到的数字是正确的。
UPDATE products SET quantity = 1 WHERE id=3 ; 
COMMIT WORK;

【备注】基于spring中的代码中，如何模拟上面的机制扣减商品库存呢？

更多内容详见demo，user表在各类情况下的作用,如下：
user表内容如下：

CREATE TABLE `users` (
  `id` bigint(20) auto_increment NOT NULL COMMENT '主键',
  `phone` varchar(100) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='user表';
insert into `users`(`id`,`phone`) values(1, '111111');
alter table users engine=innodb;
show table status from bimowu where name='users';

demo使用的sql如下：

    例1(MyISAM)
    B:
    select *,sleep(50) from users;
    A:
    UPDATE users SET phone='2222222' WHERE id =1;
    例2(MyISAM)
    A:
    UPDATE users SET phone='222222211' WHERE id =1 AND SLEEP(50)=0;
    B:
    select * from users;
    例1(InnoDB)
    B:
    select *,sleep(50) from users;
    A:
    UPDATE users SET phone='2222222' WHERE id =1;
    例2(InnoDB)
    A:
    UPDATE users SET phone='2222222-22' WHERE id =1 AND sleep(50)=0;
    B:
    select * from users;
    例3(查询结果是update前的结果)
    B:
    select *,sleep(10) from users;
    A:
    UPDATE users SET phone='222222233' WHERE id =1;
    例4(B查不到未提交的A的结果,但B的update语句却可以)
    A:
    set autocommit=0;
    UPDATE users SET phone='222222244' WHERE id =1;
    B:
    set autocommit=0;
    select * from users;
    UPDATE users SET phone='222222255' WHERE id =1;
    例4`(B的update条件中可以感知A未提交的update数据)
    A:
    set autocommit=0;
    UPDATE users SET phone='222222244' WHERE id =1;
    B:
    set autocommit=0;
    select * from users;
    UPDATE users SET phone='222222255' WHERE phone='222222255';
    UPDATE users SET phone='222222255' WHERE phone='222222244';
    例4``(B的select for update同update一样，同理insert和delete也与update相同)
    A:
    set autocommit=0;
    UPDATE users SET phone='222222244' WHERE id =1;
    B:
    set autocommit=0;
    select * from users where id=1 for update;

sql复杂语句复习

mysql规范：

• in里面最好不要有完成的语句，因为mysql嵌套查询效率很差【如果开发代码：可以用代码逻辑实现，先把in里面的语句执行，然后把值放在in里面】
• not in最好不要使用
• 前台最好不要有3张以上的关联表，后台除外

用户表:tran_account
主要字段：account_id,mobile_no,username,card_name（不为空则绑卡），create_time

用户渠道表:tran_account_ext_info
主要字段：account_id,channel_id

产品表：product_info
主要字段：product_id,product_name,product_period

订单表：tran_order
主要字段：order_money,product_id,account_id,status(支付状态，20表示成功),create_time

查看特定渠道注册并且未绑卡（即card_name为空）的用户：

select * FROM tran_account a, tran_account_ext_info e WHERE e.channel_id in (‘C01020003’)
AND a.CREATE_TIME<='2016-07-01 23:59:59' and a.create_time >= '2016-07-01 00:00:00')
AND a.CARD_NAME IS NOT NULL;

查看特定渠道C01020003特定时间段购买的金额总数：

SELECT SUM(`ORDER_MONEY`) FROM tran_order WHERE account_id IN(SELECT `account_id` FROM tran_account where ACCOUNT_ID IN
    (SELECT user_id FROM tran_account_ext_info WHERE channel_id in (‘C02300002’,’C02200102’)
    AND CREATE_TIME<='2016-06-30 23:59:59' and create_time >= '2016-06-30 00:00:00')
    AND CARD_NAME IS NOT NULL);

计算某段时间按照用户汇总的年华购买金额（比如买的是90天的产品，则年华金额为购买金额/4,产品可能有30天，90天，180天和365天的产品） ：

SELECT SUM(b.order_amount) amount,SUM(b.ORDER_MONEY) order_money,b.account_id,ta.MOBILE_NO,ta.USER_NAME FROM
(
SELECT o.product_id,t.PRODUCT_NAME,t.PERIOD,
CASE
WHEN t.PERIOD=365 THEN o.ORDER_MONEY
WHEN t.PERIOD=180 THEN o.ORDER_MONEY/2
WHEN t.PERIOD=90 THEN o.ORDER_MONEY/4
WHEN t.PERIOD=30 THEN o.ORDER_MONEY/12
END order_amount,o.ORDER_MONEY order_money,o.ACCOUNT_ID
FROM tran_order o ,tran_product_info t
WHERE o.product_id=t.product_id AND o.CREATE_TIME >=’2016-09-01 00:00:00’  and o.create_time <= ‘2016-10-01’  AND o.ORDER_STATUS=20)
) b,tran_account ta
WHERE ta.ACCOUNT_ID = b.account_id
GROUP BY b.account_id