mysql dba系统学习（22）数据库事务详解

上个星期去面试数据库管理员的工作，笔试通过之后就是直接的面试，他问了我一个问题，叫我介绍哈数据库的事务的看法和理解，但是不知所错的没有章法的乱答一气，唉唉，基础不扎实啊。

下面来好好的学习哈mysqlinnodb引擎的事务相关的知识

一，我们为什么需要数据库事务呢？

这个问题看似很简单，但是真的能够说明白还是要看看我们的基本功是不是扎实

例如，用户A给用户B通过ATM机转账1000元，那么A账户上就会少1000元，而B用户会多1000元

　在这个过程中，两个环节是关联的。第一个账户划出款项必须保证正确的存入第二个账户，如果第二个环节没有完成，整个的过程都应该取消，否则就会发生丢失款项的问题。整个交易过程，可以看作是一个事物，成功则全部成功，失败则需要全部撤消，这样可以避免当操作的中间环节出现问题时，产生数据不一致的问题。

二，数据库事务的特点

原子性
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

一致性
事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。
事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。

隔离性
由发并事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，
要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，
以使数据结束时的状态与原始事务执行的状态相同。

持久性
事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

三、如何在MYSQL 中使用事务

1、mysql什么引擎可以使用事务

只有InnoDB /BDB　的之类的transaction_safe table 才能支持。

首先需要你的mysql支持innodb引擎，查看你的mysql支持的引擎的语法为：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

mysql> show engines  \G; *************************** 1. row ***************************       Engine: ndbcluster      Support: NO      Comment: Clustered, fault-tolerant tables Transactions: NULL           XA: NULL   Savepoints: NULL *************************** 2. row ***************************       Engine: MRG_MYISAM      Support: YES      Comment: Collection of identical MyISAM tables Transactions: NO           XA: NO   Savepoints: NO *************************** 3. row ***************************       Engine: BLACKHOLE      Support: YES      Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO           XA: NO   Savepoints: NO *************************** 4. row ***************************       Engine: FEDERATED      Support: NO      Comment: Federated MySQL storage engine Transactions: NULL           XA: NULL   Savepoints: NULL *************************** 5. row ***************************       Engine: MEMORY      Support: YES      Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO           XA: NO   Savepoints: NO *************************** 6. row ***************************       Engine: InnoDB      Support: YES      Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES           XA: YES   Savepoints: YES *************************** 7. row ***************************       Engine: MyISAM      Support: DEFAULT      Comment: Default engine as of MySQL 3.23 with great performance Transactions: NO           XA: NO   Savepoints: NO *************************** 8. row ***************************       Engine: CSV      Support: YES      Comment: CSV storage engine Transactions: NO           XA: NO   Savepoints: NO 8 rows in set (0.00 sec) ERROR: No query specified

我们可以看到默认的数据库引擎是MyISAM不是innodb，那么如何才可以让其默认的数据库引擎是innodb呢？

可以在[mysqld] 加入：default_storage_engine=InnoDB;

建立InnoDB 表：

1	create table ...... engine=innodb;

如果某个表已经创建好了，你可以这样查看该表使用的数据库引擎：

1	show create table table_name;

如果该表的引擎不是innodb，你可以这样来修改，将该表使用innodb引擎：

1	alter table table_name engine=innodb;

2、如何使用事务

认为分为两种：

1、begin ，rollback,commit .当然有的人用begin /begin work .推荐用START TRANSACTION 是SQL-99标准启动一个事务。

1 2 3 4

start transaction update from account setmoney=money-100 where name='a'; update from account setmoney=money+100 where name='b'; commit

解释： 这样start transaction 手动开启事务，commit 手动关闭事务。

2、默认的时候autocommit=1 自动提交是开启的，所以你可以理解为每条语句一输入到mysql就commit 了。当你 set autocommit=0 时候，你可以这样：

1 2 3

update from account setmoney=money-100 where name='a'; update from account setmoney=money+100 where name='b'; commit

