mysql事务_MySQL

　介绍

mysql 从4.1 就开始支持事务处理，但是只有用 InnoDB /BDB 类型的引擎创建的数据库才支持事务操作。

查看mysql数据库创建引擎类型：show create table table_name

创建或修改指定类型数据库：Create table .... type=InnoDB； Alter table table_name type=InnoDB;

mysql 事务类型

认为分为两种：

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；

<code class="language-sql"><code class="language-sql">解释： 这样start transaction 手动开启事务，commit 手动关闭事务。

<code class="language-sql"><code class="language-sql">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；

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">mysql事务级别

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">在介绍事务级别之前需要了解常见在数据库查询和更新过程中出现的一些问题，简单来讲就是这些事务级别是为了解决什么问题而存在的。 ?

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">1.不可重复读 重复读指的是我们在每次读取的结果都需要是一致的。那么不可重复读就是这个问题会出现每次读取相关的数据出现不一致。出现这个的原因是读取到了其他会话对这条记录进行了修改。<br> <br> 2.幻读 幻读指的是当前会话来读取相关记录时，出现其他会话对这个记录添加了一条记录。为了避免幻读通常将事务的隔离级别设置为 serializable 。但是随之而来的问题就有了，当数据库的事务级别设置为serializable级别，数据库就变成了单线程访问数据库，导致性能下降非常大。<br> <br> 3.脏读 例如：用户A\B同时向t_accout 表操作，A对t_accout做了这样一个操作，update account set money=money+100 ；在没有提交之前，B用户去查询到没有提交的的money，而后A此时有了一个rollback，B用户再查发现钱少了100.这时候为了避免这个情况，一般设置事务的级别为 <strong>read committed 。就是只能读取提交后的东东 ；</strong><br> <br> 为了解决上述的问题出现了事务的级别，以下是数据库中常见的事务级别。<br> <br> 级别<br> <br> 1.读未提交（Read Uncommitted）：这种隔离级别可以让当前事务读取到其它事物还没有提交的数据。这种读取应该是 在回滚段中完成的。通过上面的分析，这种隔离级别是最低的，会导致引发脏读，不可重复读，和幻读。 2.读已提交（Read Committed）：这种隔离级别可以让当前事务读取到其它事物已经提交的数据。通过上面的分析，这种隔离级别会导致引发不可重复读，和幻读。 3.可重复读取（Repeatable Read）：这种隔离级别可以保证在一个事物中多次读取特定记录的时候都是一样的。通过上面的分析，这种隔离级别会导致引发幻读。 4.串行（Serializable）：这种隔离级别将事物放在一个队列中，每个事物开始之后，别的事物被挂起。同一个时间点只能有一个事物能操作数据库对象。这种隔离级别对于数据的完整性是最高的，但是同时大大降低了系统的可并发性。<br> <strong>各个级别对应可能出现的问题</strong><br> <br> <strong><img alt="\" style="max-width:90%" src="http://img.bitscn.com/upimg/allimg/c150410/142V3E423Z60-11355.jpg" style="max-width:90%"></strong>

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">锁机制

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><small><small>除了事务来处理以上不可重复读、幻读、脏读等问题。还有锁也能解决上述问题。</small></small> 在讲解锁之前，需要对常见的几个锁的名词有一个感性的认识。

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">排它锁：

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">由写表操作加上的锁，加锁后其他用户不能获取该表或行的任何锁，典型是mysql事务中。也就是既不能对表查询，也不能对表进行修改。<br> 例如：为user表加排它锁

start transaction;
 
select * from user where userId = 1 for update;

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">共享锁：

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获取排它锁，也就是说只能读不能写 排它锁和共享锁都是针对某个数据库来讲的，而通常我们听到的还有例如悲观锁、乐观锁、行锁、表锁。<br> 悲观锁其实是针对于开发者或者说是程序员来讲的。悲观锁就是认为在操作数据库时，悲观的认为会在同一时间出现并发访问问题。一般是更新多、查询少的时候用。与之对应的是乐观锁，一般在更新少、查询多的时候用。<br> <br> 这里援引来自网上的一个经典的实例来讲解事务的现实使用。

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">实例讲解

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">场景：老公去在 ATM 上取钱，老婆在柜台存钱，假设这个账户中有 1000 元。老公首先执行查询操作，查询到账户余额为 1000 此时程序将 1000 拿到内存中，老公取了 200 元，程序就执行了更新操作将账户余额改为 800，但是当老公的程序没有 commit 的时候，老婆查询账户，此时账户余额还是 1000 元，老婆存入 200 元，程序执行了更新操作将账户余额改为 1200，然后老公将更新语句提交，接着老婆也将更新语句提交。最后导致的结果就是该账户的余额为 1200，这就是更新丢失的问题。引发更新丢失的根源就是查询上，因为双方都是根据从数据库查询到的数据再对数据库中的数据进行更新的。解决更新丢失有三个方案：(1) 将事务隔离级别设置为最高，采用死锁策略。(2) <strong>采用悲观锁</strong>，悲观锁不是数据库中真正的锁，是人们看待事务的态度。(3)<strong> 采用乐观锁</strong>，乐观锁也不是数据库中真正的锁。

 

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

 

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><strong>悲观锁（更新多，查询少时用）</strong>

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">如果我们采用的是第二个方案时，即采用悲观锁。就是我们在操作数据库时采用悲观的态度，认为别人会在此时并发访问数据库。我们在查询语句中 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；也要求对数据库加排它锁，因为老公已经拿到了排它锁，导致老婆不能加锁，所以老婆只有等待老公执行完毕，释放掉锁以后才能继续操作。

 

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><strong>乐观锁（更新少，查询多时用）</strong>

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">如果我们采用的是第三个方案时，即采用乐观锁，就是我们在操作数据库的时候会认为没有其它用户并发访问，但是乐观锁也不是完全乐观的，乐观锁是采用版本号的方式进行控制的。在数据库表中有一列版本号。从数据库中查询的时候，将版本号也查询过来，在进行更新操作的时候，将版本号加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 的记录没有了，所以就避免了数据丢失的问题。不过这种情况也导致了多个用户更新操作时，只有一个用户的更新被执行。

 

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">附：各数据库默认事务级别

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">1、mysql的默认事务的隔离级别：可重复读取(repeatable read);

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">2、sqlserver的默认事务的隔离级别：提交读取(read committed);

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">3、oracle的默认事务的隔离级别：提交读取(read committed).