DROP TABLE IF EXISTS `goods`;

CREATE TABLE `goods` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(100) DEFAULT NULL COMMENT '商品名称',

  `stock` int(11) unsigned NOT NULL COMMENT '商品库存',

  `version` int(2) DEFAULT NULL COMMENT '版本号',

  `token_time` datetime NOT NULL COMMENT '乐观锁时间戳',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of goods

-- ----------------------------

BEGIN;

INSERT INTO `goods` VALUES (1, 'product', 9999, 1, '2018-11-30 22:06:20');

COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

package org.yugh.goodsstock.pessimistic_lock.repository;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Repository;

import javax.annotation.Resource;

import java.util.List;

import java.util.Map;

/**

 * @author: YuGenHai

 * @name: SeckRepository

 * @creation: 2018/11/28 00:30

 * @notes: 悲观锁DAO层

 * @notes: 悲观锁需要注意mysql自带自动commit，用行锁需要开启事务 set transation 或者set autocommit =0

 * 防止自动提交，set autocommit =1 自动提交

 */

@Repository

public class PessimisticLockRepository {

    /**

     * 测试使用 {@link JdbcTemplate}

     */

    @Resource

    private JdbcTemplate jdbcTemplate;

    /**

     * 获取现有库存量

     * @param id

     * @return

     * @author yugenhai

     */

    public int queryStock(long id) {

        //开启事务

        String lock = "set autocommit=0";

        jdbcTemplate.update(lock);

        //获得当前库存 并上锁

        String sql = "select * from goods where id=1 for update";

        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);

        if(null != list && list.size() > 0){

            Map<String,Object> map = list.get(0);

            System.out.println("当前库存值： "+ map.get("stock"));

            return Integer.valueOf(String.valueOf(map.get("stock")));

        }

        return 0;

    }

    /**

     * 还有库存量，并且要释放当前锁

     * @author yugenhai

     * @return

     */

    public int updateStock() {

        String update = "update goods set stock=stock-1 where id=1";

        jdbcTemplate.update(update);

        String unlock = "commit";

        jdbcTemplate.update(unlock);

        return 1;

    }

    /**

     * 商品被抢光后需要释放

     * @author yugenhai

     * @return

     */

    public int unlock(){

        String unlock = "commit";

        jdbcTemplate.update(unlock);

        return 1;

    }

}

package org.yugh.goodsstock.pessimistic_lock;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.test.context.junit4.SpringRunner;

import org.yugh.goodsstock.pessimistic_lock.repository.PessimisticLockRepository;

import javax.annotation.Resource;

/**

 * @author: YuGenHai

 * @name: PessimisticLockTest

 * @creation: 2018/11/28 00:32

 * @notes: 悲观锁测试秒杀商品

 */

@RunWith(SpringRunner.class)

@SpringBootTest

public class PessimisticLockTest {

    @Resource

    PessimisticLockRepository pessimisticLockRepository;

    /**

     * STOCK库存总数，测试可以理解为购买者

     * 表里的stock对应库存

     */

    private static final int STOCK = 10000;

    /**

     * 悲观锁秒杀商品

     * @author yugenhai

     */

    @Test

    public void pessimisticLockTest() {

        long beTime = System.currentTimeMillis();

        for (int i = 0; i < STOCK; i++) {

            //获得当前库存

            //顺带上锁，开启事务

            int stock = pessimisticLockRepository.queryStock(1);

            if (stock > 0) {

                //库存还有

                //当前用户继续秒杀一个商品 并提交事务 释放锁

                pessimisticLockRepository.updateStock();

                System.out.println(new Thread().getName() + " 抢到了第 " + (i + 1) + " 商品");

            } else {

                //没有库存后释放锁

                System.err.println(new Thread().getName() + " 抱歉，商品没有库存了！");

                pessimisticLockRepository.unlock();

                //break;

            }

        }

        System.out.println("秒杀 "+ STOCK + " 件商品使用悲观锁需要花费时间：" + (System.currentTimeMillis() - beTime));

    }

}

当前库存值： 8

Thread-9994 抢到了第 9992 商品

当前库存值： 7

Thread-9995 抢到了第 9993 商品

当前库存值： 6

Thread-9996 抢到了第 9994 商品

当前库存值： 5

Thread-9997 抢到了第 9995 商品

当前库存值： 4

Thread-9998 抢到了第 9996 商品

当前库存值： 3

Thread-9999 抢到了第 9997 商品

当前库存值： 2

Thread-10000 抢到了第 9998 商品

当前库存值： 1

Thread-10001 抢到了第 9999 商品

当前库存值： 0

秒杀 10000 件商品使用悲观锁需要花费时间：9922

Thread-10002 抱歉，商品没有库存了！

2018-12-01 00:51:06.914  INFO 9125 --- [       Thread-2] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@f0da945: startup date [Sat Dec 01 00:50:56 CST 2018]; root of context hierarchy

2018-12-01 00:51:06.915  INFO 9125 --- [       Thread-2] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...

2018-12-01 00:51:06.920  INFO 9125 --- [       Thread-2] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.