原文:数据库事务、隔离级别和锁ACID的真实含义隔离级别和并发控制MySQL和PostgreSQL对比如何写代码 作者:大宽宽 转自:https://cloud.tencent.com/developer/article/1121737 数据库事务、隔离级别和锁ACID的真实含义隔离级别和并发控制MySQL和PostgreSQL对比如何写代码 这是个令大多数后端同学头疼的问题。部分是因为不同的文章、文档充斥着不相容的概念。高层抽象和底层实现混到一起令人傻傻的分不清楚。此外还有一部分是SQL标准和实现压根就不一致。本文期望在众多文献中找到一条容易理解知识线,帮助大家在实际工作中更加容易明白到底怎么使用数据库的事务、隔离级别和锁。 ACID的真实含义 一般都会用ACID来表达事务的特性。A、C、I、D分别代表“原子性”,“一致性”,“隔离性”和“持久性“。这是1983年(恰好是我出生的年份)ACM的一篇期刊文章Principles of Transaction-Oriented Database Recovery给出的。这四条特性指导了30多年来数据库的模型、设计和开发。ACID很重要,基本上每篇关于数据库的事务的书、文章都要把他们拿出来重新解释一遍。但是,似乎很多人看了很多遍这种文章还是不明白(包括我在内)。因为简单列举,解释这4个概念和现实中数据库的实现不那么容易接起来。 我是这样理解这几个概念的: Atomicity,原子性。很令人郁闷的是,这个词表达意思与常规语境下不太一样。对于一门支持并发的编程语言(比如Java,C++),原子性是指一组指令被执行时,不受其他指令的干扰。比如我们可以说“CAS是原子的;给一个整型变量赋值是原子的”等等。但是在ACID语境下,“不受干扰”这层意思其实是后边“隔离性“说的事情。在ACID语境下,原子性是指一组对数据库的改变,要么最终成功执行完成,要不就全部回滚。这就要求数据库系统要实现某种回滚的机制,比如redo/undo log)。所以,也许这里用术语”revertability“可能更适合。与事务性数据库相比,一些NoSQL的数据库也声称支持原子性,但是意义不同。比如Redis事务的原子性的意思可能更接近于“一组指令被执行时,不受其他指令的干扰”,而不是“可以回滚”。 Consistency, 一致性。这个术语的用词也颇为怪异。一般来讲,我们习惯用“一致性”来描述数据在某些条件下可以变成一样的。例如,在描述CPU工作方式时可以说需要主内存在CPU Core1里的缓存和CPU Core2里的缓存是“一致的“;或者,一个分布式数据系统中,A节点从B节点复制数据,A的数据要和B的数据”严格一致”或者“最终一致”。而ACID下的一致性指的是,在事务完成前后,数据都是要在业务意义上是”正确的“,所以也许术语”correctness“更适合这里的意思。但如果这样定义的话,数据库的位置就很尴尬了,因为保证业务是否正确是要业务代码来最终保证的,数据库能做的非常有限。目前数据库里实现的约束检查,比如唯一约束、外键约束、一些enum测检查、一些数据类型/长度/有效数字的检查等等,对于简单的场景还可以使用。对于复杂的业务约束检查,很难或者不可能实现。有一类数据正确性问题正是由于下面隔离性的使用不当而带来的。 真实复杂业务的数据正确性维护一般用正确的业务代码 + 合法性job来定时执行 + 数据库自身的简单合法性防护一起实现。 Isolation,隔离性。是指一组对数据库的并发修改互相不影响。这个概念表面上看来并不是能说得通,因为如果并发修改的是互不相干的数据,那么自然隔离性可以得到满足;如果并发修改的是相关联的,或者就是同一份数据,就必然会相互影响。那么,此时可以做的就是区分哪个修改优先级更加高。而高优先级的修改应该覆盖掉低优先级的修改。但是,现实往往更复杂,因为并发的修改并不一定能够讲明白先来后到的(要不怎么叫并发呢),此时谁应该生效无法很好的定义。另外一种情况是“先读取,再基于读取结果对数据进行修改”这样业务逻辑。比如,先找到可用的库存,有则扣减,没有则提示缺货;再比如先读取当前的计数值,再往上加1。这时保证隔离性的主要问题不在于隔离本身,而在于如果将读取作为对数据修改的前提条件,之后在对数据进行修改的一刹那,读取时的前提条件还是否满足。毕竟读取和写入是两个分开的指令,而在这两个指令中间可能夹杂其他事务对数据的修改。保持隔离性的一个简单做法是保证对关联数据的修改串行化,对应事务性数据库的“Serializable”隔离级别。保证串行化的一种方案是锁,通过锁定可以彻底避免竞争条件。但是大家都能明白加锁对数据库并发的性能负面影响很大,所以就衍生出了几种弱一些的隔离性保证——READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ。此外MVCC能够解决一部分锁带来的问题。这些内容在下文中会详细的讲解。 Duration,持久性。是指对数据的修改,一旦完成,该结果就应当永远不丢失。这是这4个术语中我唯一觉得表面上和实际上意思差不多的一条。在现实当中,一般通过持久性存储设备(比如磁盘/SSD)写入并刷新来保证数据的持久性。如果觉得一个节点不靠谱,可以增加多个副本(replica)一起来保证持久;如果觉得这样还不够靠谱,可以在不同的地理位置的另一个数据中心做备份。实际上绝对的持久性是不存在的,因为整个存储层面有很多不确定因素,比如文件系统本身fsync指令实现有bug,磁盘的固件有bug,供电出现问题造成数据错乱,异步的数据复制没有生效等等。所以在现实当中的数据库,只能在当前成本和技术限制的约束下,尽量维持一定程度的持久性。 所以这么整理起来。实际上事务性数据库实现的是 支持未完成的数据修改回滚的机制,对应“原子性” 力所能及的数据合法性检查,对应“一致性” 保证数据并发的修改的规则,对应“隔离性” 使用基于持久化存储(磁盘、SSD)的方式对数据进行存储,对应“持久性” 这四条中一、二、四不是本文要讨论的内容,不再赘述。后面主要来谈一下第三条——隔离性规则,即隔离级别的实现。 隔离级别和并发控制 SQL92标准定义了四种隔离级别——Read Uncommitted,Read Committed,Repeatable Read和Serializable。定义这4种隔离级别时,制定者主要围绕着基于锁的并发控制来说的。但是后来出现了MVCC,之后主流数据库都开始支持MVCC。有的数据库采用比较纯粹的MVCC实现,比如PostgreSQL;有的则是混杂的,比如MySQL InnoDB。这就会造成数据库的实现和标准的描述有很多出入。 其实我们并不在意标准怎么说。标准只是一个参考,引导我们理解我们能够接触的实际数据库系统的工作原理。我们先从最简单情况谈起。 最不严格的隔离级别 理论上,最不严格的隔离级别应该是不隔离。 不隔离很容易理解,不同的事务可以对同一数据并发的随便改:A事务改了一半的结果B能看到;B改了一半的结果A也能看到;如果A和B反复修改同一个数据,那么彼此的修改可以覆盖。数据系统在没有做隔离防护时,就一定会是这个样子。这样也就无所谓事务了。 这里数据访问冲突可以分为两种: Dirty Read,脏读。即一个事务的没提交之前的修改被另外一个事务可以看到。 Dirty… Continue Reading 【转载】数据库事务、隔离级别和锁ACID的真实含义隔离级别和并发控制MySQL和PostgreSQL对比如何写代码

