Mysql 事务、锁

1、Mysql 事务

1.1 什么是事务
事务就是一段sql语句的批处理，但是这个批处理是一个atom（原子） ，不可分割，要么都执行，要么回滚（rollback）都不执行。这样就避免了某个操作成功某个操作失败。利于数据的安全 注意：mysql数据支持事务，但是要求必须是innoDB存储引擎 

1.2 为什么出现这种技术
为什么要使用事务这个技术呢？ 现在的很多软件都是多用户，多程序，多线程的，对同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。这样很抽象，举个例子： 
A 给B 要划钱，A 的账户-1000元， B 的账户就要+1000元，这两个update 语句必须作为一个整体来执行，不然A 扣钱了，B 没有加钱这种情况很难处理（找出原因）。

1.3 事务的特性

事务都应该具备ACID特征。所谓ACID是Atomic（原子性），Consistent（一致性），Isolated（隔离性），Durable（持续性）四个词的首字母所写，下面以“银行转帐”为例来分别说明一下它们的含义：

原子性：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。换句话说，事务是不可分割的最小单元。

比如：银行转帐过程中，必须同时从一个帐户减去转帐金额，并加到另一个帐户中，只改变一个帐户是不合理的。

一致性：在事务处理执行前后，数据库是一致的。也就是说，事务应该正确的转换系统状态。

比如：银行转帐过程中，要么转帐金额从一个帐户转入另一个帐户，要么两个帐户都不变，没有其他的情况。

隔离性：一个事务处理对另一个事务处理没有影响。如果多个事务并发执行，应象各个事务独立执行一样！就是说任何事务都不可能看到一个处在不完整状态下的事务。

比如说，银行转帐过程中，在转帐事务没有提交之前，另一个转帐事务只能处于等待状态。

持续性：事务处理的效果能够被永久保存下来。反过来说，事务应当能够承受所有的失败，包括服务器、进程、通信以及媒体失败等等。

比如：银行转帐过程中，转帐后帐户的状态要能被保存下来。

1.4 MySQL/InnoDB定义的4种隔离级别
4个隔离级别：read uncommited ，read commited ，repeatable read ，serializable 。

read uncommited: 即脏读，一个事务修改了一行，另一个事务也可以读到该行。如果第一个事务执行了回滚，那么第二个事务读取的就是从来没有正式出现过的值。 

read commited(解决脏读引起不可重复读问题)：即一致读，试图通过只读取提交的值的方式来解决脏读的问题，但是这又引起了不可重复读取的问题。一个事务执行一个查询，读取了大量的数据行。在它结束读取之前，另一个事务可能完成了对数据行的更改。当第一个事务试图再次执行同一个查询，服务器就会返回不同的结果。

repeatable read（解决不可重复读问题引起幻读问题）: 即可重复读，在一个事务对数据行执行读取或写入操作时锁定了这些数据行。但是这种方式又引发了幻想读的问题。因为只能锁定读取或写入的行，不能阻止另一个事务插入数据，后期执行同样的查询会产生更多的结果。mysql默认的级别。

serializable（解决幻读问题）：模式中，事务被强制为依次执行。这是 SQL 标准建议的默认行为。

在分布式的系统中，通常会有多个线程连接到数据库中同时对一个表进行操作(这里的同时并不表示同一个时间点，而是同时竞争cpu的资源，至于如何调度，就要看线程和操作系统如何进行调度了)，这种情况下如果会话的事物设置不当，就会导致数据混乱，常常会出现以下三种情况(假设现在系统中有两个会话A和B，同时对表T_Test操作)：
1.脏读：如果有A向B 做了这个操作：update account set money=money+100 where name='B'；在没有commit 之前B 查询：select money from account where name='B'；找到了没有提交的money ，之后A在此时有rollback ，B 再查询，100 不见了。为了避免提高级别：read committed 。就是只能读取提交后的东东。

2.不可重复读：1中说明的就是我们不能读取一个事务的中间状态。

可重复读是指我们每次读取到的结果都要一致。这个也是mysql默认的级别。

3.幻读：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。和不可重复读的区别是：

不可重复读是读取到了别人对表中的某一条记录进行了修改，导致前后读取的数据不一致。  

虚读(幻读)是前后读取到表中的记录总数不一样，读取到了其它事务插入的数据。

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

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

2、Mysql如何使用事务 
（1）在执行sql语句之前，我们要开启事务 start transaction; 
（2）正常执行我们的sql语句 
（3）当sql语句执行完毕，存在两种情况： 
     1，全都成功，我们要将sql语句对数据库造成的影响提交到数据库中，committ 
     2，某些sql语句失败，我们执行rollback（回滚），将对数据库操作赶紧撤销 

