oracle 事务隔离级别

一 事务的4个基本特征 
    当事务处理系统创建事务时，将确保事务有某些特性。组件的开发者们假设事务的特性应该是一些不需要他们亲自管理的特性。这些特性称为ACID特性。 ACID就是：原子性(Atomicity )、一致性( Consistency )、隔离性或独立性( Isolation)和持久性(Durabilily)。 
1、原子性(Atomicity ) 
    原子性属性用于标识事务是否完全地完成，一个事务的任何更新要在系统上完全完成，如果由于某种原因出错，事务不能完成它的全部任务，系统将返回到事务开始前的状态。 
让我们再看一下银行转帐的例子。如果在转帐的过程中出现错误，整个事务将会回滚。只有当事务中的所有部分都成功执行了，才将事务写入磁盘并使变化永久化。为了提供回滚或者撤消未提交的变化的能力，许多数据源采用日志机制。例如，SQL Server使用一个预写事务日志，在将数据应用于(或提交到)实际数据页面前，先写在事务日志上。但是，其他一些数据源不是关系型数据库管理系统 (RDBMS)，它们管理未提交事务的方式完全不同。只要事务回滚时，数据源可以撤消所有未提交的改变，那么这种技术应该可用于管理事务。 
2、一致性(Consistency ) 
    事务在系统完整性中实施一致性，这通过保证系统的任何事务最后都处于有效状态来实现。如果事务成功地完成，那么系统中所有变化将正确地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。因为事务开始时系统处于一致状态，所以现在系统仍然处于一致状态。再让我们回头看一下银行转帐的例子，在帐户转换和资金转移前，帐户处于有效状态。如果事务成功地完成，并且提交事务，则帐户处于新的有效的状态。如果事务出错，终止后，帐户返回到原先的有效状态。 
记住，事务不负责实施数据完整性，而仅仅负责在事务提交或终止以后确保数据返回到一致状态。理解数据完整性规则并写代码实现完整性的重任通常落在开发者肩上，他们根据业务要求进行设计。当许多用户同时使用和修改同样的数据时，事务必须保持其数据的完整性和一致性。因此我们进一步研究A C I D特性中的下一个特性：隔离性。 
3、隔离性 (Isolation) 
    在隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。重要的是，在隔离状态执行事务，系统的状态有可能是不一致的，在结束事务前，应确保系统处于一致状态。但是在每个单独的事务中，系统的状态可能会发生变化。如果事务不是在隔离状态运行，它就可能从系统中访问数据，而系统可能处于不一致状态。通过提供事务隔离，可以阻止这类事件的发生。在银行的示例中，这意味着在这个系统内，其他过程和事务在我们的事务完成前看不到我们的事务引起的任何变化，这对于终止的情况非常重要。如果有另一个过程根据帐户余额进行相应处理，而它在我们的事务完成前就能看到它造成的变化，那么这个过程的决策可能 
建立在错误的数据之上，因为我们的事务可能终止。这就是说明了为什么事务产生的变化，直到事务完成，才对系统的其他部分可见。隔离性不仅仅保证多个事务不能同时修改相同数据，而且能够保证事务操作产生的变化直到变化被提交或终止时才能对另一个事务可见，并发的事务彼此之间毫无影响。这就意味着所有要求修改或读取的数据已经被锁定在事务中，直到事务完成才能释放。大多数数据库，例如SQL Server以及其他的RDBMS，通过使用锁定来实现隔离，事务中涉及的各个数据项或数据集使用锁定来防止并发访问。 
4、持久性(Durability) 
    持久性意味着一旦事务执行成功，在系统中产生的所有变化将是永久的。应该存在一些检查点防止在系统失败时丢失信息。甚至硬件本身失败，系统的状态仍能通过在日志中记录事务完成的任务进行重建。持久性的概念允许开发者认为不管系统以后发生了什么变化，完成的事务是系统永久的部分。在银行的例子中，资金的转移是永久的，一直保持在系统中。这听起来似乎简单，但这，依赖于将数据写入磁盘，特别需要指出的是，在事务完全完成并提交后才写入磁盘的。所有这些事务特性，不管其内部如何关联，仅仅是保证从事务开始到事务完成，不管事务成功与否，都能正确地管理事务涉及的数据，当事务处理系统创建事务时，将确保事务有某些特性。组件的开发者们假设事务的特性应该是一些不需要他们亲自管理的特性。 
二、为什么需要对事务并发控制 
    如果不对事务进行并发控制，我们看看数据库并发操作是会有那些异常情形 
1、丢失更新(Lostupdate) 
    两个事务都同时更新一行数据，但是第二个事务却中途失败退出，导致对数据的两个修改都失效了。 

两种丢失更新的区别

第一类丢失更新 

    A事务撤销时，把已经提交的B事务的更新数据覆盖了。这种错误可能造成很严重的问题，通过下面的账户取款转账就可以看出来：

时间	取款事务A	转账事务B
T1	开始事务
T2		开始事务
T3	查询账户余额为1000元
T4		查询账户余额为1000元
T5		汇入100元把余额改为1100元
T6		提交事务
T7	取出100元把余额改为900元
T8	撤销事务
T9	余额恢复为1000 元（丢失更新）

   A事务在撤销时，“不小心”将B事务已经转入账户的金额给抹去了。 

    第二类丢失更新 

A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失：   