原文:PostgreSQL防止更新丢失(覆盖) 作者:openwares.net 转自:https://blog.csdn.net/Starrain00/article/details/7333926 PostgreSQL防止更新丢失(覆盖) 所有的数据库都会遇到更新丢失(覆盖)的问题。 更新丢失(覆盖)的详细描述见数据库事务隔离级别。 发生更新丢失(覆盖)问题的关键在于”读取,计算,写回”这个过程中,当前事务使用了过期的(stale)数据,没有将其他并发事务对数据的修改纳入到计算结果中,从而在写回时将其他事务的更新覆盖了。 防止更新丢失(覆盖)的方法 悲观锁 事务中对需要修改的结果集加行锁,常用的就是select for update,或者lock table对整个表加锁。加锁之后,当前事务未处理完成之前,其他所有需要访问锁定行的事务都必须等待。这虽然能解决更新丢失(覆盖)的问题,但很明显会影响数据库的并发性能。 如果并发事务冲突的几率很高,则采用悲观锁可以减少事务回滚并重试的开销。 乐观锁 乐观锁不锁定任何行,当更新数据时再做检查数据是否已经发生了变化。多版本并发控制MVCC(Multiversion concurrency control)可以实现乐观锁。PostgreSQL使用MVCC实现并发控制。PostgreSQL的默认事务隔离级别为读已提交(Read Commited),在这个级别上会发生不可重复读现象,这个隔离级别是无法防止更新丢失(覆盖)的。 但是在可重复读(Repeatable Read)事务隔离级别,可以完全防止更新丢失(覆盖)的问题,如果当前事务读取了某行,这期间其他并发事务修改了这一行并提交了,然后当前事务试图更新该行时,PostgreSQL会提示:ERROR: could not serialize access due to concurrent update事务会被回滚,只能重新开始。 由于使用的是MVCC机制的乐观锁,内部有版本号(这个字段名字叫xmin)来控制并发,所以不会对数据集上锁,对性能的影响是很小的。但如果并发事务冲突的几率比较大,那么事务回滚的开销就比较大了。 总的来说,如果并发事务冲突的几率很低那么应该选择乐观锁,对于PostgreSQL来说,将事务隔离级别提高到Repeatable Read即可。如果事务并发冲突的几率很高,那么可以谨慎的使用悲观锁。 还有一种防止更新丢失(覆盖)的方法叫条件更新,也就是在更新时指定where子句,检测指定的条件是否已经变化来决定是否进行更新。这不是一种通用的解决方案,只能根据业务逻辑来选择特定的检测条件,并不能防止这些检测条件之外的可能存在的更新丢失问题。而且有些情况下可能很难选择合适的更新检测条件,比如一个银行账户,关键的字段有账户号和余额,很难通过WHERE条件来检测当前事务执行期间是否有其他并发事务已经修改了余额并做了提交。所以这种方法只在特定的逻辑环境下有一定的用途。 PostgreSQL可重复读隔离级小实验 先建一张表,并插入一条记录: 下面演示两个事务并发的几种情况,左边为事务A,右边为事务B。 第一种情况: begin transaction isolation level repeatable read ;        | select whatever from sometable where… Continue Reading 【转载】PostgreSQL防止更新丢失(覆盖)

