mysql复习小结二事务的实验总结

1.事务的概念：

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如事务开始和事务结束之类的语句来界定。事务由事务开始和事务结束之间执行的全体操作组成。

 

2.事务的特性ACID:

                            Automicity：原子性，事务所引起的数据库操作，要么都完成，要么都不执行；

                            Consistency：一致性，事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。                

                            Isolation:隔离性，事务调度：事务之间影响最小，如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。

                            Durability：一旦事务成功完成，系统必须保证任何故障都不会引起事务表示出不一致性；

                                    

3.隔离级别说明：

         READ UNCOMMITTED

         READ COMMITTED

         REPEATABLE READ

         SERIALIZABLE

 read uncommitted, read committed, repeatable read，serializable 这4种隔离级别的不同特点。

 1. Read uncommitted（未提交读） 

    所谓Read uncommitted ，就是指可以读取未提交的数据。两个事务T1和T2，T1执行的操作在事务未提交前，对T2是可见的。 

2. Read committed（已提交读） 

  所谓Read committed ，就是指只能读取提交后的数据。两个事务T1和T2，T1执行的操作在未提交前对T2是不可见的，提交后数据库的变化，对T2事务是可见的。

3. Repeatable read(可重复读) 

可重复读可以防止脏读和不可重复读的发生，但仍然会出现幻象。两个事务T1和T2，T1执行的操作，在T1未提交和T2未提交是均不可见的。在两者都提交后，T2可以查看到T1对数据库的修改。这是mysql默认的隔离级别。

4. serializable（可串行化） 

所谓serializable，就是指不允许多个事务同时进行。 如有两个事务T1，T2，如果T1在操作表的话，T2不可以对标进行查询等操作，直到T1提交事务才会释放掉锁。

4.隔离级别实验

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

 

另开启一个终端，现在有终端1和终端2；在终端1终端2分别执行下列语句。

隔离级别为read-uncommitted，未提及时就可以读取到数据。

mysql> set tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
 
mysql> select * from tb;
+------+------+
| id   | age  |
+------+------+
|    1 |   20 |
|    2 |   30 |
+------+------+
2 rows in set (0.00 sec)

在两个终端同时开启事务。

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

开启事务之后的操作有所不同了。下面分别指明终端1终端2的操作。

终端1

update tb set age=19 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed:1  Warnings: 0
 
mysql> select * from tb;
+------+------+
| id   | age  |
+------+------+
|    1 |   19 |
|    2 |   30 |
+------+------+
2 rows in set (0.00 sec)
终端2
mysql> select * from tb;
+------+------+
| id   | age  |
+------+------+
|    1 |   19 |
|    2 |   30 |
+------+------+
2 rows in set (0.00 sec)

可以看出，在同一个事务中，看到的却是修改后的数据。

终端1和2都提交以结束事务

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

一样，在终端1和终端2上修改隔离级别位read-committed（只有提交后才可以读取）

mysql> set tx_isolation='Read-committed';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

终端1和2都开启事务；

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

终端1执行插入操作

mysql> insert into tb values(1,4);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from tb;
+------+------+
| id   | age  |
+------+------+
|    1 |    4 |
+------+------+
1 row in set (0.00 sec)
 

终端2显示为空

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

终端1提交

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

终端2才能显示结果

mysql> select * from tb;
+------+------+
| id   | age  |
+------+------+
|    1 |    4 |
+------+------+
1 row in set (0.00 sec)

在终端1和2修改隔离级别并开启事务。

mysql> set tx_isolation='Repeatable-read';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

终端1插入数据

mysql> insert into tb values(3,6);
Query OK, 1 row affected (0.00 sec)

终端2查看不到

mysql> select * from tb;
+------+------+
| id   | age  |
+------+------+
|    1 |    4 |
|    2 |    5 |
+------+------+
2 rows in set (0.00 sec)

终端1提交

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

终端2查看不到

 

mysql> select * from tb;
+------+------+
| id   | age  |
+------+------+
|    1 |    4 |
|    2 |    5 |
+------+------+
2 rows in set (0.00 sec)

终端2提交

 

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

终端2查看到了

 

mysql> select * from tb;
+------+------+
| id   | age  |
+------+------+
|    1 |    4 |
|    2 |    5 |
|    3 |    6 |
+------+------+
3 rows in set (0.00 sec)

终端1和终端2都设置成串行化，开启事务

mysql> set tx_isolation='serializable'
    -> ;
Query OK, 0 rows affected (0.00 sec)

终端1执行更新操作

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update tb set age=4 where id=1;
Query OK, 0 rows affected (0.00 sec)

终端2执行查询操作

mysql> select * from tb;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restartingtransaction

这里的查看竟然无法执行。被加上了锁。然而，一旦终端1commit后，2就可以查看了

mysql> select * from tb;
+------+------+
| id   | age  |
+------+------+
|    1 |    4 |
|    2 |    5 |
|    3 |    6 |
|    4 |    7 |
+------+------+
4 rows in set (6.80 sec)
 
 

5.事务的执行实验

START TRANSACTION：启动
COMMIT： 提交
ROLLBACK: 回滚

默认的myisam是不支持事务的，我们需要修改为innodb，可以通过修改my.cf文件，也可以通过命令修改全局变量。rollback是全部回滚，rollback to s1是回滚到指定的savepoint s1处。commit提交后事务结束。

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)


mysql> show variables like '%storage_engine%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)


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


mysql> show variables like '%storage_engine%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)


mysql> create table tb (id int(4));
Query OK, 0 rows affected (0.01 sec)


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


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


mysql> insert into tb values (1);
Query OK, 1 row affected (0.00 sec)


mysql> select * from tb;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


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


mysql> insert into tb values (2);
Query OK, 1 row affected (0.00 sec)


mysql> select * from tb;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)


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


mysql> select * from tb;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


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


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


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