《一起学mysql》2
事务
爸妈让往他们银行卡里转点儿钱,可是我这钱全在支付宝里,爸妈又没有支付宝,只能从支付宝里转给他
们了,假如转账过程中,支付宝扣款成功了,但是银行系统崩溃了,钱没转进去,这咋整?我的大洋就这样
打水漂了?肯定不可以,为了避免这种情况的发生,就用到了事务,在转钱过程中遇到了任何差错,就回到
没转钱之前的状态,这个就叫做事务
事务四大特性(ACID)
以下特性纯属个人理解
原子性(Atomicity):转账前 -> 转账 -> 转账成功,以上述转账为例,转账这个动作包括【从我的支付宝扣
除money,在我爸妈的卡上增加money】,中括号里的内容要么全部执行(转账成功),如果没有全部执行就
回到转账前的状态(跟没执行一个效果),不能停留在转账的中间过程——我的支付宝扣了钱,爸妈银行卡没
多钱
一致性(Consistency):我的理解是能量守恒,转账前后,我的支付宝金额+我爸妈卡内金额是一致的
隔离性(Isolation):这个一般用在并发,两个线程的操作会互相影响的情况下,隔离性又分为若干个隔离级
别,下面具体讨论
永久性(Durability):只要事务提交了,就成了事实。
隔离级别
在说隔离级别之前先来说几个概念
脏读:读到了另一个事务未提交的更改
例如
事务一:包工头给农民工转账
事务二:农民工查看工资
事务一:开始事务
事务一:包工头给农民工转账1000
事务二:开始事务
事务二:农民工查看账户,多了1000块
事务二:提交
事务一:包工头回滚,转账失败
不可重复读:一个事务对同一记录的读取结果不一致,因为另一个事务更新了该记录,并提交
例如
事务一:查看宾馆8301 的状态
事务二:预订8301房间
事务一:开始事务
事务一:查看8301状态,未预定
事务二:开始事务
事务二:预定8301房间
事务二:提交
事务一:再次查看8301 状态,被预定
幻读:一个事务执行同一个查询结果不一样,因为另一事务插入了新的记录,并提交
例如
事务一:统计网站的注册用户数
事务二:注册新的用户
事务一:开始事务
事务一:查看注册用户数为10000
事务二:开始事务
事务二:新增一个用户(插入一条记录)
事务二:提交
事务一:查看注册用户数为10001
四种隔离级别
|
是否允许脏读
|
是否不可重复读
|
是否幻读
|
read uncommitted
|
是 |
是
|
是
|
read committed
|
否
|
是
|
是
|
repeatable read
|
否
|
否
|
是
|
serializable
|
否
|
否
|
否
|
事务的使用:
MariaDB [jason]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [jason]> insert into test values(2);
Query OK, 1 row affected (0.00 sec)
MariaDB [jason]> select * from test;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
MariaDB [jason]> rollback;
Query OK, 0 rows affected (0.00 sec)
MariaDB [jason]> select * from test;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
MariaDB [jason]> insert into test values(2);
Query OK, 1 row affected (0.00 sec)
MariaDB [jason]> commit;
Query OK, 0 rows affected (0.00 sec)
MariaDB [jason]> select * from test;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql默认自动开启事务,可以通过begin 手动开启事务,如果想要回到begin之前的状态,则rollback,
操作完成后记得commit,否则推出窗口会就等同rollback 了
mysql 中的锁
行级别锁只对事务安全的表(innodb,bdb)有效
for update 行级锁
首先我们先建一张表,插入两条数据
create table test(
f1 int,
f2 char(10),
index (f1)
)engine=innodb;
insert into test (f1) values
(1),
(2);
假设我们现在有个需求,f1 必须是唯一的,不可重复,每次插入只能插入当前最大值加 1,假设有两个窗
口各有一个事务,在读取最大值并插入
窗口1:
MariaDB [jason]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [jason]> select max(f1) from test;
+---------+
| max(f1) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
MariaDB [jason]> insert into test (f1) values(3);
Query OK, 1 row affected (0.00 sec)
窗口2:
MariaDB [jason]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [jason]> select max(f1) from test;
+---------+
| max(f1) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
MariaDB [jason]> insert into test (f1) values(3);
Query OK, 1 row affected (0.00 sec)
窗口1:
MariaDB [jason]> commit;
Query OK, 0 rows affected (0.01 sec)
窗口2:
MariaDB [jason]> commit;
Query OK, 0 rows affected (0.01 sec)
现在查看数据:
MariaDB [jason]> select * from test;
+------+------+
| f1 | f2 |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 3 | NULL |
+------+------+
4 rows in set (0.00 sec)
结果我们插入了重复的数据,这与我们的要求相背离了,mysql 提供了锁,可以解决这个问题
我们先把错误数据删掉
MariaDB [jason]> delete from test where f1=3;
Query OK, 2 rows affected (0.00 sec)
我们可以用一个for update 锁来达到这个目的,只能有一个会话可以拥有这个锁,当另一个会话也申请这个锁
时,会暂时卡住,直到前一个会话commit 释放锁之后,
窗口1:
MariaDB [jason]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [jason]> select max(f1) from test for update;
+---------+
| max(f1) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
窗口2:
MariaDB [jason]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [jason]> select max(f1) from test;
+---------+
| max(f1) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
MariaDB [jason]> select max(f1) from test for update;
窗口2 暂时没有查询结果,要等到 窗口1 commit 之后才行
窗口1:
MariaDB [jason]> insert into test (f1) values (3);
Query OK, 1 row affected (0.00 sec)
MariaDB [jason]> commit;
Query OK, 0 rows affected (0.01 sec)
i窗口2:
+---------+
| max(f1) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
MariaDB [jason]> insert into test (f1) values (4);
Query OK, 1 row affected (0.00 sec)
MariaDB [jason]> commit;
Query OK, 0 rows affected (0.01 sec)
窗口2 的等待可能会超时,报错:Lock wait timeout exceeded;
我们再来看看
MariaDB [jason]> select * from test;
+------+------+
| f1 | f2 |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
+------+------+
4 rows in set (0.00 sec)
共享读锁:lock in share mode 行级锁
如果一个事务正在修改f1 的值,而另一个窗口想读取有关f1 的最新值,可以用共享读锁
窗口1:
MariaDB [jason]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [jason]> select max(f1) from test;
+---------+
| max(f1) |
+---------+
| 4 |
+---------+
1 row in set (0.00 sec)
MariaDB [jason]> insert into test (f1) values(5);
Query OK, 1 row affected (0.00 sec)
窗口2;
MariaDB [jason]> select max(f1) ,f2 from test lock in share mode;
上面语句会阻塞直到 窗口1 commit;同样会超时跑异常
表级锁
读锁
只能对表进行读操作,不能进行写操作,写操作被锁
窗口1:申请test 表的读锁,在窗口1 中可以读,写test
窗口2:允许读test,但是写操作会阻塞,直到窗口1 unlock
![](《一起学mysql》2_files/Image.png)
写锁
上锁的那段时间没有读或写操作
上锁的窗口可以执行插入和insert 操作,其他窗口执行的命令会遇到阻塞
![](《一起学mysql》2_files/Image [1].png)
lock table 不是事务安全的,在锁表之前会提交全部的活动事务,
在更新比较频繁的表中应该尽量避免表级锁,以避免拥堵
欢迎转载,不必注明出处