本文围绕MySQL数据库的四个进阶主题展开整理涵盖事务管理的ACID特性与隔离级别、视图的创建与使用规则、用户权限的授予与回收以及通过C语言API连接MySQL的完整流程。一、事务管理1.1 事务的引入1并发场景下的数据问题在多客户端同时访问数据库的场景中如果对增删改查操作不加控制容易引发数据不一致。以火车票售票系统为例tickets表中存有余票信息客户端A和客户端B几乎同时查询到剩余票数大于0各自执行卖票逻辑并更新余票。2事务的定义事务是一组逻辑上存在相关性的DML语句这些语句要么全部成功执行要么全部失败回滚构成一个整体。一个MySQL数据库中可能同时运行多个事务每条事务至少包含一条SQL。如果大家都访问同样的表数据且不加保护就容易出现并发问题。1.2 ACID特性一个完整的事务需要满足四个属性简称ACID属性英文说明原子性Atomicity事务中的所有操作要么全部完成要么全部不执行不会结束在中间环节。一致性Consistency事务开始之前和结束以后数据库的完整性没有被破坏写入的数据符合所有预设规则。隔离性Isolation数据库允许多个并发事务同时读写数据隔离性可以防止事务交叉执行导致数据不一致分为不同级别。持久性Durability事务处理结束后对数据的修改是永久的即便系统发生故障也不会丢失。事务被MySQL编写者设计出来本质上是为了简化应用层的编程模型。使用事务时开发者只需关注提交或回滚服务器宕机或并发修改等问题。1.3 事务的版本支持在MySQL中只有使用了InnoDB存储引擎的数据库或表才支持事务MyISAM不支持。可以通过以下命令查看数据库引擎show engines; -- 表格显示 show engines \G -- 行显示执行结果中InnoDB行的Transactions字段为YES表示支持事务MyISAM行的Transactions字段为NO。1.4 事务提交方式事务的提交方式常见的有两种自动提交和手动提交。查看当前自动提交状态show variables like autocommit;默认情况下autocommit的值为ON即每条SQL语句执行后自动提交。SET AUTOCOMMIT0; -- 禁止自动提交 SET AUTOCOMMIT1; -- 开启自动提交1.5 事务常见操作以下操作基于一张简单的银行用户表account表结构如下create table if not exists account( id int primary key, name varchar(50) not null default , blance decimal(10,2) not null default 0.0 )ENGINEInnoDB DEFAULT CHARSETutf8;1保存点与部分回滚开启事务可以使用start transaction或begin推荐使用begin。在事务执行过程中可以通过savepoint创建保存点。begin; -- 开始事务 savepoint save1; -- 创建保存点save1 insert into account values (1, 张三, 100); savepoint save2; -- 创建保存点save2 insert into account values (2, 李四, 10000); select * from account; -- 两条记录都在 rollback to save2; -- 回滚到保存点save2 select * from account; -- 只剩一条记录 rollback; -- 直接回滚到事务开始 select * from account; -- 空表如果没有设置保存点直接使用rollback只能回滚到事务的开始位置。2提交与持久化使用commit提交事务后数据将被持久化到数据库中。begin; insert into account values (1, 张三, 100); commit; -- 提交事务数据持久化对比实验表明未commit时客户端崩溃MySQL会自动回滚commit之后客户端崩溃数据已经持久化不受影响。3单条SQL与事务的关系对于InnoDB引擎每一条DML语句默认都封装成事务并自动提交。当关闭自动提交后单条insert语句如果未手动commit客户端崩溃时数据会回滚而在自动提交开启的状态下单条insert执行后数据已经持久化。结论只要输入begin或start transaction事务便必须通过commit提交才会持久化与是否设置autocommit无关事务可以手动回滚操作异常时MySQL也会自动回滚。1.6 事务隔离级别1隔离性的理解MySQL服务可能同时被多个客户端进程访问访问方式以事务进行。单个事务对用户表现出原子性但多个事务各自执行SQL时仍有可能互相影响比如多个事务同时访问同一张表甚至同一行数据。查看与设置隔离性的命令如下-- 查看 SELECT global.tx_isolation; -- 查看全局隔离级别 SELECT session.tx_isolation; -- 查看会话隔离级别 SELECT tx_isolation; -- 默认同上 -- 设置 SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}; set session transaction isolation level serializable; -- 设置当前会话 set global transaction isolation level READ UNCOMMITTED; -- 设置全局设置当前会话隔离级别只影响当前会话设置全局隔离级别后新起的会话会受到影响。2读未提交读未提交隔离级别下事务可以看到其他事务没有提交的执行结果。在该级别下一个事务在执行中读到另一个执行中事务更新但未commit的数据这种现象叫做脏读。insert和delete操作同样存在脏读问题。3读提交读提交隔离级别下一个事务只能看到其他已经提交的事务所做的改变。这种级别满足了隔离的简单定义但会引起不可重复读同一个事务执行时如果多次select可能得到不同的结果。4可重复读可重复读是MySQL默认的隔离级别。它确保同一个事务在执行中多次读取操作数据时会看到同样的数据行。在该级别下终端A开启事务并更新数据终端B同步开启事务。无论终端A是否commit终端B在自己的事务中多次查询看到的结果都是一致的这就是可重复读。5串行化串行化是事务的最高隔离级别通过强制事务排序使之不可能相互冲突从而解决了幻读问题。它在每个读的数据行上加上共享锁但可能导致超时和锁竞争。客户端A执行select操作时会对数据加共享锁客户端B的update操作会被阻塞进入等待队列直到终端A的事务提交后终端B的update才会继续执行。6隔离级别对比四种隔离级别在脏读、不可重复读、幻读和加锁读方面的对比如下表隔离级别脏读不可重复读幻读加锁读读未提交会发生会发生会发生不加锁读已提交不会会发生会发生不加锁可重复读不会不会不会不加锁串行化不会不会不会加锁隔离级别越严格安全性越高但数据库的并发性能也就越低往往需要在两者之间寻找平衡点。不可重复读的重点在于修改和删除幻读的重点在于新增。1.7 一致性事务执行的结果必须使数据库从一个一致性状态变到另一个一致性状态。当数据库只包含事务成功提交的结果时数据库处于一致性状态。如果系统运行发生中断某个事务尚未完成而被迫中断而未完成的事务对数据库所做的修改已被写入数据库此时数据库就处于一种不正确的状态。一致性通常通过原子性来保证。1.8 MVCC机制1并发场景分类数据库并发的场景主要有三种读读不存在任何问题也不需要并发控制读写存在线程安全问题可能造成事务隔离性问题如脏读、幻读、不可重复读写写也存在线程安全问题可能存在更新丢失。2记录隐藏字段理解MVCC需要了解三个记录隐藏字段DB_TRX_ID6字节最近修改事务ID记录创建这条记录或最后一次修改该记录的事务ID。DB_ROLL_PTR7字节回滚指针指向这条记录的上一个版本历史版本一般存放在undo log中。DB_ROW_ID6字节隐含的自增ID如果数据表没有主键InnoDB会自动以DB_ROW_ID产生一个聚簇索引。此外还有一个删除flag隐藏字段记录被更新或删除并不代表真的删除而是删除flag发生了变化。3undo日志与版本链undo日志用于保存数据的历史版本。当一条记录被修改时旧版本会被写入undo log中通过DB_ROLL_PTR指针形成一条版本链。当前记录为李四其回滚指针指向undo log中的旧版本张三。事务2在形成快照时从DB_TRX_ID4开始沿着版本链查找根据Read View的可见性规则判断哪个版本对当前事务可见。Read View是事务进行快照读时产生的读视图包含了事务开始时当前系统中所有活跃事务的列表用于判断版本可见性。