时间	转账事务A	取款事务B
T1		开始事务
T2	开始事务
T3		查询账户余额为1000元
T4	查询账户余额为1000元
T5		取出100元把余额改为900元
T6		提交事务
T7	汇入100元
T8	提交事务
T9	把余额改为1100 元（丢失更新）

上面的例子里由于支票转账事务覆盖了取款事务对存款余额所做的更新，导致银行最后损失了100元，相反如果转账事务先提交，那么用户账户将损失100元。

2、脏读(DirtyReads) 
    一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险的，因为很可能所有的操作都被回滚。 

3、非重复读(Non-repeatableReads) （数据不一样）（强调值不一样）
   一个事务对同一行数据重复读取两次，但是却得到了不同的结果。同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生非重复读。

Expected (good) behavior if the transactions wereserialized: If a transaction only reads (and does not modify) the item, eachtime the item is read, the same value will be obtained [əbˈtein]

 4、二类丢失更新(Secondlost updates problem) 
    无法重复读取的特例。有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效。 

用一个操作过程来说明：

　　(1)会话Session1中的一个事务获取(查询)一行数据，并显示给一个用户User1.

　　(2)会话Session2中的另一个事务也获取这一行，但是将数据显示给另一个用户User2.

　　(3)User1 使用应用修改了这一行，让应用更新数据库并提交。会话Session1 的事务执行完毕。

　　(4)User2 也修改这一行，让应用更新数据库并提交。会话Session2 的事务执行完毕。

　　这个过程就叫做“丢失更新”，因为第(3)步做的操作会全部丢失(被第4步操作覆盖)，最终数据库只会保存第(4)步的更新结果。这个情况在有些系统中可能不会有影响，但在有些系统中可能就影响很大了，举个简单的例子：

　　财务系统加工资，若公司本次调薪决定给员工张三加1k人民币，财务部两名操作人员A和B，过程情况若是这样的：

　　1)A操作员在应用系统的页面上查询出张三的薪水信息，然后选择薪水记录进行修改，打开修改页面但A突然有事离开了，页面放在那没有做任何的提交。

　　2)这时候B操作员同样在应用中查询出张三的薪水信息，然后选择薪水记录进行修改，录入增加薪水额1000，然后提交了。

　　3)这时候A操作员回来了，在自己之前打开的薪水修改页面上也录入了增加薪水额1000，然后提交了。

　　其实上面例子操作员A和B只要一前一后做提交，悲剧就出来了。后台修改薪水的sql：update 工资表 setsalary = salary + 增加薪水额 where staff_id = ‘员工ID’。这个过程走下来后结果是：张三开心了这次涨了2k，操作员A和B都郁闷了。

　　二、解决思路：

　　基本两种思路，一种是悲观锁，另外一种是乐观锁; 简单的说就是一种假定这样的问题是高概率的，最好一开始就锁住，免得更新老是失败;另外一种假定这样的问题是小概率的，最后一步做更新的时候再锁住，免得锁住时间太长影响其他人做有关操作。

　　三、解决方案1(悲观锁)

　　a)传统的悲观锁法(不推荐)：

　　以上面的例子来说明，在弹出修改工资的页面初始化时(这种情况下一般会去从数据库查询出来)，在这个初始化查询中使用select ……for update， 通过添加for update nowait语句，将这条记录锁住，避免其他用户更新，从而保证后续的更新是在正确的状态下更新的。然后在保持这个链接的状态下，在做更新提交。当然这个有个前提就是要保持链接，就是要对链接要占用较长时间，这个在现在web系统高并发高频率下显然是不现实的。

　　b)现在的悲观锁法(推荐优先使用)：

　　在修改工资这个页面做提交时先查询下，当然这个查询必须也要加锁(select ……for update nowait)，有人会说，在这里做个查询确认记录是否有改变不就行了吗，是的，是要做个确认，只是你不加for update就不能保证你在查询到更新提交这段时间里这条记录没有被其他会话更新过，所以这种方式也需要在查询时锁定记录，保证在这条记录没有变化的基础上再做更新，若有变化则提示告知用户。

　　四、解决方案2(乐观锁)

　　a)旧值条件(前镜像)法：

　　就是在sql更新时使用旧的状态值做条件，SQL大致如下 Update table set col1 = newcol1value， col2 = newcol2value…。 where col1 =oldcol1value and col2 = oldcol2value…。，在上面的例子中我们就可以把当前工资作为条件进行更新，如果这条记录已经被其他会话更新过，则本次更新了0行，这里我们应用系统一般会做个提示告知用户重新查询更新。这个取哪些旧值作为条件更新视具体系统实际情况而定。(这种方式有可能发生阻塞，如果应用其他地方使用悲观锁法长时间锁定了这条记录，则本次会话就需要等待，所以使用这种方式时最好统一使用乐观锁法。)

　　b)使用版本列法(推荐优先使用)：

　　其实这种方式是一个特殊化的前镜像法，就是不需要使用多个旧值做条件，只需要在表上加一个版本列，这一列可以是NUMBER或 DATE/TIMESTAMP列，加这列的作用就是用来记录这条数据的版本(在表设计时一般我们都会给每个表增加一些NUMBER型和DATE型的冗余字段，以便扩展使用，这些冗余字段完全可以作为版本列用)，在应用程序中我们每次操作对版本列做维护即可。在更新时我们把上次版本作为条件进行更新。这个方法比较简单，也最常用，就是在数据库表格中加一列last_modified_date，就是最后更新的时间，每次更新的时候都将这列设成systimestamp，当前系统时间;

