PostgreSQL串行化访问隔离级别分析

1、1, SERIALIZABLE隔离级别介绍SERIALIZABLE事务隔离级别，中文叫串行化，他是postgresql事务级别中最高一级，postgresql默认事务隔离级别是read committed,不过这个可以通过配置postgresql.conf中的default_transaction_isolation参数来设置默认事务隔离级别，采用SERIALIZABLE事务隔离级别可以防止脏读（dirty read），非重复读（nonrepeatable read），和幻像（phantom read），一个事务如果进入了set traansaction isolation level ser

2、ializable;就会独占这个事务需要的所有资源，其他任何修改同样资源的请求都会被推出，不过讲述postgresql事务隔离级别SERIALIZABLE的特性时是必需要结合postgresql的另一个实现机制MVCC(多版本控制 Multiversion Concurrency Control, MVCC)一起来说明。SQL标准定义了四种隔离级别。最严格的是可序列化，在标准中用了一整段来定义它，其中说到一组可序列化事务的任意并发执行被保证效果和以某种顺序一个一个执行这些事务一样。其他三种级别使用并发事务之间交互产生的现象来定义，每一个级别中都要求必须不出现一种现象。注意由于可序列化的定义，在

3、该级别上这些现象都不可能发生（这并不令人惊讶-如果事务的效果与每个时刻只运行一个的相同，你怎么可能看见由于交互产生的现象？）。 在各个级别上被禁止出现的现象是： 脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时数据就叫脏读非重复读（nonrepeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，如果每次返回不同的结果集，就叫非重复读。幻像（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，就叫幻像读。表 13-1. 标准SQL事务隔离级别隔离级别 脏读 不可重复读 幻读

4、读未提交 可能 可能 可能 读已提交 不可能 可能 可能 可重复读 不可能 不可能 可能 可序列化 不可能 不可能 不可能 PostgreSQL官方文档介绍：13.2.1. 读已提交隔离级别读已提交是PostgreSQL中的默认隔离级别。 当一个事务运行使用这个隔离级别时， 一个查询（没有FOR UPDATE/SHARE子句）只能看到查询开始之前已经被提交的数据， 而无法看到未提交的数据或在查询执行期间其它事务提交的数据。实际上，SELECT查询看到的是一个在查询开始运行的瞬间该数据库的一个快照。不过SELECT可以看见在它自身事务中之前执行的更新的效果，即使它们还没有被提交。还要注意的是，即

5、使在同一个事务里两个相邻的SELECT命令可能看到不同的数据， 因为其它事务可能会在第一个SELECT开始和第二个SELECT开始之间提交。 13.2.2. 可重复读隔离级别可重复读隔离级别只看到在事务开始之前被提交的数据；它从来看不到未提交的数据或者并行事务在本事务执行期间提交的修改（不过，查询能够看见在它的事务中之前执行的更新，即使它们还没有被提交）。这是比SQL标准对此隔离级别所要求的更强的保证，并且阻止表 13-1中描述的所有现象。如上面所提到的，这是标准特别允许的，标准只描述了每种隔离级别必须提供的最小保护。 这个级别与读已提交不同之处在于，一个可重复读事务中的查询可以看见在事务开始

6、时的一个快照，而不是事务中当前查询开始时的快照。因此，在一个单一事务中的后续SELECT命令看到的是相同的数据，即它们看不到其他事务在本事务启动后提交的修改。 使用这个级别的应用必须准备好由于序列化失败而重试事务。 UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命令在搜索目标行时的行为和SELECT一样： 它们将只找到在事务开始时已经被提交的行。 不过，在被找到时，这样的目标行可能已经被其它并发事务更新（或删除或锁住）。在这种情况下， 可重复读事务将等待第一个更新事务提交或者回滚（如果它还在进行中）。 如果第一个更新事务回滚，那么它的作用将被忽

7、略并且可重复读事务可以继续更新最初发现的行。 但是如果第一个更新事务提交（并且实际更新或删除该行，而不是只锁住它），则可重复读事务将回滚并带有如下消息 ERROR: could not serialize access due to concurrent update因为一个可重复读事务无法修改或者锁住被其他在可重复读事务开始之后的事务改变的行。 当一个应用接收到这个错误消息，它应该中断当前事务并且从开头重试整个事务。在第二次执行中，该事务将见到作为其初始数据库视图一部分的之前提交的改变，这样在使用行的新版本作为新事务更新的起点时就不会有逻辑冲突。 注意只有更新事务可能需要被重试；只读事务将永

