MySQL事务2013-5-30

 

 

资料：1.《MySQL技术内幕》SQL编程 2.《MySQL技术内幕》第四版

事务（transcation）是数据库区别文件系统的重要特征之一。事务就是将作为一个不可分割的逻辑单元而被执行的SQL语句（DML语句），该组语句要么全部成功，要么全部失败。

从理论上说事务应该是同时满足ACID特性。

A（atomicity），原子性。就死构成事务的所有SQL语句都是一个逻辑单元，不可分割，不能只执行它们中的一部分。

C（consistency）,一致性。即你在事务开始前和事务执行完之后，数据库的完整性没有被破坏，必须是稳定的。换句话说就是不应该经过执行事务之后吧你的数据库搞的一团乱。

I （isolation）,隔离性。每个事务对其他的事务都是不可见的，不应该互相影响。

D（dirable）,持久性。事务一旦提交，其结果时永远的被记录到数据库系统中。

MySQL是否支持事务安全是由数据引擎决定的，比如或MySQL的Innodb存储引擎和Falcon存储引擎是支持事务，而MyISAM存储引擎不支持事务安全性。

就以上的举个例子：A去银行转账，将100块钱转给B。银行系统需要作出的就是从A的账户上减去100，在B的账户上加上100。        

         update transcation1 set menory = (menory-100) where name='A';
         update transcation1 set menory = (menory+100) where name='B';

　　但是如果在万一银行在执行的过程中发生了意外，第一条语句执行完了。整个操作将不完整。A的账户了可以少了100,但是B的账户没有增加。如果没有事务，那就必须去手动去修改为原来的状态。

      就是事务的原子性，这两条语句必须是做为一个单元，要么全部失败，要么全部成功，数据也能和原来保持一致了。

 

      在MySQL命令行下的默认设置下，事务都是自动提交的，即当语句执行后就马上执行commit操作。用户对事务的控制语句主要有这些

      1.start transction或者begin:显示的开启一个事务，即不commit之前将不会自动提交。

        commit或者rollback：提交或者回滚。

        这一组一般都是一起的，所以再这里一起讨论。

    create table t(a_id int unsigned auto_increment,primary key(a_id),name varchar(10) not null,menory decimal(10,2));这是上面的例子创建的表

　　 首先插入两条数据

 insert into t values (null,'A',100.00),(null,'B',100.00);
 select * from t;

a_id	name	menory
1	A	100.00
2	B	100.00

  

　　如果再银行需要再增加一个账户

mysql> start transaction;
mysql> insert into t values(null,'C',1.00);

