PostgreSQL事务隔离级别：让数据库操作不再“见鬼“

先来认识两个捣蛋鬼1. 脏读 - 读到了草稿数据大白话解释想象你在抄学霸同桌的作业学霸正在修改数学题的答案从5改成8你看了一眼“哦答案是8”赶紧抄下来结果学霸发现8不对又改回了5并提交了作业你抄到的8就是脏数据——不真实、不准确的数据数据库例子你和小张一起维护一个员工工资表。小张正在操作他把小王的工资从5000改成了8000。但他还没点击保存这个8000只是草稿。就在这个时候你去查了一下小王的工资。系统直接告诉你现在是8000。你一看“哇小王涨工资了”然后你基于这个8000的工资开始做你的奖金计算表。结果小张发现改得不合适又撤销了修改最后点击了保存。数据库里小王的工资其实还是5000。这下尴尬了你读到的那个8000就是一个**脏数据**像草稿一样不是最终结果。你后续的所有计算都白做了。核心读到了别人未提交的数据。这个数据就像草稿可能永远都不会变成真的。2. 幻读 - 像遇到了幻觉大白话解释你第一次查询和第二次查询之间有别人偷偷修改了数据导致你两次读到的结果集不一样了感觉像出现了幻觉。可能是**“多了一行”、“少了一行”或者同一行数据变了**。举个例子你要统计公司里所有名字是两个字的人。你第一次查询“把所有名字是两个字的人列出来”。系统返回了3个人小明小红小刚。在你准备做报告的时候老板往数据库里新插入了一个员工叫小刘。接着你再次执行了完全相同的查询“再把所有名字是两个字的人列出来”。系统这次返回了4个人小明小红小刚小刘。你一下就懵了“嗯刚才明明是3个怎么突然冒出来一个我眼花了见鬼了”——这种感觉就是幻读。核心两次相同的查询却因为别人修改了数据插入、更新、删除导致你读到的结果集不一样了像产生了幻觉。简单总结对比一下问题大白话比喻核心原因关注点脏读读到了别人的草稿结果他撤销了。读取了未提交的数据数据的内容错了幻读第一次查有3个再一查变4个见了鬼了。读取期间别人修改了数据数据的结果集变了PostgreSQL的防鬼神器事务隔离级别PostgreSQL提供了4个不同级别的防护措施帮你避免遇到这些捣蛋鬼。每个级别就像不同的安全等级1. 读未提交Read Uncommitted- 最宽松模式大白话解释这个模式就像完全不设防什么都能读到包括别人的草稿。PostgreSQL的实际情况-- PostgreSQL实际上不支持真正的读未提交-- 即使你设置了这个级别PostgreSQL也会自动升级到读已提交SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;为什么PostgreSQL不支持PostgreSQL觉得这个级别太危险了就像让小偷随便进你家一样所以很干脆的不提供这个功能。2. 读已提交Read Committed- “默认模式”大白话解释这是PostgreSQL的默认模式就像只读别人已经确定保存的东西。实际效果✅防止脏读你只能读到别人已经保存的数据❌不能防止幻读别人还是可以偷偷插入新数据举个例子-- 小张的操作BEGIN;UPDATEemployeesSETsalary8000WHEREname小王;-- 还没COMMIT所以这个8000是草稿-- 你的查询读已提交模式SELECTsalaryFROMemployeesWHEREname小王;-- 结果5000只能读到已提交的数据不会读到8000-- 小张提交了COMMIT;-- 你再查询SELECTsalaryFROMemployeesWHEREname小王;-- 结果8000现在可以读到了但是幻读还是会发生-- 你第一次查询SELECTCOUNT(*)FROMemployeesWHEREnameLIKE小_;-- 结果3-- 老板插入了新员工INSERTINTOemployees(name)VALUES(小刘);-- 你第二次查询SELECTCOUNT(*)FROMemployeesWHEREnameLIKE小_;-- 结果4出现了幻读3. 可重复读Repeatable Read- “稳定模式”大白话解释这个模式就像时间暂停在你的事务期间别人不能修改你已经读过的数据。实际效果✅防止脏读✅防止幻读在PostgreSQL中❌不能防止序列化异常举个例子-- 你开始事务BEGIN;SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD;-- 第一次查询SELECTCOUNT(*)FROMemployeesWHEREnameLIKE小_;-- 结果3-- 经理尝试插入新员工会被阻塞INSERTINTOemployees(name)VALUES(小刘);-- 你第二次查询SELECTCOUNT(*)FROMemployeesWHEREnameLIKE小_;-- 结果还是3没有幻读COMMIT;PostgreSQL的特殊之处PostgreSQL的可重复读级别实际上比标准更严格它连幻读都能防止MySQL vs PostgreSQL 对比数据库可重复读级别防止脏读防止幻读实际表现MySQL标准实现✅✅InnoDB基本防止幻读PostgreSQL超标准实现✅✅比标准更严格防止幻读MySQL InnoDB的实际表现MySQL的InnoDB存储引擎在可重复读级别下通过Next-Key Lock机制实际上也能基本防止幻读。但相比PostgreSQL在某些边缘情况下可能仍有细微差异。边缘情况举例-- MySQL中的边缘情况-- 事务ABEGIN;SELECTCOUNT(*)FROMemployeesWHEREsalary5000;-- 结果3-- 事务B在另一个连接中INSERTINTOemployees(name,salary)VALUES(小刘,6000);COMMIT;-- 事务A继续UPDATEemployeesSETdepartment技术部WHEREsalary5000;-- 这里可能会影响新插入的小刘导致幻读SELECTCOUNT(*)FROMemployeesWHEREsalary5000;-- 结果4出现了幻读COMMIT;为什么会出现这种情况MySQL的Next-Key Lock主要防止SELECT查询的幻读但在UPDATE操作时可能会看到其他事务新插入的记录导致更新范围扩大从而产生幻读。4. 序列化Serializable- “最严格模式”大白话解释这个模式就像排队办事所有操作必须一个接一个不能同时进行。实际效果✅防止脏读✅防止幻读✅防止序列化异常❌性能最慢举个例子-- 你开始序列化事务BEGIN;SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;-- 你的操作SELECTCOUNT(*)FROMemployeesWHEREdepartment技术部;-- 结果5-- 小强也在做类似操作-- 如果你们两个的操作有冲突其中一个会被回滚-- 就像两个人同时要修改同一个文件系统会要求重试简单总结对比一下隔离级别防止脏读防止幻读性能适用场景PostgreSQL特点MySQL特点读未提交❌❌最快几乎不用PostgreSQL不支持支持但危险读已提交✅❌快日常使用PostgreSQL默认支持可重复读✅✅中等需要一致性比标准更严格MySQL默认InnoDB基本防止幻读序列化✅✅最慢最高一致性要求完全隔离完全隔离关键差异PostgreSQL默认读已提交性能优先不能防止幻读MySQL默认可重复读一致性优先基本防止幻读幻读防护都需要在可重复读级别下才能防止幻读主要区别PostgreSQL默认更宽松MySQL默认更严格为什么PostgreSQL选择读已提交作为默认1. 性能与并发性的平衡读已提交减少了锁的持有时间提高并发性能每个查询只看到查询开始前已提交的数据避免脏读相比可重复读减少了资源消耗和锁竞争2. 满足大多数应用需求防止脏读已经解决了大部分数据一致性问题很多应用场景中不可重复读和幻读的影响较小开发者可以根据需要选择更高的隔离级别3. 与其他数据库的兼容性Oracle也使用读已提交作为默认降低从其他数据库迁移到PostgreSQL的难度设置隔离级别的方法-- 方法1设置当前事务必须在事务开始前设置BEGIN;SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;-- 你的操作...COMMIT;-- 方法2设置会话默认影响当前会话的所有后续事务SETSESSIONdefault_transaction_isolationread committed;-- 方法3设置全局默认需要超级用户权限ALTERSYSTEMSETdefault_transaction_isolationread committed;SELECTpg_reload_conf();-- 重新加载配置-- 方法4在连接字符串中设置-- postgresql://user:passhost/db?options-c default_transaction_isolationread_committed-- 方法5查看当前隔离级别SHOWdefault_transaction_isolation;总结PostgreSQL提供了不同级别的防护措施帮你避免遇到那些见鬼的数据问题