事务处理

  • 事务来自于2个独立的需求: 并发数据库访问 和 系统错误恢复.

  • 一个事务可以被看作是 一个单元的一系列SQL语句的集合.

    事务特性 (A.C.I.D)

  • A - 原子性 (Atomacity): 事务必须是原子工作单元,对于其数据修改,要么全都执行,要么全都不执行.

  • C - 一致性 (Consistency): 事务将数据库从一种一致状态转变为下一种一致状态.
  • I - 隔离性 (Isolation): 由并发事务所作的修改必须与任何其它并发事务所作的修改隔离.

  • D - 持久性 (Durability): 事务完成之后,它对于系统的影响是永久性的.即使出现致命的系统故障也将一直保持.

    事务的隔离级别

  • 不对数据库进行并发控制,会产生异常情况:

    1. 脏读 (Dirty Read): 一个事务读取另一个事务尚未提交的修改产生脏读,同一事务内不是脏读. - 由于读取过程中,另一事务更新了数据没有及时提交造成的(数据可能被回滚).
    2. 非重复读 (Nonrepeatable Read): 一个事务对同一行数据重复读取两次得到不同的结果. - 由于查询过程中,其他提交事务修改或删除数据造成的.
    3. 幻像读 (Phantom Read): 事务在操作过程中进行两次查询,第二次查询结果包含第一次查询中未出现的数据(不要求两次查询的SQL语句相同). - 由于两次查询中,另一事务插入数据造成的.
    4. 丢失修改 (Lost Update): a. 两个事物更新相同的数据源,第一个被提交,第二个被撤销,那么第一个做的更新也会被撤销. b. 两个并发事务同时读取同一行数据,第一个进行修改提交,第二个也进行修改提交,造成第一次写操作失效.
  • 为了兼顾并发效率和异常控制,标准SQL规范里定义了4个事务隔离级别:
    1. 未提交读 (Read Uncommitted): 更新语句没有提交,别的事务可以读到改变数据. - 允许 脏读.
    2. 已提交读 (Read Committed): 只能读取到已提交的数据. - 不允许 脏读, 允许 非重复读.(多数数据库默认该级别).
    3. 可重复读 (Repeatable Read): 同一事务先后执行同一查询语句得到一样的结果. - 不允许 脏读,非重复读, 允许 幻像读.
    4. 串行读 (Serializable): 串行化的读,当前事务执行时不允许别的事务并发进行.每次读需要获得 表级共享锁,读写都会阻塞. - 不允许 不一致现象 出现.
  • 各隔离级别对各种异常的控制能力: | | LU丢失修改 | DR脏读 | 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 |

事务隔离的实现 - 锁

  • 共享锁(S锁): 用于只读操作(SELECT),锁定共享的资源. - 不阻止其他用户 读, 只阻止其他用户 写.
  • 更新锁(U锁): 用于可更新的资源.防止多个会话读取,锁定,资源更新时发生死锁.
  • 独立锁(X锁,排他锁): 一次只有一个独占锁用在一个资源上,阻止其他锁.写是独占锁,可有效的防止 脏读.
  • Read Uncommited: 一个事务在写数据, 另外一事务则 不允许 同时进行 写操作, 但允许其他事务 读数据. 该隔离级别可以通过 “排他写锁” 实现.
  • Read Committed 读取数据的事务允许其他事务继续访问该数据; 但 未提交的写事务 会禁止 其他事务访问该数据. 可以通过 “瞬间共享读锁” 和 “排他写锁” 实现.
  • Repeatable Read 读取数据的事务 禁止写事务, 但 允许读事务, 写事务则禁止任何其他事务. 可以通过 “共享读锁” 和 “排他写锁” 实现.
  • Serializable 读数据 加 共享锁, 写数据 加 排他锁, 读写互斥.
  • 一般处理并发问题时的步骤:
    1. 开启事务.
    2. 申请写权限,也就是给对象(表或记录)加锁.
    3. 假如失败,则结束事务,过一会重试.
    4. 假如成功,也就是给对象加锁成功,防止其他用户再用同样的方式打开.
    5. 进行编辑操作.
    6. 写入所进行的编辑结果.
    7. 假如写入成功，则提交事务,完成操作.
    8. 假如写入失败，则回滚事务，取消提交.
    9. (7.8)两步操作已释放了锁定的对象,恢复到操作前的状态.

索引

  • 数据库创建索引的优点:提高系统的性能
    1. 创建唯一性的索引,保证数据库表中每一行数据的唯一性.
    2. 加速数据的检索速度 – 最主要的原因.
    3. 加速表与表之间的连接,特别在实现数据的参考完整性方面特别有意义.
    4. 使用分组和排序子句进行数据检索时,可以显著的减少查询中分组和排序的时间.
    5. 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能.
  • 数据创建索引的缺点:
    1. 创建索引和维护索引需要消耗时间,且随着数据增加而增加.
    2. 索引需要占物理空间,除了数据表占据数据空间之外,每个索引还要占一定的物理空间.
    3. 表的数据增加,删除,修改时,索引要动态维护,降低了数据维护的速度.
  • 什么样的列建立索引:
    1. 在主键的列上,强制该列的唯一性和组织表中数据的排列结构.
    2. 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度.
    3. 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的.
    4. 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间.
    5. 在经常使用在where子句中的列上面创建索引,加快条件的判断速度.
  • 什么样的列不创建索引:
    1. 在查询中很少使用或者作为参考的列不应该创建索引.
    2. 对于那些只有很少数据值的列也不应该增加索引(比如性别,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大.增加索引,并不能明显加快检索速度).
    3. 对于那些定义为text,image和bit数据类型的列不应该增加索引.因为这些列的数据量要么相当大,要么取值很少.
    4. 当修改性能远远大于检索性能时,不应该创建索引,因为修改性能和检索性能是矛盾的.
  • 创建索引的方法: 直接创建和间接创建.
  • 索引特征:
    1. 唯一性索引. – 保证在索引列中的全部数据是唯一的,不包含冗余数据.
    2. 复合索引. – 一个索引创建在多列上,可以减少表中索引的数量.