企业官网建设流程全解析

1688网站建设与维护,电商网站的建设背景图片,html5网站单页模板,织梦网站调整“数据库死锁了”——这句话我在线上听过无数次。 锁是MySQL保证数据一致性的核心机制#xff0c;但用不好就是各种问题。这篇从原理到排查#xff0c;把锁这块讲透。 MySQL锁的分类 按粒度分 锁类型说明开销并发度引擎表锁锁整张表低低MyISAM/InnoDB行锁锁单行高高InnoDB…“数据库死锁了”——这句话我在线上听过无数次。锁是MySQL保证数据一致性的核心机制但用不好就是各种问题。这篇从原理到排查把锁这块讲透。MySQL锁的分类按粒度分锁类型说明开销并发度引擎表锁锁整张表低低MyISAM/InnoDB行锁锁单行高高InnoDB间隙锁锁区间高中InnoDB按模式分锁类型说明兼容性共享锁S锁读锁多个事务可以同时持有与S锁兼容与X锁互斥排他锁X锁写锁只能有一个事务持有与S锁、X锁都互斥InnoDB行锁详解行锁的三种形式1. 记录锁Record Lock锁住索引记录本身-- 锁住id1这一行 SELECT * FROM users WHERE id 1 FOR UPDATE;2. 间隙锁Gap Lock锁住索引记录之间的间隙防止幻读-- 假设表里有id: 1, 5, 10 -- 这条SQL会锁住(1,5)这个间隙 SELECT * FROM users WHERE id 1 AND id 5 FOR UPDATE;3. 临键锁Next-Key Lock记录锁 间隙锁默认锁类型-- 锁住id5这条记录以及(1,5]这个范围 SELECT * FROM users WHERE id 5 FOR UPDATE;加锁规则这块有点绕记几个关键点唯一索引等值查询只加记录锁如果记录存在唯一索引范围查询加临键锁普通索引查询加临键锁无索引查询锁全表-- 假设id是主键name有普通索引 -- 情况1唯一索引等值只锁一行 SELECT * FROM users WHERE id 1 FOR UPDATE; -- 情况2普通索引等值锁记录间隙 SELECT * FROM users WHERE name 张三 FOR UPDATE; -- 情况3无索引锁全表 SELECT * FROM users WHERE age 25 FOR UPDATE; -- age没索引这就是为什么要建索引的原因之一没索引会锁表死锁是怎么产生的经典场景两个事务互相等待对方释放锁。事务A 事务B -------------------------------------------------- BEGIN; BEGIN; UPDATE t SET x1 WHERE id1; -- 锁住id1 UPDATE t SET x2 WHERE id2; -- 锁住id2 UPDATE t SET x1 WHERE id2; -- 等待id2的锁 UPDATE t SET x2 WHERE id1; -- 等待id1的锁 -- 死锁MySQL会检测到死锁选择一个事务回滚。死锁排查实战1. 查看死锁日志SHOW ENGINE INNODB STATUS\G找到LATEST DETECTED DEADLOCK部分------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-12-29 10:30:45 0x7f8a12345678 *** (1) TRANSACTION: TRANSACTION 12345, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 100, OS thread handle 123456, query id 999 updating UPDATE orders SET status paid WHERE id 100 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table test.orders trx id 12345 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 12346, ACTIVE 3 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 101, OS thread handle 123457, query id 1000 updating UPDATE orders SET status shipped WHERE id 101 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table test.orders trx id 12346 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 50 page no 4 n bits 72 index PRIMARY of table test.orders trx id 12346 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (1)2. 查看当前锁情况-- MySQL 8.0 SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits; -- MySQL 5.7 SELECT * FROM information_schema.innodb_locks; SELECT * FROM information_schema.innodb_lock_waits;3. 查看正在等待的事务SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id w.blocking_trx_id JOIN information_schema.innodb_trx r ON r.trx_id w.requesting_trx_id;4. 查看长事务SELECT trx_id, trx_state, trx_started, NOW() - trx_started AS duration, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx WHERE trx_state RUNNING AND NOW() - trx_started 10 -- 超过10秒 ORDER BY trx_started;常见死锁场景及解决场景1相反顺序更新-- 事务A UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2; -- 事务B UPDATE accounts SET balance balance - 100 WHERE id 2; UPDATE accounts SET balance balance 100 WHERE id 1;解决固定更新顺序按id排序后更新-- 统一按id从小到大更新 UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2;场景2间隙锁冲突-- 假设表里有id: 1, 10, 20 -- 事务A SELECT * FROM t WHERE id 5 FOR UPDATE; -- 锁住(1,10) INSERT INTO t VALUES (7); -- 要插入(1,10)等待 -- 事务B SELECT * FROM t WHERE id 15 FOR UPDATE; -- 锁住(10,20) INSERT INTO t VALUES (8); -- 要插入(1,10)等待A释放解决降低隔离级别到RC没有间隙锁减少锁持有时间用唯一索引避免间隙锁场景3唯一索引插入冲突-- 表有唯一索引 (user_id, product_id) -- 事务A INSERT INTO cart (user_id, product_id) VALUES (1, 100); -- 加插入意向锁 -- 事务B INSERT INTO cart (user_id, product_id) VALUES (1, 100); -- 冲突等待解决用INSERT ... ON DUPLICATE KEY UPDATEINSERT INTO cart (user_id, product_id, quantity) VALUES (1, 100, 1) ON DUPLICATE KEY UPDATE quantity quantity 1;避免死锁的最佳实践1. 固定更新顺序// 转账固定按id从小到大锁 func Transfer(fromID, toID int, amount int) error { ids : []int{fromID, toID} sort.Ints(ids) tx : db.Begin() // 按顺序锁定 for _, id : range ids { tx.Exec(SELECT * FROM accounts WHERE id ? FOR UPDATE, id) } // 执行转账 tx.Exec(UPDATE accounts SET balance balance - ? WHERE id ?, amount, fromID) tx.Exec(UPDATE accounts SET balance balance ? WHERE id ?, amount, toID) return tx.Commit().Error }2. 缩短事务时间// 不好事务里做了很多事 tx : db.Begin() result : callExternalAPI() // 调用外部接口可能很慢 tx.Create(result) tx.Commit() // 好尽快完成事务 result : callExternalAPI() // 事务外调用 tx : db.Begin() tx.Create(result) tx.Commit()3. 合理使用索引-- 没索引会锁全表 UPDATE users SET status active WHERE created_at 2024-01-01; -- 有索引只锁需要的行 CREATE INDEX idx_created_at ON users(created_at); UPDATE users SET status active WHERE created_at 2024-01-01;4. 降低隔离级别-- RC级别没有间隙锁死锁概率低 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 或者全局设置 SET GLOBAL transaction_isolation READ-COMMITTED;5. 使用乐观锁-- 用版本号避免锁 UPDATE products SET stock stock - 1, version version 1 WHERE id 1 AND version 10; -- 检查影响行数为0说明被别人改了重试6. 设置锁等待超时-- 等待超时自动放弃默认50秒 SET innodb_lock_wait_timeout 10;监控告警死锁监控-- 查看死锁次数 SHOW GLOBAL STATUS LIKE Innodb_deadlocks; -- 定时检查增量告警 SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME Innodb_deadlocks;长事务监控-- 超过60秒的事务 SELECT * FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) 60;锁等待监控-- 锁等待超过5秒的 SELECT * FROM performance_schema.data_lock_waits WHERE TIMESTAMPDIFF(SECOND, REQUESTING_ENGINE_LOCK_ID, NOW()) 5;一个真实案例线上报警订单支付接口频繁超时。排查过程看慢查询日志发现大量UPDATE卡住SHOW ENGINE INNODB STATUS看到死锁分析死锁日志事务1: UPDATE orders SET statuspaid WHERE order_noA001 事务2: UPDATE orders SET statuspaid WHERE order_noA002发现order_no没有索引导致锁全表两个事务都锁了全表互相等待解决ALTER TABLE orders ADD INDEX idx_order_no(order_no);加完索引行锁替代表锁问题解决。总结MySQL锁的几个要点InnoDB行锁是锁索引无索引会锁表死锁的本质是循环等待打破循环就能避免固定更新顺序是避免死锁最有效的方法事务要短锁持有时间越短越好RC级别比RR少了间隙锁死锁概率更低遇到死锁不要慌SHOW ENGINE INNODB STATUS看日志分析是哪两个SQL冲突然后针对性优化。