　　然后每次更新的时候，就改成这样 Update table set col = newvalue where id = ** andlast_modified_date = old last_modified_date 这样，就可以检验出数据库的值是否在上次查看和这次更新的时候发生了变化，如果发生了变化，那么last_modified_date就变化了，以后的更新就会返回更新了0行，系统就可以通知用户数据发生了变化，然后选择刷新数据或者其他流程。

　　至于这个last_modified_date的维护，可以选择让应用每次都维护这个值，或者是使用存储过程来包装更新的操作，或者是使用触发器来更新相关的值。几种方法各有利弊，比如应用维护需要保证每段相关代码都正确的维护了这个值;存储过程有一定的开销，通常很多开发对写存储过程可能也不熟练;触发器是简单的实现，但是也是有开销的。具体使用哪种方法需要根据实际情况具体取舍。

 

c)使用校验和法(不推荐)

根据其他有实际意义的列来计算出一个虚拟的列

d)使用ORA_ROWSCN法(不推荐)

利用10g的一个ora_rowscn特性，可以对每行做精确追踪，不过这个要求在create table的时候就指定相关参数，表格如果创建了以后就不能用altertable来修改了，因为这依赖于物理的实际存储。

　　五、结论：

　　综上所述，我们对丢失更新问题建议采取上面的悲观锁b方法或乐观锁b方法(蓝色字体已标注)，其实这两种方式的本质都一样，都是在更新提交时做一次查询确认在更新提交，我个人觉得都是乐观的做法，区别在于悲观锁b方法是通过select……forupdate方式，这个可能会导致其他会话的阻塞，而乐观锁b方法需要多一个版本列的维护。

　　个人建议：在用户并发数比较少且冲突比较严重的应用系统中选择悲观锁b方法，其他情况首先乐观锁版本列法。

5、幻像读(PhantomReads) [ˈfæntəm]（强调数量不一致）

A Phantom n.adj.幻影； 幽灵Read occurs if transaction T1 obtains a different result from each Selectfor the same criteria 
事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。 

–     Expected (good) behavior if the transactionswere serialized: The first and the second search within the same transactionwill give the same result

近期工作中遇到一个BUG报重复提交会生成两条同样的记录。因为这种数据的一个字段在一定范围内不允许重名（并不是全表数据不允许重名因此这个字段没有unique约束），所以每次保存记录时会先检查有没有重名，如果没有重名才会保存。但是在重复提交过程中若操作间隔足够短就会出现以下情况：
      第一个事务首先查询，结果没有重名，接着第二个事务也开始查询，结果也没有重名，然后第一个事务插入数据并提交，接着第二个事务也将同样一份数据插入，因为没有unique约束，因此也插入成功。假设第二个事务在提交之前再查询是否有重名就会发现已经存在重名了，这样第一次查询就是典型的“幻象读”。幻象读的标准描述如下：

      杜绝幻象读必须使用最高的隔离级别，即事务串行化。 

NOTE：如无特别设定，程序总是使用数据库的默认隔离级别。mysql的默认级别是第三级：REPEATABLE-READ。

数据库锁机制 
    数据并发会引发很多问题，在一些场合下有些问题是允许的，但在另外一些场合下可能却是致命的。数据库通过锁的机制解决并发访问的问题，虽然不同的数据库在实现细节上存在差别，但原理基本上是一样的。 

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。
    按锁定的对象的不同，一般可以分为表锁定和行锁定，前者对整个表进行锁定，而后者对表中特定行进行锁定。从并发事务锁定的关系上看，可以分为共享锁定和独占锁定。共享锁定会防止独占锁定，但允许其它的共享锁定。而独占锁定既防止其它的独占锁定，也防止其它的共享锁定。为了更改数据，数据库必须在进行更改的行上施加行独占锁定，INSERT、UPDATE、DELETE和SELECT FOR UPDATE语句都会隐式采用必要的行锁定。下面我们介绍一下ORACLE数据库常用的5种锁定： 
行共享锁定：一般通过SELECT FOR UPDATE语句隐式获得行共享锁定，在Oracle中你也可以通过LOCK TABLE IN ROW SHARE MODE语句显式获得行共享锁定。行共享锁定并不防止对数据行进行更改的操作，但是可以防止其它会话获取独占性数据表锁定。允许进行多个并发的行共享和行独占性锁定，还允许进行数据表的共享或者采用共享行独占锁定； 
行独占锁定：通过一条INSERT、UPDATE或DELETE语句隐式获取，或者通过一条LOCK TABLE IN ROW EXCLUSIVE MODE语句显式获取。这个锁定可以防止其它会话获取一个共享锁定、共享行独占锁定或独占锁定； 
表共享锁定：通过LOCK TABLE IN SHARE MODE语句显式获得。这种锁定可以防止其它会话获取行独占锁定（INSERT、UPDATE或DELETE），或者防止其它表共享行独占锁定或表独占锁定，它允许在表中拥有多个行共享和表共享锁定。该锁定可以让会话具有对表事务级一致性访问，因为其它会话在你提交或者回溯该事务并释放对该表的锁定之前不能更改这个被锁定的表； 
表共享行独占：通过LOCK TABLE IN SHARE ROWEXCLUSIVE MODE语句显式获得。这种锁定可以防止其它会话获取一个表共享、行独占或者表独占锁定，它允许其它行共享锁定。这种锁定类似于表共享锁定，只是一次只能对一个表放置一个表共享行独占锁定。如果A会话拥有该锁定，则B会话可以执行SELECT FORUPDATE操作，但如果B会话试图更新选择的行，则需要等待； 
表独占：通过LOCK TABLE IN EXCLUSIVE MODE显式获得。这个锁定防止其它会话对该表的任何其它锁定。 

