乐观锁与悲观锁

乐观锁

乐观锁就是总往好的方向想，假设管理员去修改某个用户的信息，其他管理员不可能来修改，所以每次取数据都不会上锁，只在更新的时候判断一下别人有没有去更新这个数据，常用的做法就是使用版本号机制

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class OptimisticLock {
	
	public static void main(String[] args) throws SQLException {
		Connection conn = null;
		PreparedStatement statementSelect = null;
		PreparedStatement statementUpdate = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/test";
			String user = "root";
			String password = "666666";
			conn = DriverManager.getConnection(url, user, password);
			// 关闭自动提交事务
			conn.setAutoCommit(false);
			String selectSQL = "select version from user_info where uuid = ?";
			statementSelect = conn.prepareStatement(selectSQL);
			statementSelect.setInt(1, 1);
			ResultSet resultSet = statementSelect.executeQuery();
			Integer version = 0;
			if(resultSet.next()) {
				version = resultSet.getInt("version");
				
			}
			System.out.println("version: "+version);
			String updateSQL = "update user_info set version = version+1, qq = ? where uuid = ? and version = ?";
			statementUpdate = conn.prepareStatement(updateSQL);
			statementUpdate.setNull(1, Types.VARCHAR);
			statementUpdate.setInt(2, 1);
			statementUpdate.setInt(3, version);
			int rows = statementUpdate.executeUpdate();
			System.out.println("更新记录数"+rows);
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
		} finally {
			statementSelect.close();
			statementUpdate.close();
			conn.close();
		}

	}

}

悲观锁

悲观锁就是总往坏的方向想，假设管理员去修改某个用户的信息，其他管理员也可能来修改，所以每次取数据都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。
官网连接
MySQL 使用InnoDB数据库引擎的锁机制实现悲观锁SELECT ... FOR UPDATE

For SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution. For example, in a UNION, scanned (and locked) rows from a table might be inserted into a temporary table before evaluation whether they qualify for the result set. In this circumstance, the relationship of the rows in the temporary table to the rows in the original table is lost and the latter rows are not unlocked until the end of query execution.

MySQL默认使用autocommit模式,使用悲观锁需将自动提交关闭，执行一个更新操作就立即将提交或回滚事务。如果是写事务，拿到锁后，一直不释放，会造成线上故障，读事务也一样。

注意：MySQL默认隔离级别是Repeatable read（可重复读），如果不提交会导致大量事务存到回滚段，导致数据库写入非常慢，容易出现故障，所以事务不管是写还是读都要尽早结束。如果出现了这样的问题，那就到mysql的cmd或者shell界面下执行命令```show engine innodb status\G```，找到ACTIVE时间长的对应的事务，找到对应的thread id，杀掉kill connection threadid，恢复业务正常运行，再去修改业务代码上的问题。

使用DeBug模式运行，在conn.commit();提交事务之前打个断点，使其长时间持有锁，然后运行其他事务处理这条记录就会处于等待释放锁中，只有该事务释放锁后其他事务才能执行事务，负责一直处与等待中。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Lock {

	public static void main(String[] args) throws SQLException {
		Connection conn = null;
		PreparedStatement statementLock = null;
		PreparedStatement statementUpdate = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/test";
			String user = "root";
			String password = "666666";
			conn = DriverManager.getConnection(url, user, password);
			// 关闭自动提交事务
			conn.setAutoCommit(false);
			String selectSQL = "SELECT * FROM user_info WHERE uuid = ? for update";
			statementLock = conn.prepareStatement(selectSQL);
			statementLock.setInt(1, 1);
			ResultSet resultSetLock = statementLock.executeQuery();
			while (resultSetLock.next()) {
				System.out.println("username: "+resultSetLock.getString("username"));
			}
			String updateSQL = "update user_info set qq = ? where uuid = ?";
			statementUpdate = conn.prepareStatement(updateSQL);
			statementUpdate.setString(1, "1234567890");
			statementUpdate.setInt(2, 1);
			int rows = statementUpdate.executeUpdate();
			System.out.println("更新记录数"+rows);
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
		} finally {
			statementLock.close();
			statementUpdate.close();
			conn.close();
		}

	}

}