　　此时如果你启动另外一个mysql程序的实力后查询这个表格在提交之前是无法查询到的。结果还是原来的两条数据。当在commit之后另一个mysql窗口中才能看到。

     如果在插入D客户之后你发现这个D客户的金额数出错了，不要commit,直接rollback。之后再commit 也不会插入Dz这个客户。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(null,'D',2.
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.04 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select* from t;
+------+------+--------+
| a_id | name | menory |
+------+------+--------+
|    1 | A    | 100.00 |
|    2 | B    | 100.00 |
|    3 | C    |   1.00 |
+------+------+--------+
3 rows in set (0.00 sec)

　显示的开始一个事务使用begin 和start transaction是等价的。

   还有一种开始一个事务的方法就是set autocommit来开始事务，例

重新将表格清空，方便看清楚
mysql> truncate table t;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from t;
Empty set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(null,'A',200.00
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(null,'B',200.00
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;  插入两行后查询表有这两行
+------+------+--------+
| a_id | name | menory |
+------+------+--------+
|    1 | A    | 200.00 |
|    2 | B    | 200.00 |
+------+------+--------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from t;         执行rollback后查询表为空
Empty set (0.00 sec)

mysql> insert into t values(null,'A',200.00
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(null,'B',200.00
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from t;
+------+------+--------+
| a_id | name | menory |
+------+------+--------+
|    3 | A    | 200.00 |
|    4 | B    | 200.00 |
+------+------+--------+
2 rows in set (0.00 sec)

mysql> insert into t values(null,'C',200.00           commit前两条之后插入第三条
Query OK, 1 row affected (0.00 sec) 

mysql> rollback;                                      
Query OK, 0 rows affected (0.11 sec)

mysql> select * from t;                  rollback后还是没有插入C到t表，说明还是没有自动提交，需要你去手动的设置autocommit=1;不向start transaction那样能回到事务之前的状态
+------+------+--------+
| a_id | name | menory |
+------+------+--------+
|    3 | A    | 200.00 |
|    4 | B    | 200.00 |
+------+------+--------+
2 rows in set (0.00 sec)

　　2.savepoint identifier:savepoint允许在事务中创建一个保存点，一个事务中可以有多个savepoint;

      例：还是使用之前的表，清空之前的表，其实在start transaction是隐式的创建了一个savepoint.

mysql> select *from t;
Empty set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(null,'A',200.00);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(null,'B',200.00);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint mypoint_1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(null,'C',200.00);
Query OK, 1 row affected (0.00 sec)

mysql> rollback to savepoint mypoint_1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(null,'D',200.00);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t;   结果是C没有被插入
+------+------+--------+
| a_id | name | menory |
+------+------+--------+
|    1 | A    | 200.00 |
|    2 | B    | 200.00 |
|    4 | D    | 200.00 |
+------+------+--------+
3 rows in set (0.00 sec)

　　3.release savepoint identifier：删除一个事务的保存点，当没有一个保存点执行这句话，会抛出一个异常。这里就不做例子了。

     此外commit和commit work语句基本上是一致的，都用来提交事务的。不同之处在于commit work用来控制事务结束后的行为时chain还是release。

     如果是chain那么事务就成了链式事务了。可以使用completion_type来进行控制，默认该参数为0，

     当completion_type的参数为1时,commit work就等同于commit and chain,表示马上自动开启一个相同的隔离界别的事务。

     例

mysql> set @@completion_type=1;                       将completion_type设置为1，表示chain事务了。
Query OK, 0 rows affected (0.00 sec)

mysql> begin
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(null,'A',100.22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(null,'B',100.22);
Query OK, 1 row affected (0.00 sec)

mysql> commit work;                                  插入两条记录后commit
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values(null,'C',100.22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(2,'D',100.22);       
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> rollback;                                    这里rollback，后查询出来的只有A和B,说明C被回滚了，这里autocommit也是1说明是commit work时候有开始了一个隔离性的事务。
Query OK, 0 rows affected (0.08 sec)

mysql> select * from t;
+------+------+--------+
| a_id | name | menory |
+------+------+--------+
|    1 | A    | 100.22 |
|    2 | B    | 100.22 |
+------+------+--------+
2 rows in set (0.00 sec)

　　当chompletion_type=2时，commit work等同于commit and release.当事务提交后会自动断开与服务器的连接。

     例：

mysql> set @@completion_type=2;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(2,'D',100.22);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> insert into t values(null,'D',100.22);
Query OK, 1 row affected (0.00 sec)

mysql> commit work;
Query OK, 0 rows affected (0.03 sec)

mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: sampdb

　　还有需要注意的在rollback to point后还需要显示的调用commit或者rollback命令。

      

     事务支持四种不同的隔离级别。

     这里先说说隔离级别，就是对事务的隔离性的控制，不然它会发生的问题：

      1.脏读：指某个事务为提交时，就可以被其他事务看到。其的事务认为数据行已经被修改了，但对数据行作出的修改那个事务还有可能会回滚，这肯定会导致数据表混乱。

      2.不可重复读取：指一个事务使用同一个select语句读取数据表的数据，但是另一个事务在两个select中间对数据行进行修改，这样两次读取的数据就不一样。

      3.幻影数据行：这不可重复读取时同一个原来，在第二个select前，另一个事务插入了数据行。

     再说说事务的这四个隔离级别（对于Innodb存引擎）：

       READ UNCOMMITED：允许某个事务看到其他事务未提交的数据行动。

       READ COMMITED：只允许某个事务看到其他事务已经提交的数据行改动。

       REPEATABLE READ：即使有其他的事务在同时插入或数据行，这个事务所看到的结果也是一样的。在MySQL的Innodb中使用了Next-key lock锁算法（这个我不知道），因此避免了幻影数据行。                           

       SERIALIZABLE：在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新或插入记录，使事务串行执行，是四个隔离级别中限制最大的隔离级别，有可能会导致死锁或超时。并发度最低，慎用

      刚刚上面的例子，一个事务在为提交时，另一个mysql程序是看不到提交的数据的。上次是这样，因为InnoDB默认的隔离级别是repeatable read）.

       这是mysql窗口1:此时没有进行commit

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> use sampdb
Database changed
mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(null,'C',100);
Query OK, 1 row affected (0.00 sec)

　　   窗口2的内容

mysql> select * from t;
+------+------+--------+
| a_id | name | menory |
+------+------+--------+
|    1 | A    | 100.11 |
|    2 | B    | 100.11 |
|    3 | C    | 100.00 |
+------+------+--------+
3 rows in set (0.00 sec)

　　这里窗口2的能看到窗口1插入的数据，但是还有commit.