MySQL事务

事务的定义

事务是一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务，例如银行转账业务，此业务涉及到两个操作，从A账户扣款和在B账户增加存款，这两个操作要么都成功，要么都失败。

一个完整的业务需要批量的DML(insert、、delete)语句共同联合完成，事务只和DML语句有关，或者说DML语句才有事务。

事务的特征

事务的特征

1. 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
2. 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
3. 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
4. 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的隔离级别

事务的并发问题

1. 脏读：事务A读取了事务B更新的数据，事务B并没有提交，那么A读取到的数据是脏数据
2. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3. 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

在MySQL5中查看隔离级别可使用以下命令：

SELECT @@TX_ISOLATION;

在MySQL8中调整了变量的命名，命令如下

SELECT @@TRANSACTION_ISOLATION;

同样MySQL也可以设置事务隔离级别。命令如下：

SET SESSION|GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

事务的提交和回滚

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

下面我们通过设置事务隔离级别来分别学习脏读，不可重复读，幻读等情况。

CREATE DATABASE CHAPTER07;
USE CHAPTER07;
CREATE TABLE STUDENT(
 ID INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(10),
 AGE TINYINT,
 GENDER CHAR(3)
);
INSERT INTO STUDENT VALUES (1,'张三',18,'男');
INSERT INTO STUDENT VALUES (2,'李四',18,'男');
INSERT INTO STUDENT VALUES (3,'王五',18,'男');

接下来，我们分别显示脏读，不可重复读、幻读等情况。我们知道在read-uncommitted隔离级别下上述三种情况都会出现。我们打开两个窗口A和B登录mysql，都设置隔离级别为read-uncommitted，然后关闭自动提交。

那么如何解决这些问题呢？这就需要我们设置数据库隔离级别了，MySQL有4种隔离级别，分别是read-uncommitted、read-committed、repeatable-read、serializable。不同的隔离级别在并发环境下可以解决不同的并发问题，如下表:

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

脏读：

首先，我们先在A窗口查询数据。

接着我们在B窗口内修改张三的年龄为20，但是并不提交

然后我们再在窗口A查询数据，我们会发现张三的年龄变为了20，因为B窗口并没有提交，因此A窗口发生了脏读，也就是说A事务读取到了B事务未提交的数据。

下面，我们通过表格来演示这个过程：

时间	事务A	事务B
T1	开始	开始
T2	查询张三年龄为18
T3		修改张三年龄为20，未提交
T4	查询张三年龄为20，发生脏读

不可重复读：

此时，我们先在事务A中读取张三的数据。此时张三年龄为18

接着，我们在事务B中修改张三的年龄为20，并提交事务

此时，我们再次在A事务中查询张三的年龄，发现无论查询多少次年龄都是20，不会再次出现18的值，这种情况就是不可重复读，可以理解为对于之前读取到的值无法重复读取。

脏读和不可重复读很重要的一个区别在于：前者读取到了其他事务未提交的数据，后者读到的是其他事务已经提交的数据。

下面，我们通过事务时间图来理解：

时间	事务A	事务B
T1	开始	开始
T2	查询张三年龄为18
T3		修改张三年龄为20
T4		提交事务
T5	张三年龄为20，不可重复读

幻读：

我们在事务A中反复查询student表：

我们在事务B中新增一条数据。并提交事务

再次在事务A中查询student表，发现凭空多出来一条数据，就像产生幻觉一样。

不可重复读和幻读的区别是：前者是数据变了，后者是数据的行数变了。

下面我们通过表格来理解幻读。

时间	事务A	事务B
T1	开始	开始
T2	查询数据为3条
T3		表中新增一条数据
T4		提交事务
T5	查询出表中数据为4条，凭空多出一条（幻读）

接下来。我们修改事务A和事务B的事务隔离级别为read-committed。

我们在事务A查询张三的年龄：

同时，我们在B事务中关闭自动提交，并修改张三的年龄为20，但是不提交事务

继续在事务A中查询张三年龄，发现张三的年龄还是18.

此时，我们提交事务B的修改，再次查询发现事务A中张三的年龄修改为20。

也就是说通过设置数据库隔离级别为read-commited时，可以有效解决脏读的问题，但是依旧存在不可重复读以及幻读。

我们再次修改事务A隔离级别为repeatable-read，并关闭事务自动提交.

此时我们查询数据如下图：

我们在事务B中修改隔离级别以及设置关闭自动提交。

然后我们在事务B中修改张三年龄为23，并提交事务。

