【Day32】mysql中事务的一些注意的地方

最近很多朋友都在讨论mysql事务的问题，记得以前在博客中提过，今天我们详细的总结一下mysql的事务机制，首先从概念，属性，使用方法，优点几个方面来说：

概念：那什么是事务呢， 事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

值得注意的是MySQL中不是所有的存储类型都支持事务。

    首先我们引入存储引擎的概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。这些技术中的每一种技术都使用不同的存储机制，索引 技巧，并且最终提供不同的功能和能力。通过选择不同的技术，获得额外的速度或功能，从而改善应用的整体功能。这些不同的技术以及配套的相关功能在mysql中被称为存储引擎（也称为表类型）我们可以通过show engines，来查看mysql支持的存储引擎。

下面我们将列举几个支持事务的存储类型，还有不支持事务的存储类型,如下：

    在mysql中用的最多的存储引擎有：innodb，bdb，myisam ,memory 等。其中innodb和bdb支持事务而myisam等不支持事务。

事务是用来做什么的：

事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务用来管理insert,update,delete语句。


事务具备四个特性（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1. 事务的原子性：一组事务，要么成功；要么撤回。

2. 稳定性 ： 有非法数据（外键约束之类），事务撤回。

3. 隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

4. 可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

   那我们如何使用事务呢：

第一，我们需要先开启事务：

    start transaction

第二，我们做一个保存点：

    savepoint 保存点名称

第三，进行sql操作

第四，可以回滚，可以提交，没有问题，就提交，有问题就回滚。

下面给出一个具体的实例（为了简单易懂，我们用原生php写）：

<?php

$handler=mysql_connect("localhost","root","password");

mysql_select_db("hello");

mysql_query("SET AUTOCOMMIT=0");//设置为不自动提交，因为MYSQL默认立即执行

mysql_query("BEGIN");//开始事务定义

if(!mysql_query("insert into trans (id) values('2')"))
{

mysql_query("ROLLBACK");//判断当执行失败时回滚

}
if(!mysql_query("insert into trans (id) values('4')"))

{

mysql_query("ROLLBACK");//判断执行失败回滚

}
mysql_query("COMMIT");//执行事务

mysql_close($handler);

?>

上面代码也可以说是事物的一个生命周期，这里我们用另一种方式来描述：

1、在创建事务的过程中，用户需要创建一个innodb或bdb类型的数据表，其基本命令结构如下：

 create  table table_name  (file  defintions)  type=innodb/bdb;

2、对表类型进行修改

 alert  table table-name  type =innodb/bdb;

3、事务的整个过程

 use  databases;   //使用某个数据库

 start transaction ;   //开始事务 、这里也可以使用 begin   、 beginwork

 insert  into stu1 values('',  );   //进行相关的操作

 commit    //提交事物

 rollback   //撤销事务（事务回滚）

需要注意的问题：

我们有时会发现，进行完一个事务后，数据表没有进行相应的操作，或者说没产生变化，问题可能出现的原因是：

我们在mysql中设置了自动提交，但我们也可以改为手动提交。

set   autocommit =0;  //关闭自动提交

 set   autocommit =1;  //开启自动提交

1、 在进行多用户的时候，事务的孤立性（隔离性）尤其重要，这样可以保证这些事务互不影响，保证数据库性能不受到影响。

2、mysql中提供的孤立级别有以下四种：

（1）SERIALIZABLE(序列化)  

  //以序列的形式处理事务，只有事务提交后，用户才能看到，但是该级别的孤立会影响mysql的性能，因为需要占用大量的资源，以保证使大量事务在任意时间不被用户看到。

（2）REPEATABLE  READ(可重读)

  // 相比序列化该级别在应用的安全性上做了部分妥协。

（3）READ COMMITTED(提交后读)

 // 提交后读的安全性比可重读还要低。在这一级的事务，用户可以看到其他事务添加的新记录，在事务处理时，如果存在其他用户同时对事务的相应表进行修改，那么同一事务在不同时间使用select 查询得到的结果集可能不同。

（4）READ  UNCOMMITTED(未提交读)

//安全性相比提交后读就更低，同时该孤立及也是事务之间最小的间隔（孤立程度），该孤立级容易产生虚幻读操作。其他用户可以在该孤立级上看到未提交的事务。

事务的查看和修改：

查看：select @@tx_isolation;

修改：set  global  transaction  isolation level  设置的孤立级别；

下面再讲一个重要的属性，就是伪事务，也就是我们说的锁定。

我们都知道在mysql中有的存储类型不支持事务，这个时候我们就需要根据不同的需求用表锁定来代替在事务。

对于不支持事务的存储引擎MYISAM类型数据表，当用户插入，修改，删除时，这些操作都会立即保存到磁盘中，当多用户同时操作某个表时，可以使用表锁定来避免同一时间有多个用户对数据库中指定表进行操作，这样可以避免在用户操作数据表过程中受到干扰。只有但用户释放表的操作锁定后，其他 用户才可以访问这些修改的数据表。

