数据库技术基础实践 7

第8章事务并发与备份恢复8.1事务8.2并发控制8.3数据库备份8.4数据库恢复8.5导出数据本章内容8.1事务8.1事务数据库的事务是一种机制、一个操作序列，它包含一组用户定义的数据操作命令序列。事务把所有的命令序列作为一个整体一起向数据库系统提交，这一组数据库命令序列要么都执行，要么都不执行。事务是一个不可分割的工作逻辑单元。事务处理机制在程序开发过程中使得整个系统更加安全。在MySQL系统的三种常见存储引擎中，只有InnoDB模式支持事务，而MyISAM与Memory模式不支持事务。8.1.1事务的特性原子性（Atomicity）一致性（Consistency）隔离性（Isolation）持久性（Durability）简称为事务的ACID特性。8.1.1事务的特性原子性（Atomicity）一致性（Consistency）隔离性（Isolation）持久性（Durability）简称为事务的ACID特性。8.1.1事务的特性事务的原子性是指每一个事务都是一个包括一个或多个任务的完整操作，每个事务都是一个不可分割的逻辑单元，事务中的所有元素必须作为一个整体提交，如果事务中的任何元素失败，则整个事务将失败，系统将返回到该事务开始前的状态。A向B转账500元场景成功A账户-500，B账户+500;不成功A,B账户余额保持不变。8.1.1事务的特性事务的一致性是指当事务执行完成后，数据库必须从一个一致性状态变到另一个一致性的状态。在正在进行事务处理的过程中，数据可能处于不一致的状态，因为可能数据有部分被修改；然而，当事务成功完成后，数据必须再次回到一致状态。A向B转账500元场景转账进行过程中，账户余额扣减和增加有先后，但转账事务完成后，账户必须回到一致性状态（双方余额总数不变）。8.1.1事务的特性事务的隔离性是为了解决多个事务并发访问相同数据从而相互影响的问题。按照隔离性程度从弱到强排序，MySQL事务隔离分为：读未提交（readuncommitted）读提交（readcommitted）可重复读（repeatableread）串行化（Serializable）在后面事务并发控制小节，会对隔离性进行详细讨论。8.1.1事务的特性事务的持久性是指事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。事务的ACID原则保证了一个事务或者成功提交，或者失败回滚，二者必居其一。8.1.2事务的管理SQL规范中要求事务必须有明确的开始与结束标志。1.开始事务：MySQL中可使用如下语句做为事务开始的标志。BEGIN;或STARTTRANSACTION;2.提交事务：MySQL中可使用COMMIT语句提交事务，语法格式如下：COMMIT;3.撤销（回滚）事务：MySQL中可使用ROLLBACK语句提交事务，语法格式如下：ROLLBACK;8.1.2事务的管理一旦通过BEGIN或STARTTRANSACTION语句开启事务后，事务内所有对数据库数据的更新操作都将记录在事务日志中，直至遇到ROLLBACK语句或COMMIT语句。如果事务中某一操作失败且执行了ROLLBACK语句，那么在开启事务语句之后所有更新的数据都能回滚到事务开始前的状态。如果事务中的所有操作都全部正确完成，并且使用了COMMIT语句向数据库提交更新数据，则此时的数据又处在新的一致状态。8.1.2事务的管理【例8-1】创建account表，包括账户编号，账户名，账户余额三个字段，初始数据如图所示。现使用数据库事务模拟李四向张三转账200元的场景。要求在事务已修改数据但未提交数据时与事务完成提交后分别读取张三和李四的账户信息，对比差异。数据库的并发冲突一定是存在于两个或多个会话之间。因此，本问题需要至少在两个会话中进行操作，才能正确验证事务操作的结果。一个会话可以简单认为是一个数据库客户端连接。8.1.2事务的管理手工分步执行事务，使上述事务在（1）、（2）两步后停顿，从而方便使用查询语句查看实时结果。最终结果表明，在（1）、（2）两步执行后，使用查询语句查看的数据集与初始数据一致，在（3）执行后，account表的数据集如右图所示。STARTTRANSACTION;UPDATEaccountSETaccount.balance=account.balance-200WHEREid=2;/*(1)*/UPDATEaccountSETaccount.balance=account.balance+200WHEREid=1;/*(2)*/COMMIT;/*(3)*/8.1.2事务的管理【例8-2】在例8-1的基础上，将张三的账户余额继续减少500元，并让事务回滚，为了检查事务执行过程中和事务执行完成后的数据集，分别在当前会话完成数据更新和回滚完成后加入两条查询语句实时查看结果。STARTTRANSACTION;UPDATEaccountSETaccount.balance=account.balance-500WHEREid=1;SELECT*FROMaccountWHEREid=1;/*(1)*/ROLLBACK;SELECT*FROMaccountWHEREid=1;/*(2)*/当前会话下查询完成数据更新后的余额（左）与完成回滚操作后的余额（右）8.2并发控制8.2并发控制并发控制（ConcurrencyControl）是数据库管理系统中的一个核心概念，它用于在多个用户或进程同时访问和修改数据库时确保数据的一致性和完整性。在并发环境中，多个事务可能会同时执行，并可能尝试访问和修改相同的数据项。如果没有适当的并发控制机制，这种并发访问可能会导致脏读（DirtyRead）、不可重复读（Non-repeatableRead）、幻读（PhantomRead）等情况发生。8.2.1并发控制中的几个重要的定义与概念并发性:指多个用户或进程在同一时间段内对数据库进行访问和操作的能力。脏读：脏读是指在数据库访问中，一个事务修改了某个数据值，另一个事务读取了这个值，但在此之后，T2因为某些原因撤销了对该值的修改，导致T1读取到的数据是无效的。下图是发生脏读的一个典型场景。8.2.1并发控制中的几个重要的定义与概念不可重复读：不可重复读是指在同一个事务（T1）内多次读取同一数据时，由于其他事务(T2,T3…)的修改并提交，导致多次读取的结果不一致。下图是发生不可重复读的一个典型场景。8.2.1并发控制中的几个重要的定义与概念幻读：幻读是指在一个事务内执行多次相同条件的查询操作时，由于其他事务的插入或删除操作，导致结果集不一致。图87是发生幻读的一个典型场景。8.2.1并发控制中的几个重要的定义与概念脏读发生在数据未提交之前不可重复读和幻读发生在数据已提交之后不可重复读是由于两次读取同一数据，该数据由于另外事务的修改操作发生变化，从而导致发生读取不一致幻读是由于另外事务的插入或删除操作，导致发生读取不一致MySQL的默认事务隔离级别是可重复读8.2.1并发控制中的几个重要的定义与概念可以使用下面语句分别查看与设置MySQL当前的事务隔离级别。SELECT@@SESSION.TRANSACTION_ISOLATION;--查看SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;--设置其中设置命令中的事务隔离级别可选参数有：READUNCOMMITTEDREADCOMMITTEDREPEATABLEREADSERIALIZABLE8.2.1并发控制中的几个重要的定义与概念锁（Lock）：锁是并发控制的主要手段，用于防止多个事务同时修改同一数据而导致的数据不一致问题。8.2.2锁机制根据属性不同，MySQL中的锁可分为共享锁（SharedLock）和排他锁（ExclusiveLock）。共享锁也称为读锁或S锁。当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能加写锁，直到所有读锁释放之后，其他事务才能对数据进行写操作。共享锁可以保证被读取的数据在使用过程中不被修改。排他锁也称为写锁或X锁。当一个事务为数据加上写锁时，其他事务不能对其加上任何锁。这保证了该数据在对其加锁的事务释放锁之前不能被另外事务读取或修改。8.2.2锁机制锁类型描述允许其他事务读取允许其他事物写入示例共享锁（S锁）允许多个事务同时读取同一资源是否SELECT...LOCKINSHAREMODE(InnoDB)

