锁,即给我们选定的目标数据上锁,使其无法被其他程序修改。乐观锁和悲观锁只是个概念,并不是数据库自带的,需要我们去实现用来保证操作数据库时数据的一致性。
悲观锁:指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态
乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。乐观锁不能解决脏读的问题。
CREATE TABLE `production` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL DEFAULT '' COMMENT '商品名称', `count` int(64) NOT NULL DEFAULT '0' COMMENT '库存数量', `price` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '商品价格', `version` int(64) NOT NULL DEFAULT '0' COMMENT '乐观锁版本', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `status` int(11) NOT NULL DEFAULT '0' COMMENT '商品状态', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
1、表中的version字段是乐观锁在mysql中的实现
2、更新前查询version
SELECT id,name,count,version FROM production WHERE id=1;
3、根据查询的version以及修改条件更改数据(如果version被修改,本次更新失败,进行补偿逻辑)
UPDATE production SET count=count-1,version=version+1 WHERE id=#{id}AND version=#{version};
public class MysqlLockTest { public static final String url = "jdbc:mysql://127.0.0.1:3306/lycharacterEncoding=UTF-8"; public static final String name = "com.mysql.jdbc.Driver"; public static final String user = "root"; public static final String password = ""; public static void main(String[] args) { int count = 100; while (count > 0) { count--; new Thread(new Runnable() { @Override public void run() { Connection conn = null; Statement statement = null; try { Class.forName(name);// 指定连接类型 conn = DriverManager.getConnection(url, user, password);// 获取连接 statement = conn.createStatement();// 准备执行语句 String querySql = "SELECT id,name,count,version FROM production WHERE id=1"; ResultSet resultSet = statement.executeQuery(querySql); int count = 0; int version = 0; while (resultSet.next()) { System.out.println(Thread.currentThread().getName() + " id: " + resultSet.getString("id") + " name: " + resultSet.getString("name") + " count: " + resultSet.getString("count") + " version: " + resultSet.getString("version")); count = Integer.valueOf(resultSet.getString("count")); version = Integer.valueOf(resultSet.getString("version")); } String updateSql = "UPDATE production SET count=" + (count - 1) + ",version=" + (version + 1) + " WHERE id=1 AND version=" + version; int rows = statement.executeUpdate(updateSql); if (rows > 0) { System.out.println("更新成功" + Thread.currentThread().getName()); } else { System.out.println("更新失败" + Thread.currentThread().getName()); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (conn != null) conn.close(); if (statement != null) statement.close(); } catch (SQLException e) { e.printStackTrace(); } } } }, "线程" + count).start(); } } }