MySQL权限管理、数据一致性、版本差异

一、MySQL权限管理（高频核心）

1. MySQL的权限体系是怎样的？核心权限类型有哪些？（基础必答）

答案： MySQL的权限体系采用“层级化管理”模式，基于“用户+主机”的双重校验，核心是“最小权限原则”（仅授予业务所需的最小权限，降低安全风险），权限的生效、回收均有明确的层级和规则，底层依赖系统表存储权限信息，原理与实操结合拆解如下。

（1）权限体系核心原理

• 底层存储：MySQL的权限信息主要存储在系统数据库mysql的5张核心表中，权限的授予、回收本质是修改这些系统表的数据，MySQL启动时会加载这些表到内存，后续权限校验优先读取内存，权限修改后需手动刷新（FLUSH PRIVILEGES）才能生效。

  • user表：存储全局级权限（对所有数据库、所有表生效），记录用户账号、密码、主机、全局权限（如ALL PRIVILEGES）；
  • db表：存储数据库级权限（仅对指定数据库生效），记录用户对某个数据库的操作权限；
  • tables_priv表：存储表级权限（仅对指定数据库的指定表生效）；
  • columns_priv表：存储列级权限（仅对指定表的指定列生效）；
  • procs_priv表：存储存储过程、函数级权限（仅对指定存储过程/函数生效）。

• 层级关系（从高到低）：全局权限 > 数据库权限 > 表权限 > 列权限 > 存储过程/函数权限，权限遵循“就近匹配”原则，若用户拥有全局权限，无需再授予数据库/表级权限；若有数据库级权限，无需再授予该数据库下的表级权限。

• 用户校验：MySQL识别用户的唯一标识是“user@host”（用户名+主机地址），例如root@localhost（仅本地可登录）、test@%（所有主机可登录，%为通配符），不同host的同一用户名，权限相互独立。

（2）核心权限类型（按层级分类）

• 全局权限（对所有数据库生效，需授予在user表）

  • 核心权限：ALL PRIVILEGES（所有权限，除了GRANT OPTION）、CREATE USER（创建用户）、GRANT OPTION（授予权限给其他用户）、SHOW DATABASES（查看所有数据库）、SHUTDOWN（关闭MySQL服务）；
  • 作用场景：超级管理员（如root）常用，授予全局权限需谨慎，避免权限泄露。

• 数据库级权限（对指定数据库生效，需授予在db表）

  • 核心权限：CREATE（创建数据库/表）、ALTER（修改表结构）、DROP（删除数据库/表）、INSERT（插入数据）、SELECT（查询数据）、UPDATE（修改数据）、DELETE（删除数据）；
  • 作用场景：业务用户常用，例如授予test@%对test_db数据库的SELECT, INSERT, UPDATE, DELETE权限，满足业务读写需求。

• 表级权限（对指定表生效，需授予在tables_priv表）

  • 核心权限：与数据库级权限类似，但仅作用于指定表，例如授予test@%对test_db.user表的SELECT, UPDATE权限；
  • 作用场景：精细化权限控制，例如仅允许用户查询某张表的部分数据，不允许修改其他表。

• 列级权限（对指定列生效，需授予在columns_priv表）

  • 核心权限：仅SELECT、INSERT、UPDATE，例如授予test@%对test_db.user表的user_name、age列的SELECT权限；
  • 作用场景：敏感数据保护，例如用户表中的密码列（password），仅允许管理员查看，普通用户无权限访问。

• 存储过程/函数权限（对指定存储过程/函数生效，需授予在procs_priv表）

  • 核心权限：EXECUTE（执行存储过程/函数）、ALTER ROUTINE（修改存储过程/函数）、CREATE ROUTINE（创建存储过程/函数）；
  • 作用场景：有存储过程/函数的业务场景，仅授予执行权限，避免用户修改核心逻辑。

（3）权限操作核心语法（面试必背）