Oracle数据库的锁类型

根据保护的对象不同，Oracle数据库锁可以分为以下几大类：

DML锁（datalocks，数据锁），用于保护数据的完整性；

DDL锁（dictionarylocks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；内部锁和闩（internallocks and latches），保护数据库的内部结构。

DML锁的目的在于保证并发情况下的数据完整性，本文主要讨论DML锁。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。如表1所示。

在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。

TX锁等待的分析

在介绍了有关地Oracle数据库锁的种类后，下面讨论如何有效地监控和解决锁等待现象，及在产生死锁时如何定位死锁的原因。

监控锁的相关视图数据字典是Oracle数据库的重要组成部分，用户可以通过查询数据字典视图来获得数据库的信息。和锁相关的数据字典视图如表2所示。

TX锁等待的监控和解决在日常工作中，如果发现在执行某条SQL时数据库长时间没有响应，很可能是产生了TX锁等待的现象。为解决这个问题，首先应该找出持锁的事务，然后再进行相关的处理，如提交事务或强行中断事务。

死锁的监控和解决在数据库中，当两个或多个会话请求同一个资源时会产生死锁的现象。死锁的常见类型是行级锁死锁和页级锁死锁，Oracle数据库中一般使用行级锁。下面主要讨论行级锁的死锁现象。

当Oracle检测到死锁产生时，中断并回滚死锁相关语句的执行，报ORA-00060的错误并记录在数据库的日志文件alertSID.log中。同时在user_dump_dest下产生了一个跟踪文件，详细描述死锁的相关信息。

在日常工作中，如果发现在日志文件中记录了ora-00060的错误信息，则表明产生了死锁。这时需要找到对应的跟踪文件，根据跟踪文件的信息定位产生的原因。

如果查询结果表明，死锁是由于bitmap索引引起的，将IND_T_PRODUCT_HIS_STATE索引改为normal索引后，即可解决死锁的问题。

表1 Oracle的TM锁类型
锁模式	锁描述	解释	SQL操作
0	none
1	NULL	空	Select
2	SS(Row-S)	行级共享锁，其他对象只能查询这些数据行	Select for update、Lock for update、Lock row share
3	SX(Row-X)	行级排它锁，在提交前不允许做DML操作	Insert、Update、Delete、Lock row share
4	S(Share)	共享锁	Create index、Lock share
5	SSX(S/Row-X)	共享行级排它锁	Lock share row exclusive
6	X(Exclusive)	排它锁	Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

 

表2 数据字典视图说明
视图名	描述	主要字段说明
v$session	查询会话的信息和锁的信息。	sid,serial#：表示会话信息。 program：表示会话的应用程序信息。 row_wait_obj#：表示等待的对象。 和DBA_objects中的object_id相对应。
v$session_wait	查询等待的会话信息。	sid：表示持有锁的会话信息。 Seconds_in_wait：表示等待持续的时间信息 Event：表示会话等待的事件。
v$lock	列出系统中的所有的锁。	Sid：表示持有锁的会话信息。 Type：表示锁的类型。值包括TM和TX等。 ID1：表示锁的对象标识。 lmode,request：表示会话等待的锁模式的信 息。用数字0－6表示，和表1相对应。
DBA_locks	对v$lock的格式化视图。	Session_id：和v$lock中的Sid对应。 Lock_type：和v$lock中的type对应。 Lock_ID1： 和v$lock中的ID1对应。 Mode_held,mode_requested：和v$lock中 的lmode,request相对应。
v$locked_object	只包含DML的锁信息，包括回滚段和会话信息。	Xidusn,xidslot,xidsqn：表示回滚段信息。和 v$transaction相关联。 Object_id：表示被锁对象标识。 Session_id：表示持有锁的会话信息。 Locked_mode：表示会话等待的锁模式的信 息，和v$lock中的lmode一致。

oracle死锁的定位方法

通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。

1)用dba用户执行以下语句

以下是代码片段：
select username,lockwait,status,machine,program from v$session where sidin 
(select session_id from v$locked_object)

如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：

Username：死锁语句所用的数据库用户;

Lockwait：死锁的状态，如果有内容表示被死锁。

Status：状态，active表示被死锁

Machine：死锁语句所在的机器。

Program：产生死锁的语句主要来自哪个应用程序。

2)用dba用户执行以下语句，可以查看到被死锁的语句。

以下是代码片段：
select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in 
(select session_id from v$locked_object)) 

三、数据库的隔离级别 
   为了兼顾并发效率和异常控制，在标准SQL规范中，定义了4个事务隔离级别，（ORACLE和SQLSERER对标准隔离级别有不同的实现） 
1、未提交读(ReadUncommitted) 
    直译就是"读未提交",意思就是即使一个更新语句没有提交,但是别 