2.1 启动事务的方法
认为分为两种：
1、begin ，rollback,commit .当然有的人用begin /begin work .推荐用START TRANSACTION 是SQL-99标准启动一个事务。
start transaction；
    update from account set money=money-100 where name='a';
    update from account set money=money+100 where name='b';
commit；
解释： 这样start transaction 手动开启事务，commit 手动关闭事务。

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

当你 set autocommit=0 时候，你可以这样：
update from account set money=money-100 where name='a';
update from account set money=money+100 where name='b';
commit；
// 默认都不提交，只有手动键入commit 时候才上述都提交。

但注意当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束，注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务！
个人推荐使用第一种方法！
MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持的！（切记！）

3、非InnoDB怎么办？(锁)
肯定有人会说那我mysql 的默认MyISAM 怎么办？ 没有事务这样的事情怎么处理呢？ 这个要用到另外一种技术叫做LOCK ! 实际上实现上边那4 个安全级别的所用的技术就是LOCK ！

我怎么在处理锁的问题上，经常听到：共享锁、排它锁、悲观锁、乐观锁、行级锁、表级锁。

共享锁： 就是在读取数据的时候，给数据添加一个共享锁。共享和共享直接是不冲突的，但是和排他锁是冲突的。

排他锁： 更新数据的时候，安装排他锁，禁止其他一切行为。

场景：老公去在 ATM 上取钱，老婆在柜台存钱，假设这个账户中有 1000 元。老公首先执行查询操作，查询到账户余额为 1000 此时程序将 1000 拿到内存中，老公取了 200 元，程序就执行了更新操作将账户余额改为 800，但是当老公的程序没有 commit 的时候，老婆查询账户，此时账户余额还是 1000 元，老婆存入 200 元，程序执行了更新操作将账户余额改为 1200，然后老公将更新语句提交，接着老婆也将更新语句提交。最后导致的结果就是该账户的余额为 1200，这就是更新丢失的问题。引发更新丢失的根源就是查询上，因为双方都是根据从数据库查询到的数据再对数据库中的数据进行更新的。

解决更新丢失有三个方案：

(1) 将事务隔离级别设置为最高，采用死锁策略。

(2) 采用悲观锁，悲观锁不是数据库中真正的锁，是人们看待事务的态度。

(3) 采用乐观锁，乐观锁也不是数据库中真正的锁。

第一个方案：老公进行查询操作，数据库为表增加了共享锁，老婆进行查询操作时数据库也增加了一个共享锁。但是当老公进行更新数据库操作时，由于老婆拿着共享锁，导致老公不能增加排它锁，老婆进行更新操作时，因为老公拿着共享锁，导致老婆也拿不到排它锁，这就发生了死锁现象，你等我，我等你。在 mysql 中，处理死锁的方案是释放掉一方的锁。这样就保证了一方更新成功，但是这种性能极低，因为数据库频繁在解决死锁问题。

悲观锁（更新多，查询少时用）
第二个方案:即采用悲观锁。就是我们在操作数据库时采用悲观的态度，认为别人会在此时并发访问数据库。我们在查询语句中 select * from account where name='aaa' for update; 等于加了排它锁。当老公查询余额的时候，select money from account where name='aaa' for update; 增加了排它锁，老婆查询账户余额的时候， select money from account where name='aaa' for update；也要求对数据库加排它锁，因为老公已经拿到了排它锁，导致老婆不能加锁，所以老婆只有等待老公执行完毕，释放掉锁以后才能继续操作。

乐观锁（更新少，查询多时用）
第三个方案:即采用乐观锁，就是我们在操作数据库的时候会认为没有其它用户并发访问，但是乐观锁也不是完全乐观的，乐观锁是采用版本号的方式进行控制的。在数据库表中有一列版本号。从数据库中查询的时候，将版本号也查询过来，在进行更新操作的时候，将版本号加1，查询条件的版本号还是查询过来的版本号。比如，老公执行查询操作的时候，select money,version from account where name='aaa'; 假设此时查询到的版本号为 0，老公在进行更新操作的时候 update account set money=money+100,version=version+1 where name='aaa' and version=0; 未提交时老婆来查询，查询到的版本号依然是 0，老婆也执行更新操作 update account set money=money+100,version=version+1 where name='aaa' and version=0; 现在老公提交了事务，老婆再提交事务的时候发现版本号为 0 的记录没有了，所以就避免了数据丢失的问题。不过这种情况也导致了多个用户更新操作时，只有一个用户的更新被执行。

行级别的锁：
select * from employee where employeeID=9857 for update;  

where 后边是索引列不是索引列那么就为表级别的锁

3.1 不同锁的优缺点及选择

行级锁的优点及选择 ：
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 操作。


参考来源：

Mysql 事务（一） 

深入浅出mysql事务处理和锁机制