• 创建用户：CREATE USER '用户名'@'主机地址' IDENTIFIED BY '密码';（例如：CREATE USER 'test'@'%' IDENTIFIED BY 'Test@123456';）；
• 授予权限：GRANT 权限1,权限2 ON 数据库.表 TO '用户名'@'主机地址' [WITH GRANT OPTION];（例如：GRANT SELECT, INSERT ON test_db.* TO 'test'@'%';）；
• 回收权限：REVOKE 权限1,权限2 ON 数据库.表 FROM '用户名'@'主机地址';（例如：REVOKE INSERT ON test_db.* FROM 'test'@'%';）；
• 查看权限：SHOW GRANTS FOR '用户名'@'主机地址';；
• 删除用户：DROP USER '用户名'@'主机地址';；
• 刷新权限：FLUSH PRIVILEGES;（权限修改后必须执行，否则不生效）。

（4）权限管理注意事项（面试高频延伸）

• 密码安全：禁止使用弱密码（如123456），建议使用“字母+数字+特殊符号”，可通过validate_password插件强制密码复杂度；
• 最小权限原则：避免授予ALL PRIVILEGES和GRANT OPTION（防止用户将权限授予他人），仅授予业务所需权限；
• 主机限制：避免使用%（所有主机可登录），尽量指定具体的主机地址（如公司内网IP），降低远程登录风险；
• 权限过期：MySQL 8.0+支持设置用户权限过期时间（CREATE USER 'test'@'%' IDENTIFIED BY 'Test@123' PASSWORD EXPIRE INTERVAL 90 DAY;），定期更换密码；
• 系统表保护：禁止修改mysql数据库下的系统表（如user、db），避免权限紊乱，若误修改，需通过备份恢复。

2. MySQL 5.7与8.0的权限管理有哪些核心差异？（高频考点）

答案： MySQL 8.0对权限管理进行了重大优化，解决了5.7的权限冗余、安全隐患等问题，核心差异集中在用户管理、权限控制、密码策略、安全特性四个方面，结合原理和实操细节拆解如下，面试需明确区分。

• （1）用户管理差异（核心变化）

  • MySQL 5.7：支持CREATE USER和GRANT合并创建用户（如GRANT SELECT ON test_db.* TO 'test'@'%' IDENTIFIED BY 'Test@123';），无需单独执行CREATE USER；存在匿名用户（''@localhost），默认无密码，存在安全隐患；
  • MySQL 8.0：取消“GRANT创建用户”的方式，必须先执行CREATE USER创建用户，再授予权限（否则报错）；删除默认匿名用户，默认禁用root远程登录，安全性提升；支持用户角色管理（新增ROLE功能），可将多个权限打包为角色，批量授予用户，简化权限管理。

• （2）权限控制差异

  • MySQL 5.7：全局权限、数据库权限、表权限等层级划分不够清晰，存在权限冗余；不支持权限回收的精细化控制，回收权限后需手动刷新；
  • MySQL 8.0：优化权限层级，新增“服务级权限”（如CONNECTION_ADMIN，管理数据库连接）；支持权限的“部分回收”，例如授予用户对某表的所有权限后，可单独回收DELETE权限；权限修改后无需手动执行FLUSH PRIVILEGES，自动生效（修改系统表后实时同步到内存）。

• （3）密码策略差异

  • MySQL 5.7：密码存储采用mysql_native_password加密算法，加密强度较低；validate_password插件需手动安装启用，密码复杂度规则简单；
  • MySQL 8.0：默认采用caching_sha2_password加密算法，加密强度更高，更安全；validate_password插件默认启用，密码复杂度规则更严格（默认长度≥8位，包含大小写、数字、特殊符号）；支持密码过期、密码历史记录（禁止重复使用最近N次密码）。

• （4）安全特性差异

  • MySQL 5.7：无密码 policies 管理，无角色管理，权限泄露风险较高；不支持SSL/TLS加密连接的强制启用；
  • MySQL 8.0：新增角色管理（CREATE ROLE、GRANT ROLE），可批量管理用户权限；支持SSL/TLS加密连接默认启用，禁止非加密连接（可通过配置关闭）；新增password_reuse_history（密码历史）、password_require_current（修改密码需验证原密码）等安全参数，提升密码安全性。

二、MySQL版本差异（高频补充）