原文:PostgreSQL错误解决:ERROR: current transaction is aborted, commands ignored until end of transaction blockp 作者:Starrain00 转自:https://blog.csdn.net/Starrain00/article/details/7333926 postgresql错误解决 ERROR:current transaction is aborted 2012年03月08日 18:29:31 阅读数:16401 postgresql错误解决 ERROR:current transaction is aborted, commands ignored until end of transaction bl 简单的说,出现该错误是在事务中某个sql执行错误后没有rollback,然后继续执行sql时会报改错。 以下为摘抄的一篇文章,说的比较详细 文件来源:http://blog.csdn.net/hantiannan/archive/2009/09/24/4590259.aspx 在平时的数据库操作编程中我们会时不时会遇到ERROR: current transaction is aborted, commands ignored until end of transaction blockp这样的错误。该怎么解决呢?一般我们都是直接上网查找。如果英文好一点的人的话的从错误信息就能知道大概是那个地方出错了。不过如果经验不是 很丰富的话,还是上网查找比较方便。最基本错误信息可以从官方文档中查看(http://www.postgresql.org/docs/8.4/interactive/errcodes-appendix.html )。… Continue Reading 【转载】PostgreSQL错误解决:ERROR: current transaction is aborted, commands ignored until end of transaction blockp

原文:Rails transactions: The Complete Guide 作者:Mark Daggett 转自:http://markdaggett.com/blog/2011/12/01/transactions-in-rails/ Transactions in Rails DEC 1ST, 2011 Recently I was tasked to write tests for the transactions of an existing application. This gave me the opportunity to learn more about the codebase, while also improving the test coverage. Generally, most of the transaction… Continue Reading [Reproduce]Transactions in Rails

原文:Rails transactions: The Complete Guide 作者:Victor A.M. 转自:http://codeatmorning.com/rails-transactions-complete-guide/   Rails transactions: The Complete Guide Rails transactions are a way to ensure that a set of database operations will only occur if all of them succeed. Otherwise, they will rollback to the previous state of data. Our examples will demonstrate it… Continue Reading [Reproduce]Rails transactions: The Complete Guide

原文:ActiveRecord 事务的一些试验和验证 作者:baya 转自:https://baya.github.io/2015/06/13/activerecord-%E4%BA%8B%E5%8A%A1.html ActiveRecord 事务的一些试验和验证 Jun 13, 2015 Transactions are protective blocks where SQL statements are only permanent if they can all succeed as one atomic action 我们平时说的 一手交钱,一手交货 就是一种事务。 ActiveRecord 中的事务是通过 transaction 方法实现的。 我们首先试验一个基础的例子: ActiveRecord::Base.transaction ActiveRecord::Base.transaction do david.withdrawal(100) mary.deposit(100) end 在试验之前,我们先用 Rails 创建一个 demo, 并创建好相关的 model。 $ rails new ar-transaction-demo… Continue Reading 【转载】ActiveRecord 事务的一些试验和验证