数据库锁机制：表锁、行锁（Oracle 默认）、共享锁、排他锁、乐观锁、悲观锁、死锁、Hive 中的锁

数据库锁机制是控制并发访问数据的关键技术。本文系统介绍了锁的概念、分类和应用场景1锁通过限制并发访问确保数据一致性类比厕所门锁机制2按粒度分为表锁适合批量操作、行锁适合高并发和页锁3按模式分为共享锁读锁、排他锁写锁和意向锁4对比了悲观锁先加锁和乐观锁后检查的实现差异5分析了死锁成因及解决方案6特别说明Hive采用表锁的设计考量。不同数据库根据应用场景选择适合的锁机制如OLTP系统多用行锁数据仓库倾向表锁。数据库锁机制详解一、什么是锁一句话理解锁就是控制多人同时访问同一数据的机制。生活类比text公共厕所 - 有人进去了 → 锁上门加锁 - 其他人 → 只能等待阻塞 - 里面的人出来 → 打开锁释放锁 - 下一个人 → 才能进去 如果不锁门两个人同时进去...尴尬二、为什么需要锁问题场景假设银行账户余额 1000 元时间操作A取钱500操作B取钱800结果10:00:00读取余额100010:00:01读取余额100010:00:02计算 1000-50050010:00:03计算 1000-80020010:00:04写入余额50010:00:05写入余额20010:00:06❌ 最终余额200多取了300没有锁两个操作互相干扰数据错误有锁text操作A读取余额 → 加锁 → 处理 → 写回 → 释放锁 ↓ 操作B等待...等待...等待... → 读取新余额 → 处理 → 写回三、锁的分类3.1 按粒度分类锁粒度锁的范围并发度开销适用场景表锁整张表低小全表操作如 ALTER TABLE行锁某一行高大OLTP 系统如银行、电商页锁某一页数据中等中等某些数据库如 SQL Server3.2 按模式分类锁模式英文符号含义共享锁Shared LockS读锁多个事务可同时读不能写排他锁Exclusive LockX写锁只能一个事务读写其他不能意向锁Intention LockIS/IX表示准备加更细的锁提高效率3.3 兼容性矩阵共享锁S排他锁X共享锁S✅ 兼容可同时读❌ 不兼容排他锁X❌ 不兼容❌ 不兼容四、行锁 vs 表锁 详解4.1 表锁sql-- MySQL LOCK TABLES emp READ; -- 加表锁共享锁 SELECT * FROM emp; -- 可以读 UPDATE emp SET sal5000; -- ❌ 不能写会阻塞 UNLOCK TABLES; -- 解锁特点锁定整张表简单粗暴适合批量操作数据仓库、ETLHive 主要使用表锁4.2 行锁sql-- MySQL InnoDB BEGIN; SELECT * FROM emp WHERE empno7369 FOR UPDATE; -- 只锁这一行 UPDATE emp SET sal5000 WHERE empno7369; COMMIT; -- 释放锁特点只锁影响的行其他行不受影响适合高并发 OLTP银行、电商Oracle/MySQL InnoDB 默认使用行锁五、乐观锁 vs 悲观锁这是思想层面的分类不是具体的锁实现。类型思想做法适用场景悲观锁肯定有人跟我抢先加锁再操作并发高的场景乐观锁应该没人跟我抢先操作提交时检查并发低的场景5.1 悲观锁示例sql-- MySQL 悲观锁 BEGIN; SELECT * FROM products WHERE id1 FOR UPDATE; -- 加锁 UPDATE products SET stockstock-1 WHERE id1; COMMIT; -- 释放锁5.2 乐观锁示例sql-- 用版本号实现乐观锁 -- 1. 读取数据 版本号 SELECT stock, version FROM products WHERE id1; -- stock10, version5 -- 2. 更新时检查版本号 UPDATE products SET stock9, version6 WHERE id1 AND version5; -- 版本号匹配才更新 -- 3. 如果更新行数0说明被别人改过重试六、死锁6.1 什么是死锁两个事务互相等待对方释放锁谁也动不了。text事务A锁住了行1等待行2 事务B锁住了行2等待行1 结果两个都卡死6.2 死锁示例sql-- 事务A BEGIN; UPDATE accounts SET balancebalance-100 WHERE id1; -- 锁住行1 UPDATE accounts SET balancebalance100 WHERE id2; -- 等待行2 -- 事务B同时执行 BEGIN; UPDATE accounts SET balancebalance-50 WHERE id2; -- 锁住行2 UPDATE accounts SET balancebalance50 WHERE id1; -- 等待行1 -- 死锁两个事务互相等待6.3 如何避免死锁方法说明固定访问顺序总是先更新 id 小的再更新 id 大的减少事务时间尽快 COMMIT不要有用户交互使用超时设置锁等待超时超时后自动放弃死锁检测数据库自动检测杀掉其中一个事务七、Hive 中的锁7.1 Hive 锁的特点特点说明表级锁Hive 主要使用表锁不支持行锁共享锁S读取表时加多个读操作可并发排他锁X写入表时加阻塞其他读写自动加锁执行 SQL 时自动加不需要手动7.2 Hive 锁示例sql-- 读操作 → 加共享锁 SELECT * FROM emp; -- 自动加共享锁允许其他读禁止写 -- 写操作 → 加排他锁 INSERT INTO emp VALUES(9999,张三,CLERK,...); -- 自动加排他锁阻塞其他操作 -- 查看锁 SHOW LOCKS; -- 手动解锁某些情况需要 UNLOCK TABLE emp;7.3 为什么 Hive 用表锁原因说明设计定位数据仓库不是高并发 OLTP批量操作每次处理大量数据行锁意义不大简单可靠表锁简单死锁概率低HDFS 特性HDFS 不支持行级修改八、课堂笔记相关理解你的课堂笔记中提到小文件过多导致跑批任务慢这与锁的关系阶段锁情况说明读取阶段共享锁多个任务可以同时读写入阶段排他锁写任务会阻塞其他操作合并文件排他锁小文件合并时需要独占表导致其他任务等待九、各数据库锁机制对比数据库默认锁粒度支持行锁死锁检测适用场景MySQLInnoDB行锁✅✅OLTP高并发MySQLMyISAM表锁❌❌只读/低并发Oracle行锁✅✅OLTPPostgreSQL行锁✅✅OLTPHive表锁❌❌数据仓库HBase行锁✅✅实时读写十、总结问题答案锁是什么控制多人同时访问同一数据的机制为什么需要锁防止数据不一致如银行取钱行锁 vs 表锁行锁并发高表锁简单共享锁 vs 排他锁共享锁可同时读排他锁独占乐观锁 vs 悲观锁乐观锁假设没人抢悲观锁假设有人抢死锁两个事务互相等待对方释放锁Hive 用什么锁主要用表锁共享锁/排他锁一句话记忆锁就像厕所门锁进去就锁上别人得等出来再打开下一个才能进。目的就是防止两个人同时用同一个数据搞乱账。