1. MySQL 5.7与8.0的核心差异有哪些？（除权限外，重点考点）

答案： MySQL 8.0是MySQL的重大版本更新，除了权限管理的优化，还在性能、语法、存储、安全等多个维度进行了升级，解决了5.7的诸多痛点，面试需重点掌握核心差异，尤其是生产环境升级相关的考点，原理与实操结合如下。

（1）性能优化差异（核心亮点）

• 查询优化：MySQL 8.0优化了查询优化器，支持“哈希连接”（Hash Join），解决了5.7中多表关联仅支持嵌套循环连接、合并连接的问题，大幅提升多表关联查询效率（尤其是大数据量场景）；新增“自适应哈希索引”（AHI）的优化，自动调整索引策略，适配不同查询场景。
• 事务性能：InnoDB引擎优化，事务提交时的日志写入效率提升，减少锁等待时间；支持“原子DDL”（如ALTER TABLE），DDL操作要么全部成功，要么全部失败，避免5.7中DDL中断导致表结构损坏的问题。
• 并发性能：提升了InnoDB的并发处理能力，支持更多的并发连接（默认最大连接数从5.7的151提升到151），优化了锁机制，减少死锁概率；新增“并行查询”（Parallel Query），支持多线程并行执行查询，提升大数据量查询效率。

（2）语法与功能差异

• 新增语法：

  • 窗口函数（高频考点）：新增ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()等窗口函数，支持复杂的数据分析（如排名、分组统计），无需通过子查询实现，简化SQL编写；
  • CTE（公共表表达式）：支持WITH语句，可定义临时结果集，简化复杂子查询，提升SQL可读性（如WITH temp AS (SELECT * FROM user WHERE age > 30) SELECT * FROM temp;）；
  • JSON增强：支持JSON数据类型的更多操作（如JSON_TABLE()，将JSON数据转换为表结构），JSON查询效率提升，适配NoSQL混合存储场景。

• 废弃语法：

  • 废弃ENGINE=InnoDB DEFAULT CHARSET=utf8，推荐使用ENGINE=InnoDB DEFAULT CHARSET=utf8mb4（utf8mb4支持emoji表情，解决5.7中utf8仅支持3字节字符的问题）；
  • 废弃mysql_install_db脚本，改用mysqld --initialize初始化数据库；
  • 废弃password()函数，改用ALTER USER 'user'@'host' IDENTIFIED BY 'new_password';修改密码。

（3）存储与文件差异

• 数据字典：MySQL 8.0将数据字典（表结构、列信息等）存储在InnoDB引擎的系统表中，替代了5.7中的.frm文件，提升了数据字典的访问效率，避免了.frm文件丢失导致的表结构损坏；
• 日志优化：二进制日志（binlog）默认采用ROW格式，提升了数据恢复的准确性；新增“binlog_checksum”参数，对binlog日志进行校验，避免日志损坏；
• 临时表：临时表的存储位置优化，避免了5.7中临时表占用系统表空间的问题，提升了临时表的读写效率。

（4）生产环境升级注意事项（面试延伸）

• 版本兼容：MySQL 8.0不兼容5.7的部分语法（如废弃的函数、语法），升级前需检查业务SQL，修改不兼容的语句；
• 密码加密：5.7的mysql_native_password加密方式在8.0中仍支持，但推荐升级为caching_sha2_password，需修改用户密码加密方式；
• 数据迁移：升级时需通过mysqldump或xtrabackup备份数据，避免升级失败导致数据丢失；
• 配置调整：8.0的部分配置参数与5.7不同（如默认字符集、最大连接数），需根据业务场景调整配置文件（my.cnf）。

三、数据一致性保障（非分库分表场景，高频难点）

1. 非分库分表场景下，如何保证MySQL的数据一致性？（核心考点）

答案： 非分库分表场景下，数据一致性主要面临“单库多表事务”“数据误操作”“数据同步异常”“硬件故障”四大问题，核心保障手段围绕“事务特性”“校验机制”“异常处理”展开，每个方案均拆解原理、实现细节和适用场景，确保覆盖面试高频提问。

