《数据库系统与应用技术》课件 第7章 MySQL事务机制与锁机制

MySQL事务机制与锁机制主讲:何广赢时间:2026/01/01目录01事务机制概述事务的定义、ACID特性和应用场景02事务管理自动提交模式、保存点和实战案例03并发调度事务调度类型和四类并发异常04隔离级别四种隔离级别的特点和配置05锁机制锁的粒度、类型和锁定操作06死锁问题死锁成因、检测和预防策略本章重点：理解事务机制和锁机制的基本原理和控制操作，掌握MySQL数据库中事务机制和锁机制的工作原理01事务机制基础01TRANSACTIONOVERVIEW事务机制概述事务的定义在MySQL中，事务是一组数据操作命令序列的集合，它的执行作为一个不可分割的工作单位。事务确保所有操作要么全部完成，要么全部不执行，中间不会被打断。在数据库系统常见的并发处理环境下，事务的目的是简化编程模型，确保数据的一致性和完整性。事务的核心价值1数据完整性保障确保数据库从一个一致性状态转移到下一个一致性状态2编程模型简化开发者无需处理复杂的数据恢复和并发冲突3错误恢复机制事务执行过程中发生差错可自动回滚到开始前的状态事务的基本特征原子性所有操作要么全部成功，要么全部失败一致性保持数据库的完整性约束不被破坏隔离性并发事务之间互不干扰持久性事务提交后数据永久保存为什么需要事务？在并发处理环境下，多个用户同时操作同一数据资源时，没有事务机制就会导致数据混乱和不一致。事务机制为数据库操作提供了可靠的保障，特别是在金融、电商等对数据准确性要求极高的场景中。ACIDPROPERTIES事务的ACID特性事务的四大特性是事务机制的理论基础，确保数据库操作的可靠性和一致性A原子性Atomicity事务中的操作是一个基本工作单位，要么全部完成，要么全部不执行。在事务执行过程中若发生差错，本次执行会被回滚到事务开始前的状态。START→Op1→Op2→...→COMMIT/ROLLBACKC一致性Consistency事务在开始之前和完成后，数据库是从上一个稳定性状态到下一个稳定性状态，其完整性不会被破坏。所有数据约束和业务规则在事务执行前后都保持一致。ConsistentState→Transaction→ConsistentStateI隔离性Isolation多个事务并发操作时，隔离性会使得各个事务在相对独立的环境中执行，期间不会相互干扰。执行的结果才能被访问，可以防止事务交叉执行而导致数据不一致。Tx1:[———]Tx2:[———](互不干扰)D持久性Durability事务完成后，数据的状态是稳定的，对数据的修改是永久的，即使系统故障也不会造成影响。数据已经被写入持久化存储介质。COMMIT→PersistentStorage→SystemCrash→DataSafeACID特性的协同作用：四大特性相辅相成，共同构成了事务机制的理论基础。原子性确保操作完整，一致性维护数据约束，隔离性处理并发冲突，持久性保证数据安全。ATOMICITY原子性：不可分割的工作单位原子性确保事务中的所有操作要么全部成功，要么全部失败，不存在中间状态核心概念事务中的操作是一个基本工作单位，要么全部完成，要么全部不执行。在事务执行过程中若发生差错，本次执行会被回滚到事务开始前的状态。原子性保证了事务的"全有或全无"特性，避免了部分操作成功、部分操作失败导致的数据不一致问题。没有原子性的风险部分操作成功转账时扣款成功但存款失败，导致资金丢失数据不一致订单创建成功但库存未扣减，导致超卖系统崩溃恢复困难无法确定哪些操作已执行，哪些需要回滚事务执行流程STARTTRANSACTION事务开始，系统记录起始状态1执行操作1UPDATEtable1SET...2执行操作2UPDATEtable2SET...N执行更多操作...继续执行其他SQL操作COMMIT-全部成功所有操作永久生效或ROLLBACK-全部失败所有操作撤销，回到开始状态CONSISTENCY一致性：数据状态的稳定转换一致性确保数据库从一个稳定状态转移到另一个稳定状态，维护数据完整性约束不被破坏核心概念事务在开始之前和完成后，数据库是从上一个稳定性状态到下一个稳定性状态，其完整性不会被破坏。所有数据约束和业务规则在事务执行前后都保持一致。一致性是数据库内部状态的正确性保证，确保数据始终满足所有定义的约束条件。一致性约束类型实体完整性主键不能为NULL，且必须唯一参照完整性外键必须引用存在的记录域完整性数据类型、范围、格式约束业务规则账户余额不能为负数等自定义规则1事务开始前数据库处于一致性状态，所有约束得到满足。账户A:1000元,账户B:500元总余额:1500元✓2事务执行中数据库可能处于临时不一致状态，但事务内部可见。账户A:800元,账户B:500元总余额:1300元✗(临时)3事务完成后数据库再次处于新的一致性状态，所有约束重新得到满足。账户A:800元,账户B:700元总余额:1500元✓ISOLATION隔离性：并发事务的独立执行隔离性确保多个并发事务在独立环境中执行，互不干扰，防止数据不一致核心概念多个事务并发操作时可能会同时对其数据进行读写，隔离性会使得各个事务在相对独立的环境中执行，期间不会相互干扰，执行的结果才能被访问，可以防止事务交叉执行而导致数据不一致。隔离性通过锁机制和MVCC（多版本并发控制）来实现，确保每个事务看到的数据都是一致的。没有隔离性的风险丢失更新两个事务同时更新同一数据，后提交的事务覆盖了先提交的事务的更新脏读事务读取了另一个未提交事务的临时数据，后者随后回滚不可重复读同一事务中两次读取同一数据得到不同结果并发事务执行示意T1事务1BEGIN;UPDATEaccountsSETbalance=balance-100WHEREid='A';UPDATEaccountsSETbalance=balance+100WHEREid='B';COMMIT;T2事务2BEGIN;--等待事务1释放锁SELECTbalanceFROMaccountsWHEREid='A';--读取已提交的数据COMMIT;隔离级别通过设置不同的隔离级别（READUNCOMMITTED、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE），可以在数据一致性和系统性能之间找到平衡。DURABILITY持久性：永久性的数据变更持久性确保事务完成后，数据修改是永久的，即使系统故障也不会丢失核心概念事务完成后，数据的状态是稳定的，对数据的修改是永久的，即使系统故障也不会造成影响。数据已经被写入持久化存储介质（如硬盘、SSD）。持久性通过事务日志（redolog、undolog）机制实现，确保已提交事务的数据不会丢失。持久化机制RedoLog（重做日志）记录事务对数据的修改，用于崩溃恢复时重放已提交事务UndoLog（回滚日志）记录事务前的数据状态，用于事务回滚和MVCCCheckpoint（检查点）定期将内存中的脏页刷新到磁盘，加快恢复速度数据持久化流程1执行UPDATE操作修改内存中的数据页（BufferPool）2写入RedoLog记录修改内容到重做日志缓冲区3事务提交RedoLog刷写到磁盘，确保持久性4后台刷新修改的数据页异步刷新到磁盘持久性保证系统崩溃：已提交事务的数据可通过RedoLog恢复断电故障：重启后自动进行崩溃恢复，数据不丢失介质故障：结合备份和日志可实现时间点恢复USECASESCENARIO事务应用场景：银行转账案例通过银行转账业务场景，生动说明事务的必要性和核心价值业务场景描述事务的应用举例，比如银行转账业务中，数据操作需要一组特定流程来实现。张三给李四转账1000元，这一组操作流程分为减少张三账户金额和增加李四账户金额两步完成。张三转出账户转账1000元李四转入账户操作流程分解1查询张三账户余额SELECTbalanceFROMaccountsWHEREname='张三';2扣减张三账户金额UPDATEaccountsSETbalance=balance-1000WHEREname='张三';3增加李四账户金额UPDATEaccountsSETbalance=balance+1000WHEREname='李四';如果没有事务机制如果其中一个环节失败，就会导致数据不一致。例如：扣减张三账户金额成功，但增加李四账户金额失败，这1000元就"消失"了！数据不一致的严重后果资金丢失：转账金额在系统中凭空消失账目不平：系统总资金量发生变化客户投诉：用户体验极差，信任度下降法律风险：金融数据错误可能涉及法律责任事务的完美解决方案通过事务机制，可以确保这两步操作要么同时成功，要么同时失败，从而保持数据的一致性。成功场景扣款和存款都成功→COMMIT→数据永久生效失败场景任一步骤失败→ROLLBACK→回到初始状态RISKANALYSIS银行转账：没有事务的风险分析没有事务机制时银行转账可能出现的数据不一致情况及其严重后果场景一扣款成功+存款失败UPDATEaccountsSETbalance=balance-1000WHEREname='张三';✓执行成功UPDATEaccountsSETbalance=balance+1000WHEREname='李四';✗执行失败最终结果：张三少了1000元，李四没有增加，1000元凭空消失场景二扣款失败+存款成功UPDATEaccountsSETbalance=balance-1000WHEREname='张三';✗执行失败UPDATEaccountsSETbalance=balance+1000WHEREname='李四';✓执行成功最终结果：张三没少钱，李四多了1000元，银行资金凭空增加场景三系统崩溃中断UPDATEaccountsSETbalance=balance-1000WHEREname='张三';✓执行成功系统崩溃!操作中断最终结果：张三少了1000元，李四未收到，数据永久损坏关键启示金融系统对数据一致性的要求极高，必须使用事务机制来保证操作的原子性和一致性。原子性：扣款和存款必须同时成功或同时失败一致性：转账前后总资金量保持不变持久性：转账成功后数据永久保存TRANSACTIONSOLUTION银行转账：事务的解决方案展示事务如何完美解决银行转账问题，确保数据一致性事务包装转账操作--使用事务确保原子性STARTTRANSACTION;--1.查询张三账户余额SELECTbalanceINTO@zhang_balanceFROMaccountsWHEREname='张三';--2.检查余额是否充足IF@zhang_balance>=1000THEN--3.扣减张三账户金额UPDATEaccountsSETbalance=balance-1000WHEREname='张三';--4.增加李四账户金额UPDATEaccountsSETbalance=balance+1000WHEREname='李四';COMMIT;--提交事务，数据永久生效ELSEROLLBACK;--回滚事务，撤销所有操作ENDIF;END;事务执行流程图STARTTRANSACTION开启事务，准备执行转账操作↓验证余额检查张三账户余额是否充足↓执行扣款扣减张三账户1000元↓执行存款增加李四账户1000元↓COMMIT余额充足，提交事务ROLLBACK余额不足，回滚事务02事务管理操作02TRANSACTIONMANAGEMENTMySQL事务管理基础介绍MySQL中事务的默认设置和常用管理命令默认设置：自动提交模式在MySQL中，事务默认的设置是自动提交或回滚。这意味着每执行一条SQL语句，MySQL会自动将其作为一个独立的事务提交。--查看自动提交设置SHOWVARIABLESLIKE'autocommit';结果：ON（默认开启）手动控制事务的必要性自动提交的问题每条SQL独立提交，无法实现多个操作的原子性手动控制的优势可以将多个SQL操作组合成一个完整的事务事务管理命令STARTTRANSACTION显式开启一个新的事务COMMIT提交事务，使所有修改永久生效ROLLBACK回滚事务，撤销所有未提交的修改SAVEPOINT设置保存点，实现部分回滚重要提示用户可根据需要修改数据库系统的自动提交模式，并根据业务逻辑设置事务保存点。如有需要可回滚至指定保存点，提供更大的灵活性。AUTOCOMMITCONTROL关闭事务自动提交模式详细说明两种关闭自动提交的方式及其适用场景1显式关闭ExplicitClose通过设置系统变量@@autocommit为0，显式关闭当前会话的自动提交模式。--显式关闭自动提交SET@@autocommit=0;--验证设置结果SELECT@@autocommit;全局影响：当前会话的所有后续操作都需要手动提交持久性：设置在当前会话生命周期内有效注意：需要记得手动提交或回滚，否则可能导致锁问题2隐式关闭ImplicitClose通过执行STARTTRANSACTION命令，隐式关闭当前事务的自动提交模式。--隐式关闭自动提交STARTTRANSACTION;--或（MySQL扩展语法）BEGIN;临时影响：仅影响当前事务，完成后恢复自动提交推荐：更符合事务使用习惯，不易忘记提交清晰：代码意图明确，易于理解和维护重要提示事务不允许嵌套，启动新的事务前，要确保前一个事务已提交或回滚。隐式关闭方式更常用，更符合事务使用习惯，推荐使用。EXPLICITCLOSE显式关闭自动提交：命令与示例通过SET命令显式关闭自动提交模式的详细说明SET命令语法--关闭自动提交模式SET@@autocommit=0;--或（会话级）SETSESSIONautocommit=0;--或（使用别名）SETautocommit=0;验证设置结果--查看当前自动提交设置SELECT@@autocommit;--或SHOWVARIABLESLIKE'autocommit';结果：0（表示自动提交已关闭）使用示例--1.关闭自动提交SET@@autocommit=0;QueryOK,0rowsaffected(0.00sec)--2.执行数据操作INSERTINTOpatients(patientID,patientName)VALUES('p011','王测试');QueryOK,1rowaffected(0.01sec)--3.手动提交COMMIT;QueryOK,0rowsaffected(0.00sec)--或回滚--ROLLBACK;注意事项会话级设置SET命令只影响当前会话，不影响其他会话的自动提交设置需要手动提交关闭自动提交后，必须显式执行COMMIT或ROLLBACKIMPLICITCLOSE隐式关闭自动提交：StartTransaction使用STARTTRANSACTION命令隐式关闭自动提交，更符合事务使用习惯STARTTRANSACTION语法--标准SQL语法STARTTRANSACTION;--SQL语句SQL_statement_1;SQL_statement_2;...--结束事务COMMIT;--或ROLLBACK;MySQL扩展语法--MySQL的BEGIN语法（更简洁）BEGIN;--或（带修饰符）STARTTRANSACTIONWITHCONSISTENTSNAPSHOT;完整示例--1.隐式关闭自动提交STARTTRANSACTION;QueryOK,0rowsaffected(0.00sec)--2.执行多个SQL操作INSERTINTOpatients(patientID,patientName)VALUES('p011','王测试');QueryOK,1rowaffected(0.01sec)INSERTINTOpatients(patientID,patientName)VALUES('p012','李测试');QueryOK,1rowaffected(0.00sec)--3.验证数据SELECT*FROMpatientsWHEREpatientIDIN('p011','p012');+++|patientID|patientName|+++|p011|王测试||p012|李测试|+++--4.提交事务COMMIT;QueryOK,0rowsaffected(0.01sec)推荐使用隐式关闭方式更常用，更符合事务使用习惯，推荐在应用开发中使用。作用范围明确：只影响当前事务自动恢复：事务结束后自动恢复自动提交代码清晰：事务边界明确，易于维护SAVEPOINT设置事务保存点（Savepoint）介绍保存点的概念，实现事务的部分回滚功能保存点的概念事务除了完全回滚，还可部分回滚，即可回滚到某个保存点(savepoint)。保存点允许在事务内部设置标记点，回滚时只撤销保存点之后的操作。应用场景：长事务中的阶段性检查点复杂业务逻辑的分步回滚错误恢复和异常处理基本语法--设置保存点SAVEPOINTsavepoint_name;--回滚到保存点ROLLBACKTOSAVEPOINTsavepoint_name;--释放保存点RELEASESAVEPOINTsavepoint_name;完整语法结构--启动事务STARTTRANSACTION;--执行SQL语句1sql_statement1;--设置第一个保存点（可选）SAVEPOINTsp1;--执行SQL语句2sql_statement2;--设置第二个保存点（可选）SAVEPOINTsp2;--执行更多SQL语句...sql_statement3;--选择：提交、完全回滚或部分回滚COMMIT;--提交所有操作--ROLLBACK;--回滚所有操作--ROLLBACKTOSAVEPOINTsp1;--回滚到sp1重要规则保存点覆盖当事务回滚到某个保存点，该保存点之后的保存点将被删除无效保存点当某个保存点不存在时，回滚到这个保存点会报错SAVEPOINTOPERATIONS保存点操作详解详细说明保存点的创建、回滚和释放操作及其实现机制创建保存点语法：SAVEPOINTsp_name;在事务中设置一个标记点，可以创建多个保存点形成保存点链。回滚到保存点语法：ROLLBACKTOSAVEPOINTsp_name;撤销保存点之后的所有操作，但保留保存点之前的操作。释放保存点语法：RELEASESAVEPOINTsp_name;主动删除保存点，释放相关资源，通常不需要手动调用。保存点回滚机制1创建保存点系统在事务日志中记录保存点标记2继续执行操作后续操作继续记录到事务日志中3执行部分回滚撤销保存点之后的所有操作4保留之前操作保存点之前的操作保持不变，可以继续执行EXAMPLE7-1示例7-1：新增记录验证Rollback和Commit通过实际案例演示关闭自动提交后，事务回滚和提交的效果步骤1：关闭自动提交--查看当前自动提交设置SHOWVARIABLESLIKE'%autocommit%';+++|Variable_name|Value|+++|autocommit|ON|+++--关闭自动提交SET@@autocommit=0;QueryOK,0rowsaffected(0.00sec)步骤2：启动事务并新增记录--启动事务STARTTRANSACTION;QueryOK,0rowsaffected(0.00sec)--新增两条记录INSERTINTOpatients(patientID,patientName)VALUES('p011','王测试');QueryOK,1rowaffected(0.01sec)INSERTINTOpatients(patientID,patientName)VALUES('p012','李测试');QueryOK,1rowaffected(0.00sec)--查询验证SELECT*FROMpatientsWHEREpatientIDIN('p011','p012');步骤3：执行回滚操作--执行回滚ROLLBACK;QueryOK,0rowsaffected(0.00sec)--再次查询验证SELECT*FROMpatientsWHEREpatientIDIN('p011','p012');Emptyset(0.00sec)--结果：两条新增记录被撤销执行ROLLBACK后，前面新增的两条记录（'p011'和'p012'）被撤销，数据库回到事务开始前的状态。步骤4：提交操作--重新启动事务并更新STARTTRANSACTION;UPDATEpatientsSETpatientName='张雯雯'WHEREpatientID='p001';QueryOK,1rowaffected(0.01sec)--提交事务COMMIT;QueryOK,0rowsaffected(0.01sec)--验证更新结果SELECTpatientNameFROMpatientsWHEREpatientID='p001';++|patientName|++|张雯雯|++执行COMMIT后，数据状态被永久写入数据库，即使重启系统数据也不会丢失。EXAMPLE7-1RESULTS示例7-1：Rollback前后的数据状态通过对比展示事务回滚的效果和影响Rollback之前执行rollback之前，两条新增记录（p011、p012）已经存在于表中，在当前会话中可以查询到这两条记录。查询结果：+++|patientID|patientName|+++|p011|王测试||p012|李测试|+++状态说明此时数据处于临时状态，对其他会话不可见，尚未持久化到数据库。Rollback之后执行rollback之后，前面新增的两条记录被完全撤销，数据库回到事务开始前的状态，数据一致性得到保证。查询结果：mysql>SELECT*FROMpatientsWHEREpatientIDIN('p011','p012');Emptyset(0.00sec)--没有记录返回，说明数据已被撤销状态说明数据回到初始一致性状态，所有会话看到的数据都是一致的。关键理解ROLLBACK确保了事务的原子性：事务中的所有操作要么全部成功，要么全部失败。这个案例完美展示了事务机制如何保证数据的完整性和一致性。EXAMPLE7-1COMMIT示例7-1：Update操作后的Commit演示执行update命令并commit之后，数据永久生效的过程Update操作流程--启动新的事务STARTTRANSACTION;QueryOK,0rowsaffected(0.00sec)--执行更新操作UPDATEpatientsSETpatientName='张雯雯'WHEREpatientID='p001';QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0--验证更新结果（当前会话）SELECTpatientNameFROMpatientsWHEREpatientID='p001';++|patientName|++|张雯雯|++--提交事务COMMIT;QueryOK,0rowsaffected(0.01sec)数据持久化验证--重新连接数据库后查询mysql>SELECTpatientNameFROMpatientsWHEREpatientID='p001';++|patientName|++|张雯雯|++--数据保持不变，已持久化即使重启MySQL服务，数据依然保持不变，证明了持久性的保证。关键要点提交即永久：COMMIT后数据立即写入数据库不可回滚：已提交事务无法回滚性能考虑：COMMIT操作会立即刷新日志到磁盘EXAMPLE7-2示例7-2：模拟银行转账业务（表创建）创建模拟银行转账业务对应的实体表，并初始化账户信息创建银行账户表--创建银行账户表CREATETABLEbankAccount(CIDchar(6)PRIMARYKEYCOMMENT'账户ID',CNamechar(8)NOTNULLCOMMENT'账户名',balanceDECIMAL(12,2)COMMENT'账户余额');QueryOK,0rowsaffected(0.03sec)表结构设计说明CID（账户ID）CHAR(6)类型，主键，唯一标识每个账户CName（账户名）CHAR(8)类型，NOTNULL约束，存储账户持有人姓名balance（账户余额）DECIMAL(12,2)类型，精确存储金额，支持大额资金初始化账户数据--插入两条账户信息INSERTINTObankAccountVALUES('c00001','何晓倩',1500.00),('c00002','王知晴',1000.00);QueryOK,2rowsaffected(0.01sec)--验证数据SELECT*FROMbankAccount;++++|CID|CName|balance|++++|c00001|何晓倩|1500.00||c00002|王知晴|1000.00|++++--提交初始化数据COMMIT;初始资金统计何晓倩¥1,500王知晴¥1,000系统总资金¥2,500EXAMPLE7-2PROCEDURE示例7-2：转账存储过程设计创建模拟银行转账业务存储过程，实现完整的转账逻辑存储过程代码--修改边界符DELIMITER//--创建转账存储过程CREATEPROCEDUREproc_transation(INtransactionMoneyDECIMAL(12,2))MODIFIESSQLDATABEGIN--声明变量存储账户余额DECLAREaccountBalanceDECIMAL(12,2);--1.从转出账户扣款UPDATEbankAccountSETbalance=balance-transactionMoneyWHERECID='c00001';--2.向转入账户存款UPDATEbankAccountSETbalance=balance+transactionMoneyWHERECID='c00002';--3.查询转出账户余额SELECTbalanceINTOaccountBalanceWHERECID='c00001';事务保障存储过程使用事务机制确保转账操作的原子性：扣款和存款要么同时成功，要么同时失败。余额检查：转账后验证转出账户余额自动回滚：余额不足时自动撤销操作用户提示：返回明确的转账结果信息--4.判断余额是否充足IFaccountBalance<0THEN--余额不足，回滚事务SELECT'Thetransactionfails,makesurethebankaccountbalanceisenough!'AS'银行转账提示';ROLLBACK;ELSE--余额充足，提交事务SELECT'Thetransactionsuccessfully!'AS'银行转账提示';COMMIT;ENDIF;END//--恢复边界符DELIMITER;EXAMPLE7-2FAILURE示例7-2：转账失败案例分析模拟转账2000元的场景，由于余额不足导致转账失败调用转账存储过程--查询转账前余额SELECTCID,CName,balanceFROMbankAccount;++++|CID|CName|balance|++++|c00001|何晓倩|1500.00||c00002|王知晴|1000.00|++++--模拟转账2000元CALLproc_transation(2000);--存储过程执行结果失败原因分析1初始余额检查何晓倩账户余额：1500元2扣款操作执行扣减2000元后余额：1500-2000=-500元3余额验证失败余额-500<0，触发回滚机制4事务自动回滚扣款和存款操作全部撤销执行结果++|银行转账提示|++|Thetransactionfails,makesurethebankaccountbalanceisenough!|++--查询转账后余额SELECTCID,CName,balanceFROMbankAccount;++++|CID|CName|balance|++++|c00001|何晓倩|1500.00||c00002|王知晴|1000.00|++++--余额保持不变，事务回滚成功EXAMPLE7-2SUCCESS示例7-2：转账成功案例分析模拟转账1000元的场景，账户余额充足，转账顺利完成调用转账存储过程--查询转账前余额SELECTCID,CName,balanceFROMbankAccount;++++|CID|CName|balance|++++|c00001|何晓倩|1500.00||c00002|王知晴|1000.00|++++--模拟转账1000元CALLproc_transation(1000);--存储过程执行结果成功流程分析1扣减转出账户何晓倩：1500-1000=500元2增加转入账户王知晴：1000+1000=2000元3余额验证通过转出账户余额500≥0，验证成功4事务提交执行COMMIT，数据永久生效执行结果++|银行转账提示|++|Thetransactionsuccessfully!|++--查询转账后余额SELECTCID,CName,balanceFROMbankAccount;++++|CID|CName|balance|++++|c00001|何晓倩|500.00||c00002|王知晴|2000.00|++++--转账成功，余额已更新转账成功验证转账前¥2,500转账后¥2,500验证✓一致原子性：扣款和存款同时成功一致性：总资金量保持不变持久性：数据永久写入数据库03并发调度与隔离级别03CONCURRENCYCONTROL事务并发调度的必要性阐述在高并发系统中，多个会话访问同一数据资源时并发控制的重要性为什么需要并发？在数据库系统中，通常会存在多个会话访问同一数据资源的情况，比如购票系统，并发度（同一时间内执行的任务数量）非常高。典型高并发场景：12306购票系统春运高峰期每秒数万次查询和购票请求电商平台秒杀限时抢购，大量用户同时下单银行转账系统全天24小时处理大量转账业务并发的好处提高系统吞吐量多个事务同时执行，充分利用CPU和IO资源减少等待时间短事务不会被长事务阻塞，响应更快支持多用户现代应用必须支持大量并发用户访问没有并发控制的风险数据不一致问题若数据库系统不能够进行并发控制，可能会导致数据不一致问题，严重影响业务正确性。丢失更新两个用户同时修改同一数据，后者的修改覆盖了前者脏读读取了未提交的临时数据，该数据随后被回滚不可重复读同一事务中两次读取同一数据得到不同结果SCHEDULINGTYPES串行调度与并行调度对比两种事务调度方式的优缺点和适用场景串行调度SerialScheduling串行调度是指一个事务完成后再启动下一个事务，当一组事务按照某一顺序执行完，系统数据总是能从一个一致性状态到下一个一致性状态。执行示意：T1:[]T2:[]T3:[]数据准确性不会产生任何并发异常，数据始终一致实现简单无需复杂的并发控制机制性能低下事务串行执行，资源利用率低并行调度ParallelScheduling并行调度是指多个事务交替执行，期间往往会存在事务中有数据被修改了但未提交，导致其他事务访问到临时数据。执行示意：T1:[--][][--]T2:[][]T3:[--][]性能优越多个事务同时执行，资源利用率高响应快速短事务不会被长事务阻塞可能异常可能出现丢失更新、脏读等问题SERIALIZABLESCHEDULE可串行化调度理解并行调度的正确性标准，实现数据一致性与性能的平衡核心概念由于并行调度是交叉执行，其调度顺序的策略要比串行调度多。当按照某种顺序执行并行调度和按某一次串行调度的结果一致时，则称该并行调度策略为可串行化调度。可串行化的含义：并行执行的结果与某个串行执行结果相同保持了数据库的一致性状态是衡量并发调度正确性的标准示例场景事务T1：转账操作UPDATEASETbalance=balance-100;UPDATEBSETbalance=balance+100;事务T2：查询操作SELECTSUM(balance)FROMaccounts;可串行化调度示例--有效的可串行化调度T1:UPDATEA...T1:UPDATEB...T1:COMMIT;T2:SELECT...T2:COMMIT;--等效于串行调度：T1→T2这个并行调度的结果与先执行T1再执行T2的串行调度结果完全一致，因此是可串行化调度。不可串行化调度示例--无效的不可串行化调度T1:UPDATEA...T2:SELECT...T1:UPDATEB...T1:COMMIT;--不等效于任何串行调度T2在T1中间执行，读取了中间状态数据，这个结果与任何串行调度（T1→T2或T2→T1）都不相同，因此是不可串行化调度。CONCURRENTANOMALIES并发调度的四类异常问题概述事务并发调度可能出现的四类异常及其影响1丢失更新LostUpdate事务T1和T2同时读取同一数据并更新，T1的更新被T2覆盖，导致T1的修改丢失。场景：A=100→T1读A=100→T2读A=100→T1更新A=150→T2更新A=120→A=120（T1的更新丢失）2脏读DirtyRead事务T1更新数据但未提交，T2读取了这个临时数据，随后T1回滚，T2读取到的数据无效。场景：A=100→T1更新A=150→T2读A=150→T1回滚A=100→T2的A=150是脏数据3不可重复读Non-repeatableRead事务T2多次读取同一数据，但在两次读取之间，事务T1更新了该数据，导致T2两次读取结果不一致。场景：T2读A=100→T1更新A=150并提交→T2再读A=150→两次结果不同4幻读PhantomRead事务T2按条件查询数据，事务T1新增或删除了符合条件的数据并提交，导致T2再次查询时结果集发生变化。场景：T2查询得到5条记录→T1插入1条并提交→T2再查询得到6条记录（幻影行）异常严重程度：丢失更新>脏读>不可重复读>幻读。隔离级别的设置就是为了在不同程度上防止这些异常的发生。ANOMALY1:LOSTUPDATE异常一：丢失更新（LostUpdate）详细说明丢失更新的场景和危害，通过时序图展示问题发生过程问题描述事务T1和T2同时访问同一数据，T1更新了数据，然后T2更新了数据并提交了，这就导致事务T1对数据的更新被覆盖了，这就导致丢失更新异常。示例场景初始数据：A=100T1:READA=100T2:READA=100T1:A=A+50(A=150)T2:A=A-30(A=70)T1:COMMIT;T2:COMMIT;最终结果：A=70（T1的更新丢失！）期望结果：A=120(100+50-30)时序图展示时间事务T1事务T2t1READA=100t2READA=100t3UPDATEA=150t4UPDATEA=70t5COMMITt6COMMIT最终结果：A=70（T1更新丢失！）解决方案加锁机制：使用排他锁（X锁）防止同时修改提高隔离级别：使用SERIALIZABLE避免丢失更新乐观锁：使用版本号机制检测冲突ANOMALY2:DIRTYREAD异常二：脏读（DirtyRead）阐述脏读的场景和危害性，说明读取临时数据的风险问题描述两个事务T1和T2，T1先访问数据并作了更新但未提交，这时T2访问该数据，即访问到了该数据的临时状态，之后事务T1进行回滚，则导致脏读异常。为什么叫"脏读"？因为读取的是"脏"（未提交、临时、可能被撤销）的数据，这些数据不是数据库中真实存在的数据，可能会导致业务决策错误。示例场景初始数据：A=100时间事务T1事务T2t1BEGINt2UPDATEA=150t3READA=150t4使用A=150计算t5ROLLBACKt6A=100A=150（脏数据！）防止脏读提高隔离级别：使用READCOMMITTED或更高级别加锁等待：T2等待T1提交后再读取MVCC：读取数据的快照版本ANOMALY3:NON-REPEATABLEREAD异常三：不可重复读（Non-repeatableRead）解释不可重复读的场景，说明在需要多次读取同一数据的场景中的问题问题描述事务T1和T2同时访问同一数据源，接着事务T1对数据作了更新并提交，这时事务T2再次访问该数据发现和刚读取的状态不一致，这就导致不可重复读异常。与脏读的区别：不可重复读发生在T1已提交的情况下，而脏读是读取未提交的数据。不可重复读强调同一事务内多次读取结果不一致。示例场景时间事务T1事务T2t1BEGINt2READA=100t3BEGINt4UPDATEA=150t5COMMITt6READA=150T2两次读取结果不同：100→150防止不可重复读提高隔离级别：使用REPEATABLEREADMVCC机制：读取事务开始时的数据快照共享锁：读取时加S锁防止更新ANOMALY4:PHANTOMREAD异常四：幻读（PhantomRead）说明幻读的场景，强调与不可重复读的区别问题描述事务T1和T2同时访问同一数据源，接着事务T1对该数据源新增或删除了数据，伴随着T1的回滚或更新操作，可能会导致T2有时能读到某一记录，有时读取时没有这一记录，这就导致幻读异常。为什么叫"幻读"？因为数据像"幻影"一样时而出现、时而消失，数据的存在性不确定，好像出现了幻觉。与不可重复读的区别不可重复读更新已有数据，数据值发生变化幻读新增/删除数据，数据的数量发生变化示例场景时间事务T1事务T2t1BEGINt2SELECT*FROMusersWHEREage>18t3BEGIN结果：5条记录t4INSERTINTOusers...t5COMMITt6SELECT*FROMusersWHEREage>18t7结果：6条记录（幻读！）防止幻读最高隔离级别：使用SERIALIZABLE间隙锁：锁定查询范围防止插入MVCC快照：REPEATABLEREAD可解决幻读ISOLATIONLEVELS事务隔离级别概述介绍隔离级别的概念、作用和选择策略隔离级别的概念设置事务调度的隔离级别，高级别的隔离级别可以有效避免事务并发调度导致的丢失更新、脏读、不可重复读、幻读异常，但会降低事务的并发调度性能。隔离级别的权衡：高隔离级别：数据一致性更好，但并发性能较低低隔离级别：并发性能更好，但可能出现异常MySQL默认设置--查询当前隔离级别SELECT@@transaction_isolation;结果：REPEATABLE-READMySQL默认的隔离级别为REPEATABLEREAD（可重复读）隔离级别选择策略金融系统推荐使用SERIALIZABLE，数据准确性优先电商系统推荐使用REPEATABLEREAD，平衡一致性和性能报表系统可以使用READCOMMITTED，性能优先慎用READUNCOMMITTED很少使用，易产生脏读注意事项存储引擎差异：MyISAM不支持事务和隔离级别会话级设置：可根据不同会话设置不同级别性能监控：需要在生产环境监控性能影响ISOLATIONLEVELTYPES四种隔离级别详解详细说明四种隔离级别的特点、防止的异常和适用场景1READUNCOMMITTED未提交读最低隔离级别，一个事务可以访问另一个事务未提交的数据，这可能会导致脏读异常。可能异常：脏读、不可重复读、幻读适用场景：对数据一致性要求不高的查询，如日志分析、统计报表2READCOMMITTED提交读一个事务只能访问其他事务已经提交的变更。这能有效避免脏读，但可能会出现不可重复读异常。防止：脏读✓可能异常：不可重复读、幻读适用场景：Oracle、SQLServer默认隔离级别，适合大多数业务系统3REPEATABLEREAD可重复读MySQL默认隔离级别，能保证在同一个事务中多次访问同样记录的结果是一样的，避免了不可重复读的问题，但可能会有幻读异常。防止：脏读✓、不可重复读✓可能异常：幻读（InnoDB通过MVCC解决）4SERIALIZABLE可串行化最高隔离级别，强制事务串行调度，能避免丢失更新、脏读、不可重复读和幻读的异常。但高并发情况下数据库系统性能会降低。防止：所有并发异常✓适用场景：金融系统、核心账务处理，数据准确性要求极高的场景ISOLATIONLEVEL1READUNCOMMITTED：未提交读最低隔离级别，允许事务访问其他事务未提交的数据更新最低隔离级别READUNCOMMITTED（未提交读）表示一个事务可以访问另一个事务未提交的数据更新，这可能会导致脏读异常。特点：性能最高：无需等待其他事务提交一致性最差：可能读取到脏数据风险最高：数据不可靠可能异常脏读✗可能读取到其他事务未提交的临时数据不可重复读✗同一事务内多次读取结果不一致幻读✗查询结果集数量发生变化设置命令--设置隔离级别为未提交读SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;--验证设置SELECT@@transaction_isolation;结果：READ-UNCOMMITTEDISOLATIONLEVEL2READCOMMITTED：提交读事务只能访问其他事务已经提交的变更，有效防止脏读基本特性READCOMMITTED（提交读）表示一个事务只能访问其他事务已经提交的变更。这能有效避免脏读，但可能会出现不可重复读异常。实现机制：写锁阻塞：写操作会阻塞其他事务的读操作提交后可见：事务提交后其他事务才能看到变更异常防护防止脏读✓不会读取到未提交的临时数据不可重复读✗同一事务内多次读取可能结果不同幻读✗查询结果集数量可能发生变化设置命令--设置隔离级别为提交读SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;--验证设置SELECT@@transaction_isolation;结果：READ-COMMITTEDISOLATIONLEVEL3REPEATABLEREAD：可重复读MySQL默认隔离级别，保证同一事务中多次读取结果一致MySQL默认隔离级别REPEATABLEREAD（可重复读）是MySQL默认的事务并发调度隔离级别。这个隔离级别能保证在同一个事务中多次访问同样记录的结果是一样的，避免了不可重复读的问题。核心特性：快照一致性：事务内多次读取同一数据结果相同MVCC支持：通过多版本并发控制实现性能平衡：在一致性和性能之间取得平衡异常防护防止脏读✓不会读取未提交的临时数据防止不可重复读✓同一事务内多次读取结果一致防止幻读✓InnoDB通过MVCC机制解决幻读设置命令--设置隔离级别为可重复读SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;--验证设置（默认）SELECT@@transaction_isolation;结果：REPEATABLE-READISOLATIONLEVEL4SERIALIZABLE：可串行化最高隔离级别，强制事务串行调度，能避免所有并发异常最高隔离级别SERIALIZABLE（可串行化）是最高隔离级别，强制事务串行调度，能避免丢失更新、脏读、不可重复读和幻读的异常。实现方式：严格加锁：读取时加共享锁，写入时加排他锁串行执行：事务完全串行化，无并发完全保护：完全避免所有并发异常异常防护防止丢失更新✓通过严格加锁避免更新冲突防止脏读✓只能读取已提交的数据防止不可重复读✓多次读取结果完全一致防止幻读✓完全避免幻读现象性能影响性能严重下降由于强制事务串行执行，高并发情况下数据库系统性能会降低。事务需要等待前一个事务释放锁才能执行。吞吐量下降并发事务变成串行执行响应时间增加事务需要等待锁释放死锁风险大量锁竞争可能导致死锁设置命令--设置隔离级别为可串行化SETSESSIONTRANSACTIONISOLATIONLEVELSERIALIZABLE;--验证设置SELECT@@transaction_isolation;结果：SERIALIZABLEISOLATIONLEVELCONFIGURATION隔离级别的查询与设置展示查询和设置隔离级别的具体命令及作用范围查询隔离级别--查询当前会话隔离级别SELECT@@transaction_isolation;--或（旧版本语法）SELECT@@tx_isolation;--查询全局隔离级别SELECT@@global.transaction_isolation;示例输出：++|@@transaction_isolation|++|REPEATABLE-READ|++Set语法格式完整语法：SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVEL{READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE}参数说明：GLOBAL：设置全局隔离级别（所有新连接）SESSION：设置当前会话隔离级别（默认）：设置当前会话隔离级别设置示例--设置会话级隔离级别SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;仅影响当前会话--设置全局隔离级别SETGLOBALTRANSACTIONISOLATIONLEVELREADCOMMITTED;影响所有新建立的连接（需要SUPER权限）--临时设置（当前会话）SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;仅影响当前会话的下一个事务配置文件设置--在f配置文件中设置[mysqld]transaction-isolation=REPEATABLE-READ#或（旧版本）[mysqld]tx_isolation=REPEATABLE-READ修改配置文件后需要重启MySQL服务重要提示不同存储引擎对隔离级别的支持不同。MyISAM不支持事务，因此不支持上述四种标准隔离级别中的任何一种。推荐使用InnoDB引擎。04锁机制04LOCKMECHANISM锁机制概述为什么需要锁机制？在多用户多会话访问同一数据资源的并发操作中，为了保证数据的一致性和系统的稳定性，数据库系统除了事务机制的支持，还有锁机制，MySQL的锁机制是保证数据库事务性、一致性和隔离性的重要策略。锁的核心作用：并发控制：协调多个事务对同一数据的并发访问冲突防止：避免数据竞争和不一致完整性保证：确保ACID特性的实现锁的基本概念锁的定义一种同步机制，用于控制对共享资源的并发访问锁的持有者获取锁的事务或会话，可以对数据进行操作锁的等待者等待获取锁的事务，需要阻塞直到锁释放锁与事务的关系1.事务获取锁事务在操作数据前先获取相应的锁2.锁保护数据锁防止其他事务并发修改被保护的数据3.事务提交释放锁事务提交或回滚时释放所有持有的锁4.等待事务获取锁等待的事务可以获取释放的锁继续执行锁与隔离级别隔离级别的实现依赖于锁机制。不同的隔离级别使用不同的加锁策略：READUNCOMMITTED：几乎不加锁READCOMMITTED：写操作加排他锁REPEATABLEREAD：读加共享锁，写加排他锁SERIALIZABLE：严格加锁，串行执行LOCKGRANULARITY锁的粒度：作用范围分类详细说明锁的粒度概念，介绍不同存储引擎支持的锁粒度锁的粒度概念锁的粒度表示锁的作用范围。锁的粒度可分为服务器级锁(Server-levelLocking)和存储引擎级锁(Storage-engine-levelLocking)。粒度与性能的关系：粗粒度：开销小，但并发度低细粒度：开销大，但并发度高平衡：根据业务选择合适的粒度存储引擎支持InnoDB存储引擎MySQL默认存储引擎，支持行级锁和表级锁支持表级锁（TABLELOCK）支持行级锁（ROWLOCK）MyISAM存储引擎仅支持表级锁支持表级锁（TABLELOCK）不支持行级锁三种锁粒度对比表级锁（TableLock）开销小并发低作用范围是整张表，粒度较大，多事务不合理使用容易造成死锁页级锁（PageLock）开销中并发中锁定表中某些行（称作页），粒度介于行级锁和表级锁之间行级锁（RowLock）开销大并发高作用范围是一条记录，粒度较小，但系统开销较大LOCKTYPES隐式锁与显式锁对比两种锁的使用方式和适用场景隐式锁ImplicitLock隐式锁是数据库系统自动加的锁，用户在执行SQL操作时无需显式指定，数据库会根据操作类型和隔离级别自动管理锁。自动加锁示例：UPDATEusersSETname='张三'WHEREid=1;↓数据库自动加排他锁（X锁）执行成功，自动释放锁显式锁ExplicitLock显式锁是数据库管理员根据需要加的锁，通过特定的SQL命令手动获取和释放锁，提供对锁的精确控制。显式加锁示例：LOCKTABLESusersWRITE;UPDATEusersSETname='张三'WHEREid=1;UNLOCKTABLES;使用建议：推荐使用隐式锁，让数据库自动管理锁。只有在特殊需求下才使用显式锁，并且要注意及时释放锁，避免死锁和性能问题。LOCKCLASSIFICATION锁的类型：按粒度分类详细介绍表级锁、行级锁、页级锁的特点和适用场景表级锁TableLock作用范围是整张表，粒度较大，一定程度上会限制数据访问的并发性能，多事务不合理使用表级锁容易造成死锁。特点：实现简单，开销小并发性能差容易产生锁冲突页级锁PageLock将锁定表中某些行（称作页），其粒度介于行级锁和表级锁之间，页级锁能提供比行级锁更小的开销，能提供比表级锁更高的并发性能。特点：开销和并发性平衡锁定一组记录可能出现页级冲突行级锁RowLock作用范围是一条记录，粒度较小，但系统开销较大。行级锁能提供最高的并发性能，但锁管理的开销也最大。特点：并发性能最高实现复杂，开销大支持不同行不同事务锁粒度对比表锁类型开销并发冲突概率表级锁小低高页级锁中中中行级锁大高低LOCKMODES锁的类型：按操作权限分类说明共享锁、排他锁、意向锁的概念和区别共享锁（S锁）共享锁（S锁）又称读锁，一事务在读一行数据，共享锁允许其他事务对该数据只能读不能写。S锁的特性：共享性：多个事务可以同时持有S锁只读保护：防止其他事务修改数据不阻止读：其他事务仍可读取数据加锁示例：SELECT*FROMusersWHEREid=1LOCKINSHAREMODE;排他锁（X锁）排他锁（X锁）又称写锁，一事务在写一行数据，排他锁会限制其他事务对该数据既不能读也不能写。X锁的特性：排他性：只有一个事务能持有X锁完全保护：阻止其他事务读写阻塞S锁：其他事务不能获取S锁加锁示例：SELECT*FROMusersWHEREid=1FORUPDATE;意向锁（IS/IX锁）意向锁是一种表级锁，表明事务希望在表的某些行数据上加共享锁或排他锁。意向锁主要是为了支持多粒度锁定。意向锁的类型：意向共享锁（IS锁）事务打算在表的某些行上加S锁意向排他锁（IX锁）事务打算在表的某些行上加X锁锁兼容性矩阵S锁X锁IS/IX锁S锁✓✗✓X锁✗✗✗IS锁✓✗✓IX锁✗✗✓✓表示兼容，可以同时持有；✗表示冲突，需要等待LOCKOPERATIONS锁定与解锁命令介绍LockTables和UnlockTables命令的语法和使用方法LOCKTABLES命令基本语法：LOCKTABLEStbl_name[[AS]alias]lock_type[,tbl_name[[AS]alias]lock_type]...lock_type:READ[LOCAL]|[LOW_PRIORITY]WRITE锁类型说明：READ：共享锁，允许其他会话读但不允许写READLOCAL：允许并发插入WRITE：排他锁，不允许其他会话读写LOW_PRIORITYWRITE：低优先级写锁使用示例--给表加读锁LOCKTABLESpatientsREAD;--给表加写锁LOCKTABLESpatientsWRITE;--给多个表加不同的锁LOCKTABLESpatientsREAD,doctorsWRITE;UNLOCKTABLES命令基本语法：UNLOCKTABLES;命令说明：不需要指定表名，会释放当前会话持有的所有表锁执行LOCKTABLES时会自动释放之前持有的锁事务提交或回滚时也会自动释放锁完整示例--1.锁定表LOCKTABLESpatientsWRITE;QueryOK,0rowsaffected(0.00sec)--2.执行数据操作UPDATEpatientsSETpatientName='张雯雯'WHEREpatientID='p001';QueryOK,1rowaffected