mysql的实验总结_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的操作。

终端1update 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)