（1）基于事务ACID特性，保障单库多表事务一致性（基础）

• 核心原理：利用MySQL的事务ACID特性（原子性、一致性、隔离性、持久性），确保单库中多表操作要么全部成功，要么全部失败，避免“部分成功、部分失败”导致的数据不一致。

• 关键实现细节：

  • 原子性（Atomicity）：通过InnoDB的undo log（回滚日志）实现，事务执行过程中，若出现错误（如SQL报错、服务器宕机），MySQL会通过undo log回滚所有已执行的操作，恢复到事务开始前的状态；
  • 一致性（Consistency）：事务执行前后，数据的完整性约束（主键、外键、唯一约束）不被破坏，例如外键关联的表，删除主表数据时，需同步删除从表数据（或设置外键为NULL），避免数据孤岛；
  • 隔离性（Isolation）：通过锁机制和MVCC（多版本并发控制），避免多个事务并发执行时出现脏读、不可重复读、幻读，确保事务之间相互隔离，不影响数据一致性（推荐使用RR隔离级别，默认级别）；
  • 持久性（Durability）：通过InnoDB的redo log（重做日志）实现，事务提交后，数据会被写入redo log，即使服务器宕机，重启后MySQL会通过redo log恢复数据，确保事务提交后的数据不丢失。

• 实操示例：用户下单（单库中修改订单表、库存表），通过事务包裹操作： BEGIN; `` INSERT INTO order (order_no, user_id, amount) VALUES ('ORDER123', 1001, 100); `` UPDATE stock SET num = num - 1 WHERE product_id = 101; `` COMMIT; 若INSERT或UPDATE任一操作失败，执行ROLLBACK;，回滚所有操作，确保订单和库存数据一致。

• 注意事项：避免在事务中执行耗时操作（如大量查询、外部接口调用），防止事务长时间持有锁，导致并发性能下降；避免事务嵌套，MySQL不支持真正的事务嵌套，嵌套事务会被当作单个事务处理，容易出现回滚异常。

（2）数据校验机制，兜底数据一致性（核心补充）

• 方案1：主键、外键、唯一约束（预防型）

  • 核心原理：通过MySQL的约束机制，从源头防止无效数据插入/修改，确保数据完整性，避免数据不一致。

  • 具体实现：

    • 主键约束（PRIMARY KEY）：确保表中每行数据的唯一性，避免重复插入（如user表的user_id设为主键）；
    • 外键约束（FOREIGN KEY）：确保关联表之间的数据一致性（如order表的user_id关联user表的user_id，禁止插入不存在的user_id）；
    • 唯一约束（UNIQUE）：确保指定列的数据唯一（如user表的phone列设为唯一，避免重复手机号）；
    • 非空约束（NOT NULL）：确保指定列不能为NULL（如user表的user_name设为非空，避免空用户名）。

  • 优点：无需额外开发，MySQL自动校验，成本低；

  • 缺点：约束过多会影响写入性能（如外键约束会增加插入/修改的开销），需根据业务场景合理设置。

• 方案2：定时任务校验（兜底型）

  • 核心原理：通过定时任务（如CRON、Quartz），定期校验数据的一致性，发现不一致数据后，执行修复操作（自动修复或人工修复），适合预防“事务异常”“误操作”导致的数据不一致。

  • 具体实现：

    • 校验场景1：关联表数据校验（如order表的user_id在user表中是否存在，不存在则标记为异常数据，人工修复）；
    • 校验场景2：统计数据校验（如订单表的总金额与订单明细表的金额总和是否一致，不一致则自动重新计算）；
    • 校验场景3：数据完整性校验（如库存表的库存数量不能为负数，若出现负数，自动回滚到最近的正确值）；
    • 实现方式：编写SQL脚本（如关联查询、统计查询），通过定时任务执行，输出异常数据日志，触发告警或自动修复。

  • 优点：可兜底各种异常场景，确保数据最终一致；

  • 缺点：存在一定的延迟（定时执行），需编写校验脚本，增加维护成本。

（3）数据误操作的一致性保障（高频场景）