// 默认都不提交，只有手动键入commit 时候上述都提交

四，事务举例

1，创建表student

1 2	mysql> create table student(id int(10),name char(10),msg varchar(50)) engine=innodb ; Query OK, 0 rows affected (0.05 sec)

2，表student的数据内容

1 2 3 4 5 6 7 8 9 10 11

mysql> select * from student; +------+----------+------------+ | id   | name     | msg        | +------+----------+------------+ |    1 | chen     | goof       | |    2 | zhaoqian | DEDDEFFccc | |    3 | sunli    | nihaoma    | |    4 | zhenwang | ngood      | |    5 | meiguo   | nginx      | +------+----------+------------+ 5 rows in set (0.00 sec)

3，关闭事务自动提交

1 2 3 4 5 6 7

mysql> show variables like "autocommit"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit    | OFF   | +---------------+-------+ 1 row in set (0.01 sec)

4，修改数据保存点

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> insert into student values(6,'zhongguo','xiaoping') ; Query OK, 1 row affected (0.01 sec) mysql> savepoint s1 ;      //插入数据创建保存点是s1 Query OK, 0 rows affected (0.00 sec) mysql> update  student set  id=7 where name='meiguo' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> savepoint s2     //修改数据创建保存点s2     -> ; Query OK, 0 rows affected (0.00 sec) mysql> delete from student  ; Query OK, 6 rows affected (0.00 sec) mysql> savepoint s3;    //删除所有的表内容创建保存点s3 Query OK, 0 rows affected (0.00 sec)

5，当我们觉得我们的操作有误的话，我们可以rollback事务

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

回滚到s1 就有第六条数据，但是如果上述的创建保存点是在一个session进程的话，一旦rollback到s1那么后面的保存点也不存在了 mysql> rollback to savepoint s1     -> ; Query OK, 0 rows affected (0.00 sec) mysql> select * from student; +------+----------+------------+ | id   | name     | msg        | +------+----------+------------+ |    1 | chen     | goof       | |    2 | zhaoqian | DEDDEFFccc | |    3 | sunli    | nihaoma    | |    4 | zhenwang | ngood      | |    5 | meiguo   | nginx      | |    6 | zhongguo | xiaoping   | +------+----------+------------+ 6 rows in set (0.00 sec)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

mysql> select * from student; +------+----------+----------+ | id   | name     | msg      | +------+----------+----------+ |    6 | zhongguo | xiaoping | |    1 | chen     | goof     | |    5 | meiguo   | nginx    | +------+----------+----------+ 3 rows in set (0.00 sec) mysql> savepoint  good ; Query OK, 0 rows affected (0.00 sec) mysql> delete from student; Query OK, 3 rows affected (0.01 sec) mysql> select * from student; Empty set (0.00 sec) mysql> rollback to savepoint   good ; Query OK, 0 rows affected (0.00 sec) mysql> select * from student; +------+----------+----------+ | id   | name     | msg      | +------+----------+----------+ |    6 | zhongguo | xiaoping | |    1 | chen     | goof     | |    5 | meiguo   | nginx    | +------+----------+----------+ 3 rows in set (0.00 sec)

五，事务的ACID(Atomicity \Consistency \Isolation \Durablility)

A: 事务必须是原子（不可分割），要么执行成功进入下一个状态，要么失败rollback 到最初状态。

C：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。 这个一般通过外键来约束。

I：一个事务不能知道另外一个事务的执行情况（中间状态）

D：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

mysql 自己的MyISAM 没有通过acid 测试，但是InnoDB 可以做到。

在分布式的系统中，通常会有多个线程连接到数据库中同时对一个表进行操作(这里的同时并不表示同一个时间点，而是同时竞争cpu的资源，至于如何调度，就要看线程和操作系统如何进行调度了)，这种情况下如果会话的事物设置不当，就会导致数据混乱，常常会出现以下三种情况(假设现在系统中有两个会话A和B，同时对表student操作)：