这里的锁和Java多线程中锁的作用一样，个人觉的可以这样理解。

对指定表进行锁操作的过程：

 (1)lock  table table-name lock type ;   //locktype 有read 和write 两种

对多个表进行加锁：

 lock table  table-name1 lock type, table -name2  lock type ,table -name3 lock type;

(2)在指定的表中进行相应的操作

(3)当用户完成对锁定数据表的操作后，进行解锁。

unlock tables ；  //释放了所有加锁表的锁。


四种事务隔离模式


 READ UNCOMMITED

SELECT的时候允许脏读，即SELECT会读取其他事务修改而还没有提交的数据。

READ COMMITED

SELECT的时候无法重复读，即同一个事务中两次执行同样的查询语句，若在第一次与第二次查询之间时间段，其他事务又刚好修改了其查询的数据且提交了，则两次读到的数据不一致。

REPEATABLE READ

SELECT的时候可以重复读，即同一个事务中两次执行同样的查询语句，得到的数据始终都是一致的。

SERIALIZABLE

与可重复读的唯一区别是，默认把普通的SELECT语句改成SELECT …. LOCK IN SHARE MODE。即为查询语句涉及到的数据加上共享琐，阻塞其他事务修改真实数据。

  不同锁的优缺点及选择

行级锁的优点及选择 ：

1 ）在很多线程请求不同记录时减少冲突锁。

2 ）事务回滚时减少改变数据。

3 ）使长时间对单独的一行记录加锁成为可能。

行级锁的缺点 ：

1 ）比页级锁和表级锁消耗更多的内存。

2 ）当在大量表中使用时，比页级锁和表级锁更慢，因为他需要请求更多的所资源。

3 ）当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时，就明显的比其它锁更糟糕。

4 ）使用更高层的锁的话，就能更方便的支持各种不同的类型应用程序，因为这种锁的开销比行级锁小多了。

5 ）可以用应用程序级锁来代替行级锁，例如 MySQL 中的 GET_LOCK() 和 RELEASE_LOCK() 。但它们是劝告锁（原文： These are advisory locks ），因此只能用于安全可信的应用程序中。

6 ）对于 InnoDB 和 BDB 表， MySQL 只有在指定用 LOCK TABLES 锁表时才使用表级锁。在这两种表中，建议最好不要使用 LOCK TABLES ，因为 InnoDB 自动采用行级锁， BDB 用页级锁来保证事务的隔离。

表锁的优点及选择：

1 ）很多操作都是读表。

2 ）在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：

UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;DELETE FROM tbl_name WHERE unique_key_col=key_value;

3 ） SELECT 和 INSERT 语句并发的执行，但是只有很少的 UPDATE 和 DELETE 语句。

4 ）很多的扫描表和对全表的 GROUP BY 操作，但是没有任何写表。

表锁的缺点：

1 ）一个客户端提交了一个需要长时间运行的 SELECT 操作。

2 ）其他客户端对同一个表提交了 UPDATE 操作，这个客户端就要等到 SELECT 完成了才能开始执行。

3 ）其他客户端也对同一个表提交了 SELECT 请求。由于 UPDATE 的优先级高于 SELECT ，所以 SELECT 就会先等到 UPDATE 完成了之后才开始执行，它也在等待第一个 SELECT 操作。

如何避免锁的资源竞争

1 ）让 SELECT 速度尽量快，这可能需要创建一些摘要表。

2 ）启动 mysqld 时使用参数 –low-priority-updates 。这就会让更新操作的优先级低于 SELECT 。

这种情况下，在上面的假设中，第二个 SELECT 就会在 INSERT 之前执行了，而且也无需等待第一个 SELECT 了。

3 ）可以执行 SET LOW_PRIORITY_UPDATES=1 命令，指定所有的更新操作都放到一个指定的链接中去完成。

4 ）用 LOW_PRIORITY 属性来降低 INSERT ， UPDATE ， DELETE 的优先级。

5 ）用 HIGH_PRIORITY 来提高 SELECT 语句的优先级。

6 ）可以在启动 mysqld 时指定系统变量 max_write_lock_count 为一个比较低的值，它能强制临时地提高表的插入数达到一个特定值后的所有 SELECT 操作的优先级。它允许在 WRITE 锁达到一定数量后有 READ 锁。

7 ）当 INSERT 和 SELECT 一起使用出现问题时，可以转而采用 MyISAM 表，它支持并发的 SELECT 和 INSERT 操作。

8 ）当在同一个表上同时有插入和删除操作时， INSERT DELAYED 可能会很有用。

9 ）当 SELECT 和 DELETE 一起使用出现问题时， DELETE 的 LIMIT 参数可能会很有用。

10 ）执行 SELECT 时使用 SQL_BUFFER_RESULT 有助于减短锁表的持续时间。

11 ）可以修改源代码 `mysys/thr_lock.c’ ，只用一个所队列。这种情况下，写锁和读锁的优先级就一样了，这对一些应用可能有帮助。