• 方案1：开启binlog日志，实现时间点恢复（PITR）

  • 核心原理：开启binlog日志，记录所有数据修改操作（insert、update、delete），当出现误操作（如误删表、误删数据）时，通过binlog日志恢复到误操作前的时间点，确保数据一致性（原理同备份恢复的PITR，但无需全量备份，适合误操作场景）。

  • 关键步骤：

    • 1. 确认误操作时间点（如误删数据的时间是2026-02-27 15:00:00）；
    • 2. 查看binlog日志列表，找到误操作所在的binlog文件（show binary logs;）；
    • 3. 解析binlog日志，提取误操作前的SQL（mysqlbinlog --stop-datetime="2026-02-27 14:59:59" /var/lib/mysql/mysql-bin.000003 > recover.sql）；
    • 4. 执行恢复脚本，恢复数据（mysql -u root -p < recover.sql）。

• 方案2：开启MySQL的慢查询日志+审计日志，监控误操作

  • 核心原理：通过慢查询日志监控耗时SQL，通过审计日志（如MySQL Enterprise Audit）记录所有用户的操作（登录、SQL执行），当出现误操作时，可快速定位操作人、操作时间、操作内容，便于恢复数据和追溯责任。
  • 注意事项：审计日志会占用一定的磁盘空间，需合理设置日志保留时间；慢查询日志需设置合理的阈值（如long_query_time=1秒），避免日志过多。

四、MySQL常见问题排查（高频实战）

1. MySQL出现慢查询，如何排查和优化？（除SQL优化外，重点考点）

答案： MySQL慢查询的核心排查思路是“定位慢查询SQL → 分析慢查询原因 → 执行优化操作”，除了SQL语句本身的优化（已排除），重点关注“索引失效”“锁等待”“服务器配置”“硬件资源”四个维度，每个步骤均拆解原理、排查方法和实操细节，适配面试实战场景。

（1）第一步：定位慢查询SQL（核心前提）

• 开启慢查询日志：

  • 临时开启（重启失效）：SET GLOBAL slow_query_log = ON;（开启慢查询日志）、SET GLOBAL long_query_time = 1;（设置慢查询阈值，单位秒，SQL执行时间超过1秒记录日志）、SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';（指定慢查询日志路径）；
  • 永久开启（修改配置文件my.cnf）： slow_query_log = ON `` slow_query_log_file = /var/lib/mysql/slow.log `` long_query_time = 1 ``log_queries_not_using_indexes = ON # 记录未使用索引的SQL（可选，便于排查索引失效）

• 查看慢查询日志：

  • 直接查看日志文件：cat /var/lib/mysql/slow.log，日志中会记录慢查询SQL的执行时间、执行用户、执行时间等信息；
  • 使用mysqldumpslow工具（MySQL自带），统计慢查询TOP10：mysqldumpslow -s t -n 10 /var/lib/mysql/slow.log（-s t：按执行时间排序，-n 10：显示前10条）。

• 关键：定位到慢查询SQL后，重点关注“执行时间”“扫描行数”“是否使用索引”，这三个指标是分析慢查询原因的核心。

（2）第二步：分析慢查询原因（除SQL本身外，核心考点）

• 原因1：索引失效（最常见）

  • 核心原理：索引失效会导致MySQL走全表扫描，扫描行数剧增，执行时间变长，常见的索引失效场景（除SQL语法问题外）：

    • 表数据量过小（如少于1000行），MySQL优化器认为全表扫描比走索引更快，自动放弃索引；
    • 索引列存在NULL值、函数操作（如WHERE DATE(create_time) = '2026-02-27'）、隐式转换（如索引列是int类型，查询时用字符串匹配）；
    • 索引碎片化严重（频繁删除、更新数据导致），索引查询效率下降；
    • MySQL优化器选择错误（如统计信息过时，优化器误判扫描行数）。

  • 排查方法：执行EXPLAIN + 慢查询SQL，查看type字段（all表示全表扫描，index表示走索引）、key字段（显示使用的索引，NULL表示未使用索引）、rows字段（扫描行数）。