的事务可以读到这个改变.这是很不安全的。允许任务读取数据库中未提交的数据更改，也称为脏读。 
2、提交读(ReadCommitted) 
   直译就是"读提交",可防止脏读，意思就是语句提交以后即执行了COMMIT以后 
别的事务就能读到这个改变. 只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 
3、可重复读(RepeatableRead)： 
   直译就是"可以重复读",这是说在同一个事务里面先后执行同一个查询语句的时候,得到的结果是一样的.在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读 
4、串行读(Serializable) 
   直译就是"序列化",意思是说这个事务执行的时候不允许别的事务并发执行. 完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

四，隔离级别对并发的控制 
下表是各隔离级别对各种异常的控制能力。 

LU丢失更新 两个事务都同时更新一行数据，但是第二个事务却中途失败rollback退出，导致对数据的两个修改都失效了。	DR脏读 一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交可能rollback	NRR非重复读 同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，	SLU二类丢失更新 有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效。	PR幻像读 事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据在两次查询过程中有另外一个事务插入数据造成的
未提交读 RU 允许任务读取数据库中未提交的数据更改	Y	Y	Y	Y	Y
提交读 RC	N	N	Y	Y	Y
可重复读 RR	N	N	N	N	Y
串行读 S	N	N	N	N	N

锁（并发控制的手段）

　　独占锁（排他锁）只允许一个事务访问数据

　　共享锁允许其他事务继续使用锁定的资源

　　更新锁

　　锁就是保护指定的资源，不被其他事务操作，锁定的资源包括行、页、簇、表和数据库。为了最小化锁的成本，SQL Server自动地以与任务相应等级的锁来锁定资源对象。锁定比较小的对象，例如锁定行，虽然可以提高并发性，但是却有较高的开支，因为如果锁定许多行，那么需要占有更多的锁。锁定比较大的对象，例如锁定表，会大大降低并发性，因为锁定整个表就限制了其他事务访问该表的其他部分，但是成本开支比较低，因为只需维护比较少的锁。

　　设置事务级别：SET TRANSACTIONISOLATION LEVEL

　　开始事务：begin tran

　　提交事务：COMMIT

　　回滚事务：ROLLBACK

　　创建事务保存点：SAVE TRANSACTION savepoint_name

　　回滚到事务点:ROLLBACK TRANSACTION savepoint_name

 

 

顺便举一小例。

MS_SQL: 
--事务一 
set transaction isolation level serializable 
begin tran 
insert into test values('xxx') 
--事务二 
set transaction isolation level read committed 
begin tran 
select * from test 
--事务三 
set transaction isolation level read uncommitted 
begin tran 
select * from test 
在查询分析器中执行事务一后，分别执行事务二，和三。结果是事务二会等待，而事务三则会执行

1.假设有一个事务A

-----事务A------- 
begintran 
update [TestDB].[dbo].tb_score set score=score+1 where id=3  waitfor delay '00:00:10' 

  --延迟10秒读取 
SELECT *  FROM [TestDB].[dbo].tb_score 

commit TRANSACTION

它会运行10s左右，主要是更新表tb_score。

在这10s期间，有语句对该表做查询。SELECT *  FROM [TestDB].[dbo].tb_score

该语句会被阻塞，因为update会默认加上共享锁，直到10s后，事务A提交后才能查出结果。

以上语句就等介于下面的语句，因为事务隔离级别READ COMMITTED是SQLServer的默认设置。

SET TRANSACTION ISOLATION LEVEL READ committed 
begin tran 
SELECT *  FROM [TestDB].[dbo].tb_score 
commit TRANSACTION

 

此处，值得注意的事情是，在事务A中当update语句更新的时候，如果要更新的目标值=现在的值，比如，在id=3的记录，score已经等于100了的情况下，语句如果是update [TestDB].[dbo].tb_score set score=100 where id=3。那么执行SELECT *  FROM [TestDB].[dbo].tb_score将不被阻塞。原因猜测是sql不对这种实际不做更改的Update语句加锁

 

2.还是以上事务A

把查询语句改为如下

语句B：

SET TRANSACTION ISOLATION LEVEL READuncommitted 
begin tran 
SELECT *  FROM [TestDB].[dbo].tb_score 
commit TRANSACTION

此时

SELECT *  FROM[TestDB].[dbo].tb_score语句被允许“脏读”

该查询未等事务A提交，就已经读出，并且读书的值为A以修改但未提交的值。

例如id=3的score=100，运行A事务的时候，score=101了，此时运行语句B，则立刻返回出id=3的score=101，而不管A是否真的已经提交或者rollback。

还有一点要注意，当你设置了READ uncommitted，那么接下来的sql语句都会是 READ uncommitted。msdn上：一次只能设置一个隔离级别选项，而且设置的选项将一直对那个连接始终有效，直到显式更改该选项为止。事务中执行的所有读取操作都会在指定的隔离级别的规则下运行，除非语句的 FROM 子句中的表提示为表指定了其他锁定行为或版本控制行为。所以在查询分析器中做实验的时候，要注意，一旦设置了READ uncommitted，接下来在当前窗口中的语句都是如此。

3

-----事务A------- 
begin tran 
select *from  [TestDB].[dbo].tb_score with(tablockx) where id=3 
   waitfor delay '00:00:10'   --延迟10秒读取 
commit TRANSACTION

此事务对表加了表级别的排它锁。因此在此期间，

