事务、并发、隔离级别、锁3

数据库

并发、事务、隔离级别、锁新事业部：程学文目录事务与并发隔离级别和锁行版本控制（2005）死锁与解锁事务与并发隔离级别和锁行版本控制（2005）死锁与解锁并发与事务>数据完整性数据完整性：数据完整性（DataIntegrity）是指数据的精确性、是指数据的精确性和可靠性。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性分为四类：a.实体完整性（EntityIntegrity）b.域完整性（DomainIntegrity）c.参照完整性（ReferentialIntegrity）d.用户定义的完整性（User-definedIntegrity）并发与事务>数据完整性>实体完整性实体完整性：概念：实体完整性指表中行的完整性。要求表中的所有行都有唯一的标识符，称为主关键字。主关键字是否可以修改，或整个列是否可以被删除，取决于主关键字与其他表之间要求的完整性。实体完整性要求每一个表中的主键字段都不能为空或者重复的值。并发与事务>数据完整性>域完整性域完整性：概念：域完整性是针对某一具体关系数据库的约束条件。它反映某一具体应用所涉及的数据必须满足的语义要求。域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等。域完整性限制了某些属性中出现的值，把属性限制在一个有限的集合中。例如，如果属性类型是整数，那么它就不能是101.5或任何非整数。并发与事务>数据完整性>参照完整性参照完整性：概念：当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确。简单的说就是表间主键外键的关系，是相关联的两个表之间的约束。参照完整性属于表间规则。对于永久关系的相关表，在更新、插入或删除记录时，如果只改其一不改其二，就会影响数据的完整性：例如修改父表中关键字值后，子表关键字值未做相应改变；删除父表的某记录后，子表的相应记录未删除，致使这些记录成为孤立记录；对于子表插入的记录，父表中没有相应关键字值的记录；等等。对于这些设计表间数据的完整性，统称为参照完整性。并发与事务>事务事务：数据库提供了多种保证数据完整性的方式，其中一种基本方式便是事务EX：银行转账:甲向乙账户转帐100元1.检查甲账户余额是否超过100元2.如果大于100元，则甲账户扣除100元3.乙账户增加100元概念：数据库事务是指作为单个逻辑工作单元执行的一系列操作,事务内的语句被作为一个整体执行，确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。并发与事务>事务>事务的特点事务的特点：一个逻辑工作单元要成为事务，必须满足所谓的ACID(原子性、一致性、隔离性和持久性)属性：a.原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统操作子集的可能性。b.一致性：事务的一致性指的是在一个事务执行之前和执行。之后数据库都必须处于一致性状态。这种特性称为事务的一致性假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。并发与事务>事务>事务的特点例如，当数据库处于一致性状态S1时，对数据库执行一个事务，在事务执行期间假定数据库的状态是不一致的，当事务执行结束时，数据库处在一致性状态S2。c.隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。并发与事务>事务>事务的特点DBMS可以在并发执行的事务间提供不同级别的分离。分离的级别和并发事务的吞吐量之间存在反比关系。较多事务的可分离性可能会带来较高的冲突和较多的事务流产。流产的事务要消耗资源，这些资源必须要重新被访问。因此，确保高分离级别的DBMS需要更多的开销。d.持久性：持久性意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失。即一旦一个事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，耐得住任何系统故障。持久性通过数据库备份和恢复来保证。并发与事务>并发并发：通常为了获得更好的运行性能，各种数据库都允许多个事务同时运行，这就是事务并发。当并发的事务访问或修改数据库中相同的数据时，通常需要采取必要的隔离机制，反之会出现各种并发问题。这些并发问题可归纳为以下四种：a.丢失或覆盖更新。b.未确认的相关性。(脏读）c.不一致的分析。（不可重复读）d.幻像读并发与事务>并发>丢失更新丢失更新：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。例如:两个编辑人员制作了同一文档的电子复本。每个编辑人员独立地更改其复本，然后保存更改后的复本，这样就覆盖了原始文档。最后保存其更改复本的编辑人员覆盖了第一个编辑人员所做的更改。如果在第一个编辑人员完成之后第二个编辑人员才能进行更改，则可以避免该问题。并发与事务>并发>未确认的相关性未确认的相关性(脏读):当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。例如：一个编辑人员正在更改电子文档。在更改过程中，另一个编辑人员复制了该文档（该复本包含到目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。如果在第一个编辑人员确定最终更改前任何人都不能读取更改的文档，则可以避免该问题。并发与事务>并发>不一致的分析不一致的分析（非重复读）:当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。例如：一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。并发与事务>并发>幻想读幻像读：当对某行执行插入或删除操作，而该行属于某个事务正读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。例如：一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。并发与事务隔离级别和锁行版本控制（2005）死锁与解锁隔离级别与锁>隔离级别1.隔离级别：控制隔离数据以供一个进程使用并防止其它进程干扰的程度的事务属性。为在并发性能与并发问题间寻找一个最佳的平衡，隔离属性分为不同级别，数据库通过不同的隔离级别控制并发引起的问题：

类别

隔离级别1）未提交读READUNCOMMITTED低2）已提交读READCOMMITTED3）可重复读REPEATABLEREAD4）可序列化SERIALIZABLE高隔离级别与锁>隔离级别不同隔离级别对应的并发问题处理：隔离级别脏读不可重复读幻想读说明1.未提交读可可可包含未提交数据的读2.已提交读*否可可读取提交过的数据，否则等待3.可重复读否否可查询期间，不允许其他事务对本结果集update4.可序列化否否否查询期间，不允许其他事务对本结果集insert或delete隔离级别与锁>隔离级别>未提交读EX:未提交读：（脏读、不可重复读、幻想读）意义：包含未提交数据的读。例如，在多用户环境下，用户B更改了某行。用户A在用户B提交更改之前读取已更改的行。如果此时用户B再回滚更改，则用户A便读取了逻辑上从未存在过的行。（如图演示）演示：1）用户B：BEGINTRANUPDATECOUPON_ABNORSETMSG_FLG=1WHERECONTENT_ID=4612）用户A：SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED（此句不写即默认为READCOMMITTED模式）SELECTMSG_FLGFROMCOUPON_ABNORWHERECONTENT_ID=461（此时将查到值为1）3）用户B：ROLLBACK（此时撤消了步骤1的UPDATE操作，则用户A读到的错误数据被称为脏读）隔离级别与锁>隔离级别>可提交读EX:提交读：（脏读、不可重复读●、幻想读●）[“●”可以]意义：指定在读取数据时（控制共享锁）以避免脏读。此隔离等级的主要作用是避免脏读。演示:1.不可脏读1）用户B：BEGINTRANUPDATECOUPON_ABNORSETMSG_FLG=1WHERECONTENT_ID=4612）用户A：SETTRANSACTIONISOLATIONLEVELREADCOMMITTEDSELECTMSG_FLGFROMCOUPON_ABNORWHERECONTENT_ID=461（上句设置了提交读模式，则此

时将会查不到数据，显示查询等待中，直到用户B进行了ROLLBACK或者COMMIT操作后，此语句才

会生效）3）用户B：COMMIT4）用户A：

查询出结果隔离级别与锁>隔离级别>可重复读EX:可重复读：意义：在多用户环境下，用户A开了一个事务，并且先对test表的某条记录做了查询（select*fromtestwherename=‘AA’），接着用户B对test表做了更新并提交（updatetestsetage=25wherename=’AA’），这时A再去查test表中的这条记录，第一次读到的age值为12，第二次为25，两次读到的数据不一样，称之为非重复读。演示：1）用户A:SETTRANSACTIONISOLATIONLEVELREPEATABLEREADBEGINTRANSELECTMSG_FLGFROMCOUPON_ABNORWHERECONTENT_ID=4612)用户B:UPDATECOUPON_ABNORSETMSG_FLG=1WHERECONTENT_ID=461（等待中）3)用户A:COMMITTRANSACTION4）用户BUPDATE成功隔离级别与锁>隔离级别>可序列化EX:可序列化：意义：在多用户环境下，用户A开启了一个事务，并查询test表中的所有记录，然后用户B在自己的事务中插入（或删除）了test表中的一条记录并提交事务，此时用户A再去执行前面的查询整张表记录的操作，结果会多出（少了）一条记录，此操作称之为幻象。（如图演示）演示：1）用户A:SETTRANSACTIONISOLATIONLEVELSERIALIZABLEBEGINTRANSELECTMSG_FLGFROMCOUPON_ABNORWHEREABNORCOUPON_ID='0000000000000000000'2)用户B:INSERTINTOCOUPON_ABNOR(ABNORCOUPON_ID,RGST_ID,TIME_ORIGIN,ABNOR_CODE,STORE_ID,MSG_FLG)VALUES(‘0000000000000000000’,‘11’,getdate(),1,‘CS11’,0)--(等待中)3）用户A:COMMITTRANSACTION隔离级别与锁>隔离级别>小结1.未提交读：主动

增强自身读能力2.提交读：主动降低自身读能力3.可重复读：主动+被动降低自身读能力+增强事务自身保护力4.可序列化：主动+被动降低自身读能力+增强事务自身保护力隔离级别与锁>锁做出了以上分析，现在我们可能会存在这样的疑问，到底在执行SQL语句的时候发出什么样的锁呢？这就由事务的隔离级别决定了。一