• 原因2：锁等待（高频场景）

  • 核心原理：多个事务并发执行时，存在锁竞争（如行锁、表锁），导致某个事务长时间等待锁释放，进而表现为慢查询。

  • 排查方法：

    • 查看当前锁等待情况：SHOW ENGINE INNODB STATUS;，在输出结果中找到“TRANSACTIONS”部分，查看锁等待的事务ID、等待的锁类型；
    • 查看当前运行的事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;，找到长时间运行的事务（trx_started时间较早的），判断是否存在锁竞争；
    • 查看锁持有情况：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;，查看哪些事务持有锁，哪些事务在等待锁。

• 原因3：服务器配置不合理

  • 核心原理：MySQL的配置参数（如缓存大小、连接数）不合理，导致资源不足，进而影响查询效率，常见配置问题：

    • innodb_buffer_pool_size（InnoDB缓存大小）过小，导致频繁读取磁盘，查询变慢（推荐设置为服务器内存的50%-70%）；
    • max_connections（最大连接数）过小，导致并发连接排队，查询等待时间变长；
    • query_cache_size（查询缓存）开启（MySQL 8.0已废弃），查询缓存命中率低，反而增加开销。

  • 排查方法：查看MySQL配置文件（my.cnf），对比推荐配置，执行SHOW VARIABLES LIKE '参数名';查看当前参数值。

• 原因4：硬件资源不足

  • 核心原理：服务器CPU、内存、磁盘IO资源不足，导致MySQL无法正常高效运行，查询变慢。

  • 排查方法：

    • 查看CPU使用率：top命令，查看MySQL进程（mysqld）的CPU占用率，若持续过高（超过80%），说明CPU不足；
    • 查看内存使用率：free -m命令，查看内存剩余情况，若内存不足（剩余内存<1G），会导致频繁换页，影响查询；
    • 查看磁盘IO：iostat -x 1命令，查看磁盘读写速度（%util接近100%表示磁盘IO饱和）。

（3）第三步：优化操作（除SQL优化外，重点实操）

• 针对索引失效：

  • 若表数据量过小，无需优化（全表扫描效率已很高）；
  • 避免索引列的函数操作、隐式转换，修改SQL语句（如WHERE create_time >= '2026-02-27 00:00:00' AND create_time < '2026-02-28 00:00:00'替代函数操作）；
  • 重建索引（解决索引碎片化）：ALTER TABLE 表名 ENGINE=InnoDB;（InnoDB引擎会自动重建索引）；
  • 更新统计信息（解决优化器选择错误）：ANALYZE TABLE 表名;。

• 针对锁等待：

  • 终止长时间运行的事务（释放锁）：KILL 事务ID;（事务ID可从INNODB_TRX表中获取）；
  • 优化事务逻辑，减少事务持有锁的时间（如避免在事务中执行外部接口调用、大量查询）；
  • 调整事务隔离级别（如从RR改为RC，减少锁竞争）。

• 针对服务器配置：

  • 调整innodb_buffer_pool_size：根据服务器内存调整，如16G内存设置为10G；
  • 调整max_connections：根据业务并发量调整，如并发量高的业务设置为500-1000；
  • 关闭查询缓存（MySQL 5.7）：SET GLOBAL query_cache_type = OFF;。

• 针对硬件资源不足：

  • 升级CPU、内存（长期解决方案）；
  • 优化磁盘IO：将MySQL数据目录迁移到SSD磁盘（SSD读写速度远高于HDD）；
  • 减少磁盘IO压力：定期清理无用数据、归档历史数据，减少表数据量。

2. MySQL出现死锁，如何排查和解决？（高频难点）

答案： MySQL死锁的核心是“两个或多个事务相互持有对方需要的锁，且都不释放，导致永久等待”，死锁仅发生在InnoDB引擎（支持行锁），MyISAM引擎不支持事务和行锁，不会出现死锁。排查和解决的核心思路是“定位死锁 → 分析死锁原因 → 避免死锁”，结合原理和实操细节拆解如下。

（1）第一步：定位死锁（核心前提）

