本文共 6481 字,大约阅读时间需要 21 分钟。
现在我有一个购买商品的需求,我们知道当我们购买商品时,后台会进行减库存和增加购买记录的操作。我们分别在无锁和乐观锁和悲观锁进行相应的代码演示来说明问题。
建表语句如下:
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 一、无锁的Mysql:先看代码:
public class Test2 {
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条记录,也就是所谓的超卖现象,商家绝不可能允许这种情况的发生。
MySql的乐观锁:
我们在使用乐观锁时会假设在极大多数情况下不会形成冲突,只有在数据提交的时候,才会对数据是否产生冲突进行检验。如果数据产生冲突了,则返回错误信息,进行相应的处理。
实现:MySql最经常使用的乐观锁时进行版本控制,也就是在数据库表中增加一列,记为version,当我们将数据读出时,将版本号一并读出,当数据进行更新时,会对这个版本号进行加1,当我们提交数据时,会判断数据库表中当前的version列值和当时读出的version是否相同,若相同说明没有进行更新的操作,不然,则取消这次的操作。
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++){ 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; } } 上述在提交时,对version字段进行了比较,当数据库中的version和之前读取的version一样才会进行提交,否则提交失败,接下来进行测试。
可以看到,只有10条记录,乐观锁保证了数据的一致性。
三、悲观锁
MySql的悲观锁就是打开事务,当启动事务时,如果事务中的sql语句涉及到索引并用索引进行了条件判断,那么会使用行级锁锁定所要修改的行,否则使用表锁锁住整张表。
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 { 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; } } 开启事务并不难,所以使用悲观锁很简单,让我们看一下结果结果还是10条记录
我们可以在不同的场合使用不同的处理方法,乐观锁并发高并且性能也很好,而悲观锁虽然并发不是很高,但是它不允许脏读,所以各有各的优点。
--------------------- 作者:yanghan1222 来源:CSDN 原文:https://blog.csdn.net/yanghan1222/article/details/80449528 版权声明:本文为博主原创文章,转载请附上博文链接!