《一起学mysql》2

佚名 6年前 (2019-05-05) 随笔 376人围观抢沙发百度已收录

事务爸妈让往他们银行卡里转点儿钱，可是我这钱全在支付宝里，爸妈又没有支付宝，只能从支付宝里转给他们了，假如转账过程中，支付宝扣款成功了，但是银行系统崩溃了，钱没转进去，这咋整？我的大洋就这样打水漂了？肯定不可以，为了避免这种情况的发生，就用到了事务，在转钱过程中遇到了任何差错，就回到没转钱之前的状态，这个就叫做事务事务四大特性（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