1，脏读

session A 操作

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> select * from student; +------+----------+----------+ | id   | name     | msg      | +------+----------+----------+ |    6 | zhongguo | xiaoping | |    1 | chen     | goof     | |    5 | meiguo   | nginx    | +------+----------+----------+ 3 rows in set (0.00 sec) mysql> update student set id=2 where id=6; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> select * from  student; +------+----------+----------+ | id   | name     | msg      | +------+----------+----------+ |    2 | zhongguo | xiaoping | |    1 | chen     | goof     | |    5 | meiguo   | nginx    | +------+----------+----------+ 3 rows in set (0.00 sec)

然而在session A 没有commit 之前session B 查询 看到的结果却不是这样的

1 2 3 4 5 6 7 8 9 10 11

mysql> use test Database changed mysql> select * from student; +------+----------+----------+ | id   | name     | msg      | +------+----------+----------+ |    6 | zhongguo | xiaoping | |    1 | chen     | goof     | |    5 | meiguo   | nginx    | +------+----------+----------+ 3 rows in set (0.00 sec)

2.不可重复读

1中说明的就是我们不能读取一个事务的中间状态。 而重复读是指我们每次读取到的结果都要一直。 这个也是mysql默认的级别。

1 2 3 4 5 6 7

mysql> select @@tx_isolation ; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)

3.虚读

在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。和不可重复读的区别是：不可重复读是读取到了别人对表中的某一条记录进行了修改，导致前后读取的数据不一致。 虚读是前后读取到表中的记录总数不一样，读取到了其它事务插入的数据。比如现在有 A 和 B 两个应用程序，他们并发访问了数据库中的某一张表，假设表中有3 条记录，B 执行查询操作， 第一次查询表得到了 3 条记录。此时 A 对表进行了修改，增加了一条记录，当 B 再次查询表的时候，发现多了一条数据。这种情况就造成了 B 的虚读。但是虚读是不一定每次都发生的，这种情况是不确定的。为了避免虚读，我们可以将事物隔离级别设置为 serializable 如果设置成了这种级别，那么数据库就变成了单线程访问的数据库，导致性能降低很多。

一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

六，事物的隔离级别

默认的事务隔离级别 

1 2 3 4 5 6 7

mysql> show variables like "tx_isolation" ; +---------------+-----------------+ | Variable_name | Value           | +---------------+-----------------+ | tx_isolation  | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec)

（1）Serializable：可避免脏读、不可重复读、虚读情况的发生。

（2）Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读，是 mysql 默认的事务隔离级别）

（3）Read committed：可避免脏读情况发生。（读取已提交的数据）

（4）Read uncommitted：最低级别，以上情况均无法保证。（读取到了未提交的数据）

当我们将数据库的隔离级别设置为：Serializable 的时候，虽然可以避免所有并发访问的问题，但是 Serializable 采用的是单线程来解决并发访问的问题，也就是说在某一段时间内，只能有一个用户对数据库进行操作，导致其它用户阻塞。导致数据库的访问性能很差。

1.读未提交（Read Uncommitted）：这种隔离级别可以让当前事务读取到其它事物还没有提交的数据。这种读取应该是在回滚段中完成的。通过上面的分析，这种隔离级别是最低的，会导致引发脏读，不可重复读，和幻读。

2.读已提交（Read Committed）：这种隔离级别可以让当前事务读取到其它事物已经提交的数据。通过上面的分析，这种隔离级别会导致引发不可重复读，和幻读。

3.可重复读取（Repeatable Read）：这种隔离级别可以保证在一个事物中多次读取特定记录的时候都是一样的。通过上面的分析，这种隔离级别会导致引发幻读。

4.串行（Serializable）：这种隔离级别将事物放在一个队列中，每个事物开始之后，别的事物被挂起。同一个时间点只能有一个事物能操作数据库对象。这种隔离级别对于数据的完整性是最高的，但是同时大大降低了系统的可并发性。