8、远不会有序列化冲突。 可重复读模式提供了一种严格的保证，在其中每一个事务看到数据库的一个完全稳定的视图。不过，这个视图并不需要总是和同一级别上并发事务的某些序列化（一次一个）执行保持一致。例如，即使这个级别上的一个只读事务可能看到一个控制记录被更新，这显示一个批处理已经被完成但是不能看见作为该批处理的逻辑组成部分的一个细节记录，因为它读取空值记录的一个较早的版本。如果不小心地使用显式锁来阻塞冲突事务，尝试用运行在这个隔离级别的事务来强制业务规则不太可能正确地工作。 2, 实例演示：在数据库默认的读已提交隔离级别下：会发生死锁问题：如下图，两个用户session同时更新同一条记录时，第二个ses

9、sion需要等前一个更新提交或回退之后，才能继续完成，如果前一个用户session一直没有提交或回退，则会阻塞所有其他的session，直到发生超时（若有设定lock_timeout的话）。通常情况下生产系统的异常session会阻塞部分业务，需要手工kill进程的方式处理。会发生更新丢失问题：两个用户session同时更新同一条记录，第一个用户的更新，会被第二个用户覆盖，因为第二个用户无法得知当前记录已发生变化。如下图：注：更新丢失会影响的业务数据准确性，如果更新的是银行卡账户余额，则必须避免更新丢失问题。实例演示：在数据库的可重复读隔离级别下：可解决上例中的并行更新时更新丢失问题：用户A和

10、B的session同为可重复读级别，A、B用户先后（或同时）对同一条记录更新，后更新的session会等待前一个SQL提交或回退，当回退时，第二个session会继续执行，当提交时，第二个session会报错“could not serialize access due to concurrent update”，这种保护机制会避免更新丢失的情况。注：此时没有锁等待时间，第二个session在即时检测到记录被更新时立刻返回报错信息，即使A、B更新的是不同字段，只要是同一记录也会出错。3, XXX生产系统问题重现：以查明的案例为例，其他可能发生此错误的场景与此类似，具有代表性：Mobile Ap

11、p接口调用时，同时调用user_info & cart_detail接口：由于在user_info中,(由于方法current_user._get_default_image()的调用)会执行SQL：UPDATE res_partner SET write_uid=1,write_date=(now() at time zone UTC) WHERE id IN (738)在cart_detail中，会执行：self.poolres.partner.write(cr, SUPERUSER_ID, partner.id, last_website_so_id: sale_order_id)两者在

12、并行处理时，会针对同一条res_partner记录做更新：返回异常结果：Odoo Server日志错误信息：2016-11-10 17:06:18,447 73932 INFO demo_20161020 openerp.sql_db: bad query: UPDATE res_partner SET last_website_so_id=1772,write_uid=1,write_date=(now() at time zone UTC) WHERE id IN (738)2016-11-10 17:06:18,447 73932 ERROR demo_20161020 openerp.

13、addons.app_api.controllers.shopcart: could not serialize access due to concurrent update2016-11-10 17:06:18,447 73932 ERROR demo_20161020 openerp.addons.app_api.controllers.shopcart: 400014, 解决方案一：接口重复调用方式代码实现：根据“使用这个级别的应用必须准备好由于序列化失败而重试事务”的官方提示，新增重试事务功能。装饰器repeat_execute：参数：count = 执行次数说明：当被装饰的方法出现

14、串行化更新失败时，对其执行指定次数的重复调用，当其中某一次调用成功执行时，终止重复执行并返回结果。Class 40 Transaction Rollback40000transaction_rollback40002transaction_integrity_constraint_violation40001serialization_failure40003statement_completion_unknown40P01deadlock_detecteddef repeat_execute(count=1): def decorator(func): functools.wraps(fun

15、c) def wrapper(*args, *kwargs): v_first_call=func(*args,*kwargs) v_multi_call = None if v_first_call.get(pgcode) and v_first_call.get(pgcode)=40001: if count=1: return v_first_call else: cnt=1 while cnt count: _logger.warn(execute %s % str(cnt) cnt = cnt + 1 v_multi_call = func(*args,*kwargs) if not

16、 v_multi_call.get(pgcode): break return v_multi_call else: return v_first_call return wrapper return decorator装饰器使用时需修改的部分：由于接口已使用http.route装饰器，需做跳转。当捕捉到代码中的“could not serialize access due to concurrent update”异常，触发repeat_execute装饰器多次调用，进行3次(参数指定)调用，实际测试第2次执行即成功返回。# 修改购物车(当前使用)http.route(/app_api/we

17、bsite_sale/cart_detail, type=json, auth=user, website=True)def cart_call_kw(self, *args, *kwargs): result = eval(self.cart_detail)(*kwargs) return resultrepeat_execute(count=3)def cart_detail(self, *post): cr, uid, context, pool = request.cr, request.uid, request.context, request.registrytry: order

