表结构

CREATE TABLE `stock` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
  `count` int(11) NOT NULL COMMENT '库存',
  `sale` int(11) NOT NULL COMMENT '已售',
  `version` int(11) NOT NULL COMMENT '乐观锁，版本号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
CREATE TABLE `stock_order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sid` int(11) NOT NULL COMMENT '库存ID',
  `name` varchar(30) NOT NULL DEFAULT '' COMMENT '商品名称',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=981 DEFAULT CHARSET=utf8

一、无锁的代码以及引发的问题

会产生“超卖”现象

sql语句

1、查询  select count from stock where id = 1;
2、更新  update stock set count = count - 1 where count > 0;
3、创建  insert into stock_order(sid,name) VALUES (1,'aaa');

public class Test1 {
    public static void main(String[] args) throws SQLException, InterruptedException {
        Test2 tests = new Test2();
        Thread[] threads = new Thread[100];
        for (int i=0;i<100;i++){
            threads[i] = new Thread(){
                @Override
                public void run() {
                    try {
                        tests.service();
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
            };
        }
        for (int i=0;i<100;i++){
            threads[i].start();
        }
    }
    public void service() throws Exception {
        Connection connection = dbUtils.getConnection();
        String selectSql = "select count from stock where id = 1";
        PreparedStatement statement1 = connection.prepareStatement(selectSql);
        ResultSet resultSet = statement1.executeQuery();
        resultSet.next();
        String count = resultSet.getString("count");
        System.out.println(count);
        int c = Integer.parseInt(count);
        Thread.sleep(10);
        if (c<1)
            throw new Exception();
        String updateSql = "update stock set count = count - 1 where count > 0";
        PreparedStatement preparedStatement = connection.prepareStatement(updateSql);
        int update = preparedStatement.executeUpdate();
        String insertSql = "insert into stock_order(sid,name) VALUES (1,'aaa')";
        PreparedStatement statement = connection.prepareStatement(insertSql);
        int insert = statement.executeUpdate();
    }
}

从上述代码可以看到，有一百个线程去模拟一百个用户购买商品，数据库中只有10个商品，所以当商品卖完时，应该增加10条购买记录。为了让大家看个清楚，我在代码中加入了线程的睡眠。

我们看到，增加了11条卖出记录，也就是所谓的超卖现象，商家绝不可能允许这种情况的发生。

二、乐观锁以及引发的问题

缺点：需要在数据库表中调加version列，如果前期没有冗余，需要采用扩展字段进行设计，并发版本控制号

我们在使用乐观锁时会假设在极大多数情况下不会形成冲突，只有在数据提交的时候，才会对数据是否产生冲突进行检验。如果数据产生冲突了，则返回错误信息，进行相应的处理。

实现：MySql最经常使用的乐观锁时进行版本控制，也就是在数据库表中增加一列，记为version，当我们将数据读出时，将版本号一并读出，当数据进行更新时，会对这个版本号进行加1，当我们提交数据时，会判断数据库表中当前的version列值和当时读出的version是否相同，若相同说明没有进行更新的操作，不然，则取消这次的操作。

sql语句

1、查询  select * from stock where id = 1;
2、更新  update stock set count = count -1,version = version + 1 where version = " + stock.getVersion();
3、创建  insert into stock_order(sid,name) VALUES (1,'aaa');

public class Test2 {
    public static void main(String[] args) {
        Test test = new Test();
        Thread[] threads = new Thread[200];
        for (int i=0;i<200;i++){
            int finalI = i;
            threads[i] = new Thread(){
                @Override
                public void run() {
                   test.service();
                }
            };
        }
        for (int i=0;i<200;i++){
            threads[i].start();
        }
    }
    public void service(){
        try {
            Connection connection = dbUtils.getConnection();
            Stock stock1 = checkStock(connection);
            updateCountByOpti(connection,stock1);
            createOrder(connection);
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }
 
    private void createOrder(Connection connection) throws SQLException {
        String insertSql = "insert into stock_order(sid,name) VALUES (1,'aaa')";
        PreparedStatement statement = connection.prepareStatement(insertSql);
        int insert = statement.executeUpdate();
    }
    private void updateCountByOpti(Connection connection,Stock stock) throws SQLException {
        String sql = "update stock set count = count -1,version = version + 1 where version = " + stock.getVersion();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int update = preparedStatement.executeUpdate();
        if (update==0)
            throw new RuntimeException("没抢到");
    }
    public Stock checkStock(Connection connection) throws SQLException {
        String sql = "select * from stock where id = 1";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        Stock stock = null;
        if (resultSet.next()){
            stock = new Stock();
            stock.setId(resultSet.getInt("id"));
            stock.setName(resultSet.getString("name"));
            stock.setCount(resultSet.getInt("count"));
            stock.setSale(resultSet.getInt("sale"));
            stock.setVersion(resultSet.getInt("version"));
        }
        if (stock.getCount()<1)
            throw new RuntimeException("没有库存了");
        return stock;
    }
}

三、悲观锁

   悲观锁的定义
    1、在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）
    2、其是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。
    3、如果一个事务执行的操作都对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。

悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

   MySql的悲观锁就是打开事务，当启动事务时，如果事务中的sql语句涉及到索引并用索引进行了条件判断，那么会使用行级锁锁定所要修改的行，否则使用表锁锁住整张表。

sql语句

1、查询  select * from stock where id = 1 for update;
2、更新  update stock set count = count - 1 where count > 0;
3、创建  insert into stock_order(sid,name) VALUES (1,'aaa');

public class Test {
    public static void main(String[] args) {
        Test test = new Test();
        Thread[] threads = new Thread[200];
        for (int i=0;i<200;i++){
            threads[i] = new Thread(){
                @Override
                public void run() {
                    try {
                        test.service();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            };
        }
        for (int i=0;i<200;i++){
            threads[i].start();
        }
    }
        public void service() throws SQLException {
            Connection connection = null;
            try {
                connection = dbUtils.getConnection();
                connection.setAutoCommit(false);
                Stock stock1 = checkStock(connection);
                updateCountByOpti(connection,stock1);
                createOrder(connection);
                connection.commit();
            }catch (Exception e){
                System.out.println(e.getMessage());
                connection.rollback();
            }
        }
 
        private void createOrder(Connection connection) throws SQLException {
            String insertSql = "insert into stock_order(sid,name) VALUES (1,'aaa')";
            PreparedStatement statement = connection.prepareStatement(insertSql);
            int insert = statement.executeUpdate();
        }
        private void updateCountByOpti(Connection connection,Stock stock) throws SQLException {
            String sql = "update stock set count = count -1,version = version + 1 where version = " + stock.getVersion();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            int update = preparedStatement.executeUpdate();
            if (update==0)
                throw new RuntimeException("没抢到");
        }
 
        public Stock checkStock(Connection connection) throws SQLException, InterruptedException {
        // 这段sql是关键，通过select for update 指定主键，通过行锁对本次查询加上悲观锁，
        //如果用其他人占用着这个锁，本次query hold住，等待锁资源释放            
        //String sql = "select * from stock where id = 1 for update";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            Stock stock = null;
            if (resultSet.next()){
                stock = new Stock();
                stock.setId(resultSet.getInt("id"));
                stock.setName(resultSet.getString("name"));
                stock.setCount(resultSet.getInt("count"));
                stock.setSale(resultSet.getInt("sale"));
                stock.setVersion(resultSet.getInt("version"));
            }
            if (stock.getCount()<1)
                throw new RuntimeException("没有库存了");
            return stock;
        }
}