我们再次在事务A中多次查询数据，发现事务A中张三的年龄还是22岁。

因此，当前事务隔离级别可以解决脏读，不可重复读的并发问题。

JDBC事务

我们在使用一些网站或者论坛时，经常发现有这么一种情况，我们注册好用户名和密码后如果要进行其他操作，比如下载资源或者论坛发表文章时，通常要求我们补齐个人资料。也就是说要同时在用户表和用户详情表两张表插入数据，这个操作要么都成功，要么都失败。那么我们就需要在JDBC中控制事务。下面，我们通过示例来学习：

USE DB_TX;
CREATE TABLE INFO (ID INT, ADDRESS VARCHAR(20),TELPHONE VARCHAR (20),STUDENTID INT);
--同时在两张表中插入数据
BEGIN;
INSERT INTO STUDENT VALUES (7,'周八',20,'男');
INSERT INTO INFO VALUES (1,'甘肃兰州市','17765861234',7);
COMMIT;

package cn.bytecollege.db;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @version 3.0
 * @author MR.W
 *
 */
public class DBManager {
	
	private static String url = null;
	private static String user = null;
	private static String password = null;
	private static String driver = null;
	
	private Connection connection = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	
	static {
		InputStream is = DBManager.class.getResourceAsStream("data.properties");
		Properties p = new Properties();
		try {
			p.load(is);
			url = p.getProperty("url");
			user = p.getProperty("user");
			password = p.getProperty("password");
			driver = p.getProperty("driver");
			Class.forName(driver);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 获取连接
	 * @throws SQLException
	 */
	private void getConnection() throws SQLException {
		if(connection==null) {
			connection = DriverManager.getConnection(url,user,password);
		}
	}
	/**
	 * 开启事务
	 * @throws SQLException 
	 */
	public void beginTransaction() throws SQLException {
		getConnection();
		connection.setAutoCommit(false);
	}
	
	/**
	 * 回滚事务
	 * @throws SQLException
	 */
	public void rollbackTransaction() throws SQLException{
		getConnection();
		connection.rollback();
	}
	/**
	 * 提交事务
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
		getConnection();
		connection.commit();
	}
	
	/**
	 * 执行DML
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public int executeUpdate(String sql,Object...objects) throws SQLException {
		getConnection();
		ps = connection.prepareStatement(sql);
		setObject(objects);
		return ps.executeUpdate();
	}
	/**
	 * 执行DQL语句
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String sql,Object...objects) throws SQLException{
		getConnection();
		ps = connection.prepareStatement(sql);
		setObject(objects);
		rs = ps.executeQuery();
		return rs;
	}
	public int executeScale(String sql) throws SQLException {
		getConnection();
		ps = connection.prepareStatement(sql);
		rs = ps.executeQuery();
		rs.next();
		return (int)rs.getObject(1);
	}
	/**
	 * 
	 * @throws SQLException
	 */
	public void close() throws SQLException {
		if(rs!=null&&!rs.isClosed()) {
			rs.close();
			rs = null;
		}
		if(ps!=null&&!ps.isClosed()) {
			ps.close();
			ps = null;
		}
		if(connection!=null&&!connection.isClosed()) {
			connection.close();
			connection = null;
		}
	}
	/**
	 * 为占位符复制
	 * @param objects
	 * @throws SQLException 
	 */
	private void setObject(Object...objects) throws SQLException {
		if(objects!=null&&objects.length>0) {
			for (int i = 0; i < objects.length; i++) {
				ps.setObject((i+1), objects[i]);
			}
		}
	}
	
}

package cn.bytecollege.db;

import java.sql.SQLException;

public class Test {
	public static void main(String[] args) {
		
		DBManager manager = new DBManager();
		
		String sql1 = "INSERT INTO STUDENT VALUES (?,?,?,?)";
		
		Object[] objects1 = {11,"燕青",20,"男"};
		
		String sql2 = "INSERT INTO INFO VALUES (?,?,?,?)";
		
		Object[] objects2 = {2,"陕西省西安市","123321234567",11};
		
		//开启事务
		try {
			manager.beginTransaction();
			System.out.println("开始事务......");
			manager.executeUpdate(sql1, objects1);
//			System.out.println(1/0);
			manager.executeUpdate(sql2, objects2);
			System.out.println("结束事务......");
			System.out.println("提交事务......");
			manager.commit();
			System.out.println("提交完毕......");
			manager.close();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				System.out.println("回滚事务......");
				manager.rollbackTransaction();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		System.out.println("end");
	}
}

认识MySQL锁机制（选修）

MySQL与其他数据库在锁定机制方面最大的不同之处在于，对于不同的存储引擎支持不同的锁定机制。例如，InnoDB存储引擎支持行级锁（row-levellocking），也支持表级锁，默认的情况下是采用行级锁；MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）。BDB存储引擎采用的是页面锁（page-level-locking）同时也支持表级锁。总的来说，MySQL各存储引擎使用了3种级别的锁定机制：行级锁定，页级锁定和表级锁定。下面分析一下这3种级别的锁机制的特点。

行级锁

行级锁最大的特点是锁定对象的颗粒度很小，发生锁定资源争用的概率也很小，能够给予应用程序尽可能大的并发处理能力，从而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上有较大的优势，但是行级锁也存在不少弊端。由于行级锁的颗粒度比较小，每次获取锁和释放锁会消耗比较大，因此加锁比较慢，很容易发生死锁。行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎所实现的，比如InnoDB存储引擎。InnoDB实现了两种类型的行级锁，包括共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB使用了两种内部使用的意向锁，也就是意向共享锁和意向排他锁。各个锁的含义如下：

• 共享锁（S）：允许一个事务读一行数据时阻止其他的事务读取相同数据的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据的共享锁和排他锁。
• 意向共享锁（IS）：事务打算给数据行加行共享锁。事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁。事务在给一个数据行加排他锁前必须先取得该表的IX锁。

上面这4种锁的共存逻辑关系如表：

如果一个事务请求的锁模式与当前的锁模式兼容，InnoDB就将请求的锁授予该事务；如果两者不兼容，那么该事务要等待锁释放。意向锁是InnoDB存储引擎自动加的。对于普通SELECT语句，InnoDB不会加任何锁，对于INSERT、UPDATE、DELETE语句，InnoDB会自动给涉及的数据加排他锁，InnoDB可以通过以下语句添加共享锁和排他锁。
1添加共享锁

SELECT * FROM TABLE_NAME WHERE ... LOCK IN SHARE MODE;

2.添加排他锁

SLECT * FROM TABLE_NAME WHERE ... FOR UPDATE;

表级锁

与行级锁不同的是，表级锁的锁定机制的颗粒度最大，该锁定机制的最大特点是系统开销比较小，由于实现逻辑非常简单，因此带来的系统负面影响最小。由于表级锁一次性将整个表锁定，因此可以很好地避免死锁的问题。表级锁定机制也存在一定的缺陷。由于表级锁的锁定机制颗粒很大，所以发生锁冲突的概率最高，并发度最低。MySQL数据库的表级锁定主要分为两种类型：一种是读锁定，另一种是写锁定。MySQL数据库提供了以下4种队列来维护这两种锁，间接地说明了数据库表级锁的4种状态：

• Current read lock queue (lock -> read)。
• Padding read lock queue (lock -> read wait)。
• Current write lock queue (lock -> write)。
• Padding write lock queue (lock -> write wait)。

其中，Current read lock queue中存放的是当前持有读锁的所有线程，而正在等待资源的信息则存放在Padding read lock queue中；Current write lock queue中存放的是当前持有写锁的所有线程，而正在等待对资源写操作的信息则存放在Padding write lock queue中。MySQL内部实现读锁和写锁有多达11种具体的锁定类型，由系统中一个枚举类型变量（thr_lock_type）定义，具体各种锁定类型如表所示。

对于MySQL数据库读锁和写锁的加锁方式，通常使用LOCK TABLE和UNLOCKTABLE实现对表的加锁和解锁。下表是一个获得表锁和释放表锁的详细过程。

页级锁

页级锁定在MySQL中是比较特殊的一种锁定机制，颗粒度介于行级锁定与表级锁定之间，所以获取锁定所需要的资源开销以及锁提供的并发处理的能力也介于表级锁定和行级锁定之间。在数据库实现资源锁定的过程中，锁定机制的粒度越小，数据库实现的算法越复杂，数据库所消耗的内存越大。不过，随着锁机制粒度越来越小，应用的并发发生锁等待的概率也越来越小，系统整体性能会随之增高。MySQL是用写队列和读队列来完成对数据库的读和写操作的，所以说MySQL数据库存在读锁和写锁的概念。对于写锁而言，如果表没有加锁，就对其表加写锁；如果表已经加了写锁，此时会将写操作的请求放入写锁的队列中。对于读锁而言，如果没有加入读锁，那么请求会加入一个读操作的锁，其他读操作的请求会放到读锁的队列中。