• 方法1：查看InnoDB状态日志（最常用）

  • 执行命令：SHOW ENGINE INNODB STATUS;，在输出结果中找到“LATEST DETECTED DEADLOCK”部分，该部分会详细记录死锁的相关信息：

    • 死锁发生的时间；
    • 参与死锁的两个事务（TRANSACTION 1、TRANSACTION 2）；
    • 每个事务持有和等待的锁类型（如行锁、表锁）；
    • 每个事务执行的SQL语句。

  • 关键：从日志中提取两个事务的SQL语句和锁信息，分析死锁产生的原因。

• 方法2：开启死锁日志（永久监控）

  • 修改配置文件my.cnf，开启死锁日志： innodb_print_all_deadlocks = ON
  • 死锁日志会写入MySQL的错误日志（默认路径：/var/lib/mysql/主机名.err），可通过查看错误日志，持续监控死锁情况，适合生产环境长期监控。

（2）第二步：分析死锁原因（核心考点）

死锁产生的核心条件（四个同时满足）：互斥条件（锁不可共享）、请求与保持条件（持有一个锁，再请求另一个锁）、不可剥夺条件（锁不能被强制剥夺）、循环等待条件（两个事务相互等待对方的锁）。常见的死锁场景（面试高频）：

• 场景1：两个事务，操作两张表，锁顺序相反

  • 事务1：BEGIN; UPDATE user SET name='test1' WHERE id=1; UPDATE order SET status=1 WHERE id=101; COMMIT;
  • 事务2：BEGIN; UPDATE order SET status=2 WHERE id=101; UPDATE user SET name='test2' WHERE id=1; COMMIT;
  • 死锁原因：事务1持有user表id=1的行锁，等待order表id=101的行锁；事务2持有order表id=101的行锁，等待user表id=1的行锁，形成循环等待，触发死锁。

• 场景2：两个事务，操作同一张表，锁顺序相反

  • 事务1：BEGIN; UPDATE user SET name='test1' WHERE id=1; UPDATE user SET name='test2' WHERE id=2; COMMIT;
  • 事务2：BEGIN; UPDATE user SET name='test3' WHERE id=2; UPDATE user SET name='test4' WHERE id=1; COMMIT;
  • 死锁原因：事务1持有id=1的行锁，等待id=2的行锁；事务2持有id=2的行锁，等待id=1的行锁，形成循环等待。

• 场景3：事务嵌套，锁竞争

  • 事务1开启后，执行UPDATE操作持有行锁，未提交；事务2开启后，执行UPDATE操作请求同一行锁，等待；事务1又请求事务2持有的锁（或其他资源），形成死锁。

（3）第三步：解决和避免死锁（实操重点）

• 紧急解决：终止死锁事务

  • 通过SHOW ENGINE INNODB STATUS;找到死锁的事务ID，执行KILL 事务ID;，终止其中一个事务，释放锁，另一个事务即可继续执行；
  • 注意：MySQL会自动检测死锁（默认开启死锁检测），检测到死锁后，会自动终止其中一个事务（回滚），释放锁，但自动终止的逻辑可能不符合业务需求，建议手动终止。

• 长期避免：优化事务和锁操作（核心）

  • 1. 统一锁顺序：所有事务操作多张表、多行数据时，采用相同的锁顺序（如先操作user表，再操作order表；先更新id小的行，再更新id大的行），避免循环等待；
  • 2. 减少事务持有锁的时间：简化事务逻辑，避免在事务中执行耗时操作（如外部接口调用、大量查询），事务执行完成后立即提交，尽快释放锁；
  • 3. 避免事务嵌套：MySQL不支持真正的事务嵌套，嵌套事务会增加锁竞争的概率，尽量采用扁平事务；
  • 4. 合理使用锁粒度：避免使用表锁（如LOCK TABLES），尽量使用行锁（InnoDB默认行锁），减少锁竞争范围；
  • 5. 调整事务隔离级别：将事务隔离级别从RR（默认）改为RC，RC级别下，InnoDB的锁机制更宽松，可减少死锁概率（但会牺牲部分隔离性，需结合业务场景）；
  • 6. 避免批量更新：批量更新数据时，分批次执行（如每次更新100条），避免一次性持有大量行锁，引发锁竞争。