mysql 悲观锁学习总结

概念

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

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

在数据库中，悲观锁的流程如下：

在对任意记录进行修改前，先尝试为该记录加上排它锁（exclusive locking）。

如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。

如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。

其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

 

创建production表：

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;

插入一条测试数据如下图：

 

Mysql（Innodb引擎）悲观锁实现步骤（主要是通过SELECT FOR UPDATE语句实现）：

set autocommit=0; //mysql 默认是自动提交事务，设置0，手动提交事务

begin;  //开启事务

select name from production where id=2 for update;  // 这段sql是关键，通过select for update 指定主键，通过行锁对本次查询加上悲观锁，如果用其他人占用着这个锁，本次query hold住，等待锁资源释放

update production set status=2 where id=2; //更新id为2的资源状态

commit; // 提交事务

1、关闭mysql自动提交事务，开启事务，使用select for update对本次查询加上悲观锁 

2、开启新的查询窗口，查询id=2的资源，查询被锁定等待资源释放 

3、在之前的窗口提交事务 

4、锁资源释放，之前等待锁资源的查询语句有了返回结果 

 

悲观锁JAVA实现代码以及测试：

public static final String url = "jdbc:mysql://127.0.0.1:3306/ly?characterEncoding=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) {
        pessimisticLock();
    }

    public static void pessimisticLock() {
        int count = 100;
        while (count > 0) {
            count--;
            new Thread(new Runnable() {
                @Override
                public void run() {
                    Statement statement = null;
                    Connection conn = null;
                    ResultSet resultSet = null;
                    try {
                        Class.forName(name);// 指定连接类型
                        conn = DriverManager.getConnection(url, user, password);// 获取连接
                        beginTransaction(conn);
                        statement = conn.createStatement();// 准备执行语句
                        String querySql = "SELECT id,name,count FROM production WHERE id=2 for UPDATE";
                        resultSet = statement.executeQuery(querySql);
                        int count = 0;
                        while (resultSet.next()) {
                            System.out.println(Thread.currentThread().getName() + "抢到了锁 id: " + resultSet.getString("id")
                                    + " name: " + resultSet.getString("name")
                                    + " count: " + resultSet.getString("count"));
                            count = Integer.valueOf(resultSet.getString("count"));
                        }

                        String updateSql = "UPDATE production SET count=" + (count - 1)
                                + " WHERE id=2";
                        int rows = statement.executeUpdate(updateSql);
                        if (rows > 0) {
                            System.out.println("更新成功" + Thread.currentThread().getName() + "  库存剩余：" + (count - 1));
                            commitTransaction(conn);
                        } else {
                            System.out.println("更新失败" + Thread.currentThread().getName());
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    } finally {
                        try {
                            if (conn != null)
                                conn.close();
                            if (statement != null)
                                statement.close();
                            if (resultSet != null)
                                resultSet.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }, "线程" + count).start();
        }
    }

    /**
     * 开始事务
     *
     * @param cnn
     */
    public static void beginTransaction(Connection cnn) {
        if (cnn != null) {
            try {
                cnn.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 提交事务
     *
     * @param cnn
     */
    public static void commitTransaction(Connection cnn) {
        if (cnn != null) {
            try {
                cnn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

根据测试结果可以发现当前一个线程抢到锁，其他线程在执行SELECT FOR UPDATE时，等待持有锁的线程使用完资源释放锁，跟JAVA中的Synchronized的道理是一样的。

通过select for update时要指定主键去加行锁，如果不指定会添加表锁，很可能会造成数据库死锁，造成查询等待超时等问题。

 

与乐观锁对比的优缺点

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；

另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据

悲观的缺陷是不论是页锁还是行锁，加锁的时间可能会很长，这样可能会长时间的限制其他用户的访问，也就是说悲观锁的并发访问性不好

乐观锁则认为其他用户企图改变你正在更改的对象的概率是很小的，因此乐观锁直到你准备提交所作的更改时才将对象锁住，当你读取以及改变该对象时并不加锁。可见乐观锁加锁的时间要比悲观锁短，乐观锁可以用较大的锁粒度获得较好的并发访问性能

珍惜身边的人