排他锁（X锁）阻止其他事务读取或写入同一资源否否INSERT,UPDATE,DELECT(InooDB默认）上表中“允许多个事务同时读取”和“阻止其他事务读取或写入”是指持有特定类型锁的事务在保持锁的情况下，其他事务是否可以进行读取或写入操作。8.2.2锁机制根据锁的粒度不同，MySQL的锁主要可以分为：表级锁（Table-levelLock）行级锁（Row-levelLock）页级锁（levelLock）间隙锁（GapLock）8.2.2锁机制表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁MySQL的MyISAM和InnoDB存储引擎都支持表级锁表级锁的特点是开销小，加锁快，锁定力度大，不会出现死锁但并发度最低，发生锁冲突的概率最高适用于并发程度不高，以查询为主，数据更新操作较少的应用有一种特殊的表级锁称为意向锁（IntentionLock），它用以表明事务对表中行的锁定需求意向共享锁（IntentionSharedLock，IS）意向排它锁（IntentionExclusiveLock，IX）‌8.2.2锁机制行级锁是在表的行上进行锁定，是MySQL中锁定粒度最小的一种锁MySQL的InnoDB存储引擎支持行级锁，它允许数据库系统对表中的某一行进行加锁，而不影响其他行的数据操作行级锁的特点是开销大，加锁慢，锁定力度小，会出现死锁但并发度最高，发生锁冲突的概率最低适用于高并发环境下，对事务完整性要求较高的系统8.2.2锁机制页级锁的锁定粒度介于表级锁和行级锁之间MySQL中的BDB引擎支持页级锁随着时代的发展，BDB存储引擎已经不再被主流MySQL版本所支持，它在MySQL5.1及以前的版本中被广泛使用，但在之后的版本中已经被移除页级锁的特点是开销和加锁速度介于表级锁和行级锁之间，锁定粒度也介于表级锁和行级锁之间，并发度一般8.2.2锁机制间隙锁是数据库中用于锁定索引范围的一种锁，它可以锁定一行或多行记录。它的主要目的是防止其他事务在给定范围内插入新的数据，保证范围内数据的一致性和避免幻读。举例说明，假设执行如下范围查询，会获取范围在10到20之间的数据行，并且对这个范围的间隙（即9到21之间的索引空间）加上间隙锁，防止其他事务在这个范围内插入新的数据行SELECT*FROMtableWHEREidBETWEEN10AND20FORUPDATE;8.2.2锁机制【例8-3】为账户表加读锁、写锁与释放锁。LOCKTABLESaccountREAD;UNLOCKTABLES;

LOCKTABLESaccountWRITE;UNLOCKTABLES;为表添加读锁后，如尝试使用UPDATE语句更新表中数据，MySQL会报1099错误码，提示由于存在读锁，不可更新数据，如下图。为表添加写锁后，如尝试使用SELECT语句查询表中的数据，会发现查询语句被卡住，无法返回结果。8.2.3死锁及解决办法死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象。下图是一个典型的死锁的例子：8.2.3死锁及解决办法死锁的发生必须满足以下四个必要条件：（1）互斥条件。资源不能被共享，只能由一个事务占用。（2）请求与保持条件。事务已经占用了至少一个资源，同时又在请求其他资源。（3）不剥夺条件。资源不能被强制剥夺，只能由占用资源的事务主动释放。（4）循环等待条件。存在一个事务的循环链，链中的每个事务都在等待下一个事务占用的资源。为了避免死锁，可以采取一些策略，如避免使用多个锁、按照规定的顺序申请锁、设置超时时间以及统一管理锁资源等。8.2.3死锁及解决办法MySQL提供了死锁的检测机制，可以通过查看information_schema库下的INNODB_LOCKS和INNODB_LOCK_WAITS表来查看当前发生的死锁信息。INNODB_LOCKS表提供了锁定的行的信息而INNODB_LOCK_WAITS表则显示了哪些行正在等待其他行的锁定相关的SQL语句如下：SELECT*FROMinformation_schema.INNODB_LOCKS;SELECT*FROMinformation_schema.INNODB_LOCK_WAITS;8.3数据库备份8.3.1数据库备份概述数据库备份是指将数据库进行备份以防数据丢失。数据库备份主要有四种类型。（1）完全备份（2）事务日志备份（3）差异备份（增量备份）（4）文件备份8.3.2使用命令备份数据库使用mysqldump命令可以备份数据库。Windows环境下的默认目录是C:\ProgramFiles\MySQL\MySQLServer8.0\bin命令必须在cmd窗口下执行，不能登录到MySQL服务中执行。8.3.2使用命令备份数据库语法格式如下：mysqldump-uusername-p[-Pport]dbname[tbname...]>filename.sqlusername代表访问数据库的用户名dbname代表需要备份的数据库名称-p表示需要密码-P表示指定数据库端口号，如果不指定端口号，则使用默认端口号3306tbname表示数据库中需要备份的数据表，这里可以指定多个数据表。如省略该参数，则会备份整个数据库右箭头“>”用来告诉mysqldump将备份数据表的定义和数据写入备份文件，filename.sql表示备份文件的名称8.3.2使用命令备份数据库【例8-4】使用root用户备份testdb数据库下的account表。C:\ProgramFiles\MySQL\MySQLServer8.0\bin>mysqldump-uroot-ptestdbaccount>C:\account.sqlEnterpassword:****打开命令行（cmd）窗口，输入备份命令和密码，运行过程如下：输入密码后，MySQL会对testdb数据库下的account数据表进行备份。之后就可以在指定路径下查看刚才备份过的文件。account.sql文件片段8.3.2使用命令备份数据库如果要使用mysqldump命令备份多个数据库，需要使用--databases参数。备份多个数据库的语法格式如下：mysqldump-uusername-p[-Pport]--databasesdbname1dbname2...>filename.sql加上“--databases”参数后，必须指定至少一个数据库名称，多个数据库名称之间用空格隔开。【例8-5】使用root用户备份testdb数据库和mysql数据库。命令如下:mysqldump-uroot-p--databasestestdbmysql>C:\testdbandmysql.sql执行完后，可以在C盘根目录下看到名为testdbandmysql.sql的文件，这个文件中存储着这两个数据库的信息。8.3.2使用命令备份数据库mysqldump命令备份所有数据库的语法格式如下，使用“--all-databases”参数时，不需要指定数据库名称。mysqldump-uusername-p[-Pport]--all-databases>filename.sql【例8-6】使用root用户备份所有数据库。命令如下。执行完后，可以在C盘根目录下面看到名为all.sql的文件，这个文件中存储着所有数据库的信息。mysqldump-uroot-p--all-databases>C:\all.sql8.4数据库恢复8.4数据库恢复数据库恢复是指通过技术手段，将保存在数据库中丢失的电子数据进行抢救和恢复的技术。在MySQL中，可以使用mysql命令来恢复备份的数据。mysql命令语法格式如下：mysql-uusername-p[-Pport][dbname]<filename.sqlusername表示用户名称-p表示需要密码-P表示指定数据库端口号，如果不指定，则使用MySQL默认端口号3306dbname表示数据库名称，该参数是可选参数，filename.sql是备份文件mysql命令需要直接在命令行（cmd）窗口下执行8.4数据库恢复【例8-7】删除testdb下的account表。使用root用户从例8-4中备份的account.sql文件中恢复testdb数据库中的account表和相关数据。注意，如果使用--all-databases参数备份了所有的数据库，那么恢复时不需要指定数据库。因为，其对应的sql文件中含有CREATEDATABASE语句，可以通过该语句创建数据库。mysql-uroot-ptestdb<C:\account.sql假设备份文件存放在C盘根目录下，可使用下面命令进行数据库恢复8.5导出数据8.5导出数据通过前面学习的数据库与数据表的备份，可以实现整个数据库或者部分数据表的数据备份或迁移。但如果实际需要导出部分数据，使用备份的方式就会存在数据冗余、结果不直观等问题。因此，本节主要学习如何针对性的通过SQL语句仅导出需要的数据。这里的导出是指将MySQL数据表的数据或部分数据复制到文本文件。8.5导出数据在MySQL中，可以使用SELECT...INTOOUTFILE语句将表的内容导出成一个文本文件。SELECT...INTOOUTFI