SELECT *  FROM[TestDB].[dbo].tb_score会被阻塞。

但是对于允许脏读的READ uncommitted，则不会阻塞

SET TRANSACTION ISOLATION LEVEL READuncommitted 
begin tran 
SELECT *  FROM [TestDB].[dbo].tb_score where id=1 
commit TRANSACTION

 

此处有些疑问：

两个事务 
----事务A------- 
begin tran 
select *from  [TestDB].[dbo].tb_score with(updlock,rowlock) where id=3 
   waitfor delay '00:00:10'   --延迟10秒读取 
update  [TestDB].[dbo].tb_score set score=score+1  where id=3 
commit TRANSACTION

----事务B------- 
SET TRANSACTION ISOLATION LEVEL READ committed 
begin tran 
select *from  [TestDB].[dbo].tb_score with(updlock,rowlock) where id=3
commit TRANSACTION 
 

我的理解是，事务A运行的时候，对id=3的加了行锁，还有更新锁，更新锁的话一定要等到事务运行完才能再被获取。 
因此事务A运行的时候，事务B也马上运行，但是事务B也要对改行获取更新锁，因此被阻塞在此，所以最后运行的结果是A运行完了B在运行。以上一切正常

但是把事务B改成 
-----事务B------- 
SET TRANSACTION ISOLATION LEVEL READ committed 
begin tran 
select *from  [TestDB].[dbo].tb_score with(updlock,rowlock) where  id=1 
commit TRANSACTION 

事务B对id=1的行加锁。 
那么我的理解是，既然事务A加锁的对象是id=3的行，那么和事务B根本不会有什么冲突。但是事实上，运行事务A后马上运行B，发现事务B也会被阻塞。并且事务A还会发出异常错误：说是检测到死锁。 
Transaction (Process ID 52) was deadlocked on lock resources with anotherprocess and has been chosen as the deadlock victim. Rerun the transaction.

 

ORACLE： 
--事务一 
set transaction isolation level serializable; 
insert into test values('xxx'); 
select * from test; 
--事务二 
set transaction isolation level read committed--ORACLE默认级别 
select * from test 
执行事务一后，执行事务二。结果是事务二只读出原有的数据，无视事务一的插入操作。

 

五、并发一致性问题的解决办法 
1 封锁（Locking） 
    封锁是实现并发控制的一个非常重要的技术。所谓封锁就是事务T在对某个数据对象例如表、记录等操作之前，先向系统发出请求，对其加锁。加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其它的事务不能更新此数据对象。基本的封锁类型有两种：排它锁（Exclusive locks 简记为X锁）和共享锁（Share locks 简记为S锁）。 
    排它锁又称为写锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了其它事务在T释放A上的锁之前不能再读取和修改A。 
    共享锁又称为读锁。若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其它事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。 
2 封锁协议 
    在运用X锁和S锁这两种基本封锁，对数据对象加锁时，还需要约定一些规则，例如应何时申请X锁或S锁、持锁时间、何时释放等。我们称这些规则为封锁协议（Locking Protocol）。对封锁方式规定不同的规则，就形成了各种不同的封锁协议。下面介绍三级封锁协议。三级封锁协议分别在不同程度上解决了丢失的修改、不可重复读和读"脏"数据等不一致性问题，为并发操作的正确调度提供一定的保证。下面只给出三级封锁协议的定义，不再做过多探讨。 
    1 级封锁协议是：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。 1级封锁协议可防止丢失修改，并保证事务T是可恢复的。在1级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，所以它不能保证可重复读和不读"脏"数据。 
    2级封锁协议是：1级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。2级封锁协议除防止了丢失修改，还可进一步防止读"脏"数据。 
    3级封锁协议是：1级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。3级封锁协议除防止了丢失修改和不读'脏'数据外，还进一步防止了不可重复读。 
六、一般处理并发问题时的步骤： 
1、开启事务。 
2、申请写权限，也就是给对象(表或记录)加锁。 
3、假如失败，则结束事务，过一会重试。 
4、假如成功，也就是给对象加锁成功，防止其他用户再用同样的方式打开。 
5、进行编辑操作。 
6、写入所进行的编辑结果。 
7、假如写入成功，则提交事务,完成操作。 
8、假如写入失败，则回滚事务，取消提交。 
9、(7.8)两步操作已释放了锁定的对象，恢复到操作前的状态。

 

select for update nowait 与 select for update 区别

nowait的含义很多人都会误解为“不用等待，立即执行”。但实际上该关键字的含义是“不用等待，立即返回”
如果当前请求的资源被其他会话锁定时，会发生阻塞，nowait可以避免这一阻塞，因为
If another user is in the process of modifying that row, we will get an ORA‐00054
Resource Busy error. We are blocked and must wait for the other user to finishwith
it.
可以实验下，我用pl/sql developer锁定表game
SQL> select * from game where game_id =1;
返回一条记录
 

SQL> select * fromgame where game_id=1 for update nowait;
select * from game where game_id=1 for update nowait
             *
ERROR位于第1行:
ORA-00054:资源正忙，要求指定NOWAIT
使用NOWAIT关键字，会报ORA‐00054的错误
 

如何来查看是什么资源造成这样的情况呢？并且怎么解决呢？
查看锁定的对象，用户和会话
SQL> select lo.oracle_username,do.object_name,s.logon_time,lo.process,s.sidas s
ession_id
 2 from v$locked_object lo,v$session s,dba_objects do
 3 where lo.session_id = s.sid and do.object_id = lo.OBJECT_ID
 4 /
