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)


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值