18、= ws.custom_sale_get_order1(force_create = 1)except Exception, e: _logger.error(e) _logger.error(e.pgcode) cr.rollback() return title: 购物车为空, msg: 购物车为空, pgcode: e.pgcode 新增重试事务功能后，并行情况下成功执行并返回结果：Odoo Server日志可看出两次执行记录，第一次执行出现“could not serialize access due to concurrent update”, 随即rollback并执行第二次成功。

19、5, 解决方案二：检测Advisory锁的方式代码实现：Postgresql官方文档介绍：PostgreSQL提供了一种方法创建由应用定义其含义的锁。这种锁被称为咨询锁，因为系统并不强迫其使用 而是由应用来保证其正确的使用。咨询锁可用于 MVCC 模型不适用的锁定策略。例如，咨询锁的一种常用用法是模拟所谓平面文件数据管理系统典型的悲观锁策略。虽然一个存储在表中的标志可以被用于相同目的，但咨询锁更快、可以避免表膨胀并且会由服务器在会话结束时自动清理。 有两种方法在PostgreSQL中获取一个咨询锁：在会话级别或在事务级别。一旦在会话级别获得了咨询锁，它将被保持直到被显式释放或会话结束。不同于标

20、准锁请求，会话级咨询锁请求不尊重事务语义：在一个后来被回滚的事务中得到的锁在回滚后仍然被保持，并且同样即使调用它的事务后来失败一个解锁也是有效的。一个锁在它所属的进程中可以被获取多次；对于每一个完成的锁请求必须有一个相应的解锁请求，直至锁被真正释放。在另一方面，事务级锁请求的行为更像普通锁请求：在事务结束时会自动释放它们，并且没有显式的解锁操作。这种行为通常比会话级别的行为更方便，因为它使用一个咨询锁的时间更短。对于同一咨询锁标识符的会话级别和事务级别的锁请求按照期望将彼此阻塞。如果一个会话已经持有了一个给定的咨询锁，由它发出的附加请求将总是成功，即使有其他会话在等待该锁；不管现有的锁和新请求

21、是处在会话级别还是事务级别，这种说法都是真的。 实现理论：由于Odoo的默认事务隔离级别为“重复读repeatable read”,在事务刚开始的时候的读取值，会在事务中间被引用，当中途记录被其他session修改时，会发生并发更新的错误。因此在这里可以引入Postgresql的咨询锁，有基于session的pg_advisory_lock，和基于事务的pg_advisory_xact_lock。事务级别的咨询锁可以主动请求，请求后其他session若要请求相同的锁则会被阻塞，当本事务结束后即会自动释放。据我的实验，并发执行的cart_detail和user_info两个接口，cart_det

22、ail会率先申请到咨询锁，user_info则会请求失败，进入预设的锁等待循环，直到cart_detail事务结束后，user_info成功申请到锁并继续执行成功。代码实现：新建获取事务级咨询锁的公共方法，p_id为欲加锁的表的主键：def getadvisorylock(request,p_id): sql = select pg_try_advisory_xact_lock(%s) as lockstatus params = (p_id,) request.cr.execute(sql, params) lock_status = request.cr.dictfetchone()loc

23、kstatus return lock_status分别在user_info接口和cart_detail接口增加咨询锁的请求和锁等待循环逻辑：当成功请求锁时，退出循环，反之重试直到成功。注意需要在查询记录的语句上增加此逻辑，因为事务开始后，此查询的结果会运用在接下来的事务处理上。partner = current_user.partner_idwhile True: if getadvisorylock(request, partner.id): _logger.warn(get lock success, break) partner = current_user.partner_id break time.sleep(0.5) _logger.warn(been locked, sleep 0.5s and retry)实验结果：两个并发接口均成功返回结果，其中由于sleep时间，user_info接口增加了执行时间。以