ORACLE_USERNAME
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
 

LOGON_TIME        PROCESS     SESSION_ID
------------------- ------------ ----------
NBA---用户名称
GAME---操作的对象
2009-08-04 10:55:15---登录的时间     840:5176   10
 

使用as sysdba
根据sid查看具体的sql语句
selectsql_textfromv$session a,v$sqltext_with_newlines b
whereDECODE(a.sql_hash_value,0, prev_hash_value, sql_hash_value)=b.hash_value
 anda.sid=10;
begin :id := sys.dbms_transaction.local_transaction_id; end;
 

kill session
SQL> select sid,serial# from v$session where sid =10;
 

     SID   SERIAL#
---------- ----------
      10        23
SQL> alter system kill session '10,23';
 

系统已更改。
 

select * from gamewhere game_id=1 for update nowait;
有数据返回了

当两个用户同时更新同一条记录是， 使用select for update，后执行者，会被阻塞，而使用select for update  nowait 则会抛出：ORA-00054 resource busy and acquire withNOWAIT specified 异常，告之用户这一行已经锁定。

Oracle的update语句优化研究

一、         update语句的语法与原理

1.     语法

单表：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

如：update t_join_situation set join_state='1'whereyear='2011'

更新年度为“2011”的数据的join_state字段为“1”。如果更新的字段加了索引，更新时会重建索引，更新效率会慢。

   多表关联，并把一个表的字段值更新到另一个表中的字段去：

update 表a set a.字段1 = (select b.字段1 from 表b where a.字段2=b.字段2) where exists(select 1 from 表b where a.字段2=b.字段2)  

oracle的更新语句不通MSSQL那么简单易写，就算写出来了，但执行时可能会报

这是由于set哪里的子查询查出了多行数据值，oracle规定一对一更新数据，所以提示出错。要解决这样必须保证查出来的值一一对应。

2.     原理

Update语句的原理是先根据where条件查到数据后，如果set中有子查询，则执行子查询把值查出来赋给更新的字段，执行更新。

如：update 表a set a.字段1 = (select b.字段1 from 表b where a.字段2=b.字段2) where exists(select 1 from 表b where a.字段2=b.字段2)。查表a的所有数据，循环每条数据，验证该条数据是否符合exists(select 1 from 表b where a.字段2=b.字段2)条件，如果是则执行(select b.字段1 from 表b where a.字段2=b.字段2)查询，查到对应的值更新a.字段1中。关联表更新时一定要有exists(select 1 from 表b where a.字段2=b.字段2)这样的条件，否则将表a的其他数据的字段1更新为null值。

二、         提高oracle更新效率的各种解决方案

1.     标准update语法

当你需要更新的表是单个或者被更新的字段不需要关联其他表带过来，则最后选择标准的update语句，速度最快，稳定性最好，并返回影响条数。如果where条件中的字段加上索引，那么更新效率就更高。但对需要关联表更新字段时，update的效率就非常差。

2.     inline view更新法

inline view更新法就是更新一个临时建立的视图。如：update (select a.join_state asjoin_state_a,b.join_state as join_state_b

from t_join_situationa, t_people_info b where a.people_number=b.people_number

and a.year='2011'and a.city_number='M00000'and a.town_number='M51000') setjoin_state_a=join_state_b

括号里通过关联两表建立一个视图，set中设置好更新的字段。这个解决方法比写法较直观且执行速度快。但表B的主键一定要在where条件中，并且是以“=”来关联被更新表，否则报一下错误：

 

3.merge更新法

merge是oracle特有的语句，语法如下：

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
        col2     = col2_val 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values); 

它的原理是在alias2中Select出来的数据，每一条都跟alias1进行 ON (join condition)的比较，如果匹配，就进行更新的操作(Update),如果不匹配，就进行插入操作(Insert)。执行merge不会返回影响的行数。Merge语句的写法比较繁琐，并且最多只能两个表关联，复杂的语句用merge更新法将力不从心且效率差。

4.快速游标更新法

语法如：

begin

for cr in (查询语句) loop –-循环

  --更新语句（根据查询出来的结果集合）

endloop; --结束循环

end;

oracle支持快速游标，不需要定义直接把游标写到for循环中，这样就方便了我们批量更新数据。再加上oracle的rowid物理字段（oracle默认给每个表都有rowid这个字段，并且是唯一索引），可以快速定位到要更新的记录上。

例子如下：

begin

for cr in (select a.rowid,b.join_state from t_join_situationa,t_people_info b

where a.people_number=b.people_number

and a.year='2011'and a.city_number='M00000'and a.town_number='M51000') loop

update t_join_situation set join_state=cr.join_state where

rowid =cr.rowid;

endloop;

end;

使用快速游标的好处很多，可以支持复杂的查询语句，更新准确，无论数据多大更新效率仍然高，但执行后不返回影响行数。

三、结论

方案	建议
标准update语法	单表更新或较简单的语句采用使用此方案更优。
inline view更新法	两表关联且被更新表通过关联表主键关联的，采用此方案更优。
merge更新法	两表关联且被更新表不是通过关联表主键关联的，采用此方案更优。
快速游标更新法	多表关联且逻辑复杂的，采用此方案更优。

 

