MySQL事务

MySQL事务

Transaction(事务)定义

• 事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
• 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
• 事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

作用：

1. MySQL 事务主要用于处理操作量大，复杂度高的数据
2. 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
3. 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
4. 事务用来管理 insert,update,delete 语句

事务的特性

一般来说，事务是必须满足4个条件ACID：Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（持久性）

• Atomicity: 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样，原子性消除了系统处理操作子集的可能性
• Consistency：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。举个例子：A向B转账，假设转账之前这两个用户的钱加起来总共是2000，那么A向B转账之后，不管这两个账户怎么转，A用户的钱和B用户的钱加起来的总额还是2000，这个就是事务的一致性
• Isolation：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量
• Durability：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

事务相关术语

概览

事务开启、结束标志

• 开启标志
  • 每条SQL语句就是一个事务（自动提交模式)，即任何一条DML语句(insert、update、delete)执行，标志事务的开启
  • 直到用户执行COMMIT或者ROLLBACK为止算作一个事务
• 结束标志
  • 提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
  • 回滚：失败的结束，将所有的DML语句操作历史记录全部清空

常用事务控制语句

• BEGIN 或 START TRANSACTION

  # 显式地开启一个事务

• COMMIT 或者 COMIT WORK

  # COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的

• ROLLBACK 或者 ROLLBACK WORK

  # 回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

• SAVEPOINT identifier

  # SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT

• RELEASE SAVEPOINT identifier

  # 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常

• ROLLBACK TO identifier

  # 把事务回滚到标记点

• SET TRANSACTION

  # 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE

mysql事务处理方法

1. 用 BEGIN, ROLLBACK, COMMIT来实现
   • BEGIN 开始一个事务
   • ROLLBACK 事务回滚
   • COMMIT 事务确认
2. 直接用 SET 来改变 MySQL 的自动提交模式
   • SET AUTOCOMMIT=0 禁止自动提交
   • SET AUTOCOMMIT=1 开启自动提交

实际列子

在日常生活中，比如张三有15620元钱（在支付宝上面），李四有240元，现在张三要给李四转600元。这时候我们需要数据库中，在张三的15620上减掉600，在李四的240上面加上600。这是俩个mysql的语句，但是如果我们执行的时候，在减掉张三的语句成功执行之后，第二句语句执行失败（因为某一种原因）。那是不是这600块就凭空消失了呢？很明显，支付宝不可能让这样的事情发生。所以我们要把这俩个语句绑在一块，要么一起执行成功，要么就都执行失败。

# -*- coding: UTF-8 -*-

import MySQLdb  

#包的导入

db = MySQLdb.connect("ip地址，本机为localhost"，"用户名","密码","表名")

#打开数据库的连接 

cursor = db.cursor()

#使用cursor()方法获得操作游标

try:
   # 执行sql语句
   cursor.execute("update account set money=money-600 where name='zhangsan'")
   cursor.execute("update account set money=money+600 where name='lisi'")
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚    回滚到获取游标的位置开始重新执行  看代码上面的文字有说明
   db.rollback()

db.close()

#关闭数据库的连接

事务的并发问题

1. 脏读(Drity Read)：事务B修改数据但未提交，事务A读数据，然后B回滚，则A读到的是脏数据
2. 不可重复读(Non-repeatable read)：事务A第一次读取数据，事务B修改数据提交，事务A第二次读数据，两次数据不一致
3. 幻读(Phantom Read)：事务A update表的全部行，事务B插入一行，事务A就会发现表中还有未修改的行。（一般加Gap Lock间隙锁）

事务的隔离级别

• Read Uncommitted（读取未提交内容）
  • 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）
• Read Committed（读取提交内容）
  • 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
• Repeatable Read（可重读）
  • 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency
    Control）机制解决了该问题
• Serializable（可串行化）
  • 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示

数据库的各种锁

共享锁（读锁）、排它锁（写锁）

InnoDB引擎的锁机制：InnoDB支持事务，支持行锁和表锁用的比较多，Myisam不支持事务，只支持表锁

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁

意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁

说明：

1. 共享锁和排他锁都是行锁，意向锁都是表锁，应用中我们只会使用到共享锁和排他锁，意向锁是mysql内部使用的，不需要用户干预

2. 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
   共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
   排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

   对于锁定行记录后需要进行更新操作的应用，应该使用Select...For update 方式，获取排它锁。（用共享锁，在读了之后再写会阻塞，会导致死锁）

   这里说说Myisam：MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁

3. InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

乐观锁、悲观锁

悲观锁：悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）

1. 使用悲观锁，我们必须关闭mysql数据库的自动提交属性，采用手动提交事务的方式，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交
2. 需要注意的是，在事务中，只有SELECT... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般SELECT ... 则不受此影响。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)
3. 补充：MySQL select…for update的Row Lock与Table Lock

使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键（或有索引的地方），MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)

乐观锁：

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做（一般是回滚事务）。那么我们如何实现乐观锁呢，一般来说有以下2种方式：

1. 使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的“version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据
2. 乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）,和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突

总结：两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

　　另外，高并发情况下个人认为乐观锁要好于悲观锁，因为悲观锁的机制使得各个线程等待时间过长，极其影响效率，乐观锁可以在一定程度上提高并发度

表锁、行锁

表级锁(table-level locking)：MyISAM和MEMORY存储引擎

行级锁(row-level locking) ：InnoDB存储引擎

页面锁(page-level-locking)：BDB存储引擎

表级锁：开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。

MySQL的MVCC

详见

MySQL事务

标签：行记录   读一行   开始   时间   mic   close   HERE   共享锁   cursor