Linux——MySQL事务

MySQL事务

1.事务概述

MySQL的事务是在存储引擎层实现。 MySQL的事务有ACID:

  • A 原子性(atomicity):一个事务必须被视为一个不可分割的单元。
  • C 一致性(consistency):数据库是从一种状态切换到另一种状态。
  • I 隔离性(isolation):事务在提交之前,对于其他事务不可见。
  • D 持久性(durablity):一旦事务提交,所修改的将永久保存到数据库。

2.事务的基本语法

示例:

任何一个事务涉及到的命令:

  • 事务的开始:start transaction或begin;
  • 事务提交:commit;
  • 事务回滚:rollback;
#创建表bank
mysql> create table bank
    -> (
    -> name varchar(25),
    -> money float
    -> );
Query OK, 0 rows affected (0.15 sec)

#把bank表内插入数据
mysql> insert into bank values('lu','1000'),('qi','5000');
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

#查看表bank
mysql> select * from bank;
+------+-------+
| name | money |
+------+-------+
| lu   |  1000 |
| qi   |  5000 |
+------+-------+
2 rows in set (0.00 sec)

#开启事务(开启事务方法有:begin或start transaction)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

#更新bank表内的数据
mysql> update bank set money=money-1000 where name='qi';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update bank set money=money+1000 where name='lu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#查看更新之后的数据
mysql> select * from bank;
+------+-------+
| name | money |
+------+-------+
| lu   |  2000 |
| qi   |  4000 |
+------+-------+
2 rows in set (0.00 sec)

#回滚事务
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

#再次查询数据,发现已经便会了原来的值
mysql> select * from bank;
+------+-------+
| name | money |
+------+-------+
| lu   |  1000 |
| qi   |  5000 |
+------+-------+
2 rows in set (0.00 sec)

#提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#查询数据
mysql> select * from bank;
+------+-------+
| name | money |
+------+-------+
| lu   |  1000 |
| qi   |  5000 |
+------+-------+
2 rows in set (0.00 sec)

3.查看自动提交模式是自动还是手动

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

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

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

4.事务有四种隔离级别

事务在提交之前对其他事务可不可见

  • read unaommitted——(未提交读)
  • read committed——(已提交读)
  • Repeatable read——(可重复读)
  • seaializable——(可串行化)

4.1.未提交读

事务中修改没有提交对其他事务也是可见的,俗称脏读

mysql> create table student
    -> ( 
    -> id int not null auto_increment,
    -> name varchar(32) not null default '',
    -> primary key(id)
    -> ) engine=InnoDB auto_increment=2 default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

以下操作需要自行开启两个MySQL会话终端,A和B,两个终端都需执行以下命令:

mysql> SET SESSION TX_ISOLATION='READ-UNCOMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)

客户端A:

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

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

mysql> insert into student(name) values('zhangyi');
Query OK, 1 row affected (0.00 sec)

#注意:此时事务未提交!!!

客户端B:

mysql> set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from student;
+----+---------+
| id | name    |
+----+---------+
|  2 | zhangyi |
+----+---------+
1 row in set (0.00 sec)

#查询表,即可看到客户A没有提交的事务

总结:以上可以看出未提交读隔离级别非常危险,对于一个没有提交事务所做修改对另一个事务是可见状态,出现了脏读!非特殊情况不建议使用此级别。

4.2 已提交读

  • 多数数据库系统默认为此级别(MySQL不是)。已提交读级别为一个事务只能已提交事务所做的修改,也就是解决了未提交读的问题。
#设置为已提交读
mysql> set session tx_isolation='read-committed';
Query OK, 0 rows affected, 1 warning (0.00 sec)

客户端A:

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

mysql> select * from student;
+----+---------+
| id | name    |
+----+---------+
|  2 | zhangyi |
+----+---------+
1 row in set (0.00 sec)

mysql> insert into student(name) values('zhanger');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+---------+
| id | name    |
+----+---------+
|  2 | zhangyi |
|  3 | zhanger |
+----+---------+
2 rows in set (0.00 sec)

客户端B:

mysql> select * from student;
+----+---------+
| id | name    |
+----+---------+
|  2 | zhangyi |
+----+---------+
2 rows in set (0.00 sec)

#并没有查看到客户端A刚刚插入的数据

客户端A:

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

客户端B:

mysql> select * from student;
+----+---------+
| id | name    |
+----+---------+
|  2 | zhangyi |
|  3 | zhanger |
+----+---------+
2 rows in set (0.00 sec)

#客户端A提交完成后便可查看到已经更新的数据

总结:从上面可以看出,提交读没有了未提交读的问题,但是我们可以看到客户端A的一个事务中客户端B执行了两次同样的SELECT语句,得到不同的结果,因此已提交读又被称为不可重复读。同样的筛选条件可能得到不同的结果。

4.3 可重复读

  • 可重复读解决了不可重复读的问题,数据库级别没有解决幻读的问题。

客户端A和客户端B同时操作(都设置为可重复读,然后两边都开启一个事务)

#两个客户端均设置为可重复读
mysql> set session tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

客户端A:

mysql> select * from test.student;
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangyi  |
|  3 | zhanger  |
+----+----------+
mysql> UPDATE test.student SET name='zhangwr' WHERE id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from test.student;
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangyi  |
|  3 | zhangwr  |
+----+----------+

客户端B:

mysql> select * from test.student;
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangyi  |
|  3 | zhanger  |
+----+----------+
5 rows in set (0.00 sec)

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

mysql> select * from test.student;
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangyi  |
|  3 | zhangwr  |
+----+----------+
5 rows in set (0.00 sec)

总结:上面的例子我们得知,可重复读两次读取的内容不一样。数据库的幻读问题并没有得到解决。幻读只读锁定里面的数据,不能读锁定外的数据,解决幻读出了mvcc机制Mvcc机制。简单说就是:客户端A锁定提交之后 客户端B并不会看到更新的内容,需要客户端B再次提交执行完成之后,客户端B才可以查看到更新的数据!!!

4.4 可串行化

  • 是最高隔离级别,强制事务串行执行,执行串行了也就解决问题了,这个只有在对数据一致性要求非常严格并且没有并发的情况下使用。

在客户端A及客户端B进行以下操作(设置为可串行读):

mysql> SET SESSION TX_ISOLATION='SERIALIZABLE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

客户的A:

mysql> begin;

mysql> select * from test.student where id < 10;
+----+---------+
| id | name    |
+----+---------+
|  2 | zhangyi |
|  3 | zhanger |
+----+---------+
2 rows in set (0.00 sec)

客户端B:

mysql> insert into test.student (name) values('zhangqi');
ERROR 1205 (HY000): Lock wait timeout exceeded; trying transaction

总结:我们发现INSERT 语句被阻塞执行,原因是A执行了查询表student同时满足id<10,已被锁定。如果查询表student同时满足id<5,则新增语句可以正常执行。

隔离级别

隔离级别脏读不可重复幻读加锁读
未提交读
提交读
可重复读
串行读
  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值