实时测试的速度：

--48466条数据

--1.297

update (select a.join_state as join_state_a,b.join_state as join_state_b

from t_join_situation a, t_people_info b where a.people_number=b.people_number

and a.year='2011'and a.city_number='M00000'and a.town_number='M51000'

) set join_state_a=join_state_b

 

--7.156

update t_join_situation a set a.join_state=(select b.join_state from t_people_infob

where a.people_number=b.people_number

and a.year='2011'and a.city_number='M00000'and a.town_number='M51000')

whereexists (select1from t_people_info b

where a.people_number=b.people_number

and a.year='2011'and a.city_number='M00000'and a.town_number='M51000')

 

--3.281

begin

for cr in (select a.rowid,b.join_state from t_join_situationa,t_people_info b

where a.people_number=b.people_number

and a.year='2011'and a.city_number='M00000'and a.town_number='M51000') loop

update t_join_situation set join_state=cr.join_state where

rowid = cr.rowid;

endloop;

end;

 

--1.641

mergeinto t_join_situation a

using t_people_info b

on (a.people_number=b.people_number

and a.year='2011'and a.city_number='M00000'and a.town_number='M51000')

whenmatchedthenupdateset a.join_state=b.join_state

悲观锁与乐观锁

悲观锁【PessimisticLocking】

悲观锁对数据库系统的自动管理不感冒，需要程序员直接管理数据或对象上的加锁处理，并负责获取、共享和放弃正在使用的数据上的任何锁。

顾名思义就是采用一种悲观的态度来对待事务并发问题，我们认为系统中的并发更新会非常频繁，并且事务失败 了以后重来的开销很大，这样以来，我们就需要采用真正意义上的锁来进行实现。悲观锁的基本思想就是每次一 个事务读取某一条记录后，就会把这条记录锁住，这样其它的事务要想更新，必须等以前的事务提交或者回滚解 除锁。

假如我们数据库事务的隔离级别设置为读取已提交或者更低，那么通过悲观锁，我们控制了不可重复读的问题， 但是不能避免幻影读的问题，因为要想避免我们就需要设置数据库隔离级别为Serializable,而一般情况下我们都 会采取读取已提交或者更低隔离级别，并配合乐观或者悲观锁来实现并发控制，所以幻影读问题是不能避免的，如 果想避免幻影读问题，那么你只能依靠数据库的serializable隔离级别（幸运的是幻影读问题一般情况下不严重）

悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否 则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

实现方式

JDBC方式：在JDBC中使用悲观锁，需要使用selectfor update语句，假如我们系统中有一个Account的类，我们可 以采用如下的方式来进行：Select * from Account where...(where condition).. for update.

当使用了for update语句后，每次在读取或者加载一条记录的时候，都会锁住被加载的记录，那么当其他事务如果要 更新或者是加载此条记录就会因为不能获得锁而阻塞，这样就避免了不可重复读以及脏读的问题，但是其他事务还是 可以插入和删除记录，这样也许同一个事务中的两次读取会得到不同的结果集，但是这不是悲观锁锁造成的问题，这 是我们数据库隔离级别所造成的问题。

最后还需要注意的一点就是每个冲突的事务中，我们必须使用select for update 语句来进行数据库的访问，如果一 些事务没有使用select forupdate语句，那么就会很容易造成错误，这也是采用JDBC进行悲观控制的缺点。

 

乐观锁【OptimisticLocking】

乐观锁假定在处理数据时，不需要在应用程序的代码中做任何事情就可以直接在记录上加锁、即完全依靠数据库来管理锁的工作。一般情况下，当执行事务处理时SQL Server会自动对事务处理范围内更新到的表做锁定

乐观锁是在同一个数据库事务中我们常采取的策略，因为它能使得我们的系统保持高的性能的情况下，提高很好的并发 访问控制。乐观锁，顾名思义就是保持一种乐观的态度，我们认为系统中的事务并发更新不会很频繁，即使冲突了也没事， 大不了重新再来一次。它的基本思想就是每次提交一个事务更新时，我们想看看要修改的东西从上次读取以后有没有被其 它事务修改过，如果修改过，那么更新就会失败。（因此能够解决第二类丢失修改问题）

因为乐观锁其实并不会锁定任何记录，所以如果我们数据库的事务隔离级别设置为读取已提交或者更低的隔离界别，那么 是不能避免不可重复读问题的（因为此时读事务不会阻塞其它事务），所以采用乐观锁的时候，系统应该要容许不可重复 读问题的出现。

需要注意的是，乐观锁机制往往基于系统中的数据存储逻辑，因此也具备一定的局限性，由于乐观锁机制是在我们的系统中 实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。在系统设计阶段，我 们应该充分考虑到这些情况出现的可能性，并进行相应调整（如将乐观锁策略在数据库存储过程中实现，对外只开放基于此 存储过程的数据更新途径，而不是将数据库表直接对外公开）。

实现方式：

大多是基于数据版本 （ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解 决方案中，一般是通过为数据库表增加一个“version” 字段来实现。 

读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的 当前版本信息进行比对，如果提交的数据 版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

假如系统中有一个Account的实体类，我们在Account中多加一个version字段，那么我们JDBC Sql语句将如下写：

Selecta.version....from Account as a where (where condition..)

Update Account setversion = version+1.....(another field) where version =?...(anothercontidition)