mysql进阶

 事务

 要么都成功，要么都失败

ACID

原子，一致，持久，隔离

原子性，一致性，隔离性，持久性

原子性： 要么都成功，要么都失败回滚

一致性: 事务前后的数据完整性要保证一致，从一个一致性状态转换为另一个一致性状态

持久性：事务一但提交则不可逆，被持久化到数据库

隔离性: 多个用户并发访问数据库时，数据库为每个用户开启一个事务，不能被其他事务的操作所干扰，事务要相互隔离，一个事务在提交之前，对其他事务是不可见的

 

# 开启事务
START TRANSACTION;
# A 的余额 - 200
UPDATE money SET balance = balance - 200.00 WHERE name = 'A';
# B 的余额 + 200
UPDATE money SET balance = balance + 200.00 WHERE name = 'B';
#提交事务
COMMIT;

• 原子性：要么完全提交（A余额减少200，B 的余额增加200），要么完全回滚（两个人的余额都不发生变化）
• 一致性：这个例子的一致性体现在 200元不会因为数据库系统运行到第3行之后，第4行之前时崩溃而不翼而飞，因为事务还没有提交。
• 隔离性：允许在一个事务中的操作语句会与其他事务的语句隔离开，比如事务A运行到第3行之后，第4行之前，此时事务B去查询A余额时，它不能看到在事务A中被减去的200元（账户钱不变），因为事务A和B是彼此隔离的。在事务A提交之前，事务B观察不到数据的改变。
• 持久性：事务一旦提交，不能更改

隔离失败产生的问题

脏读，幻读,不可重复读，更新丢失

 

脏读：读取到了其他事务未提交的数据，导致产生了脏数据

不可重复读：在一个事务中，多次查询的数据结果不同

幻读:在一个事务中，根据同一个条件查询得到的数据个数不同

更新丢失
丢失更新就是两个不同的事务（或者Java程序线程）在某一时刻对同一数据进行读取后，先后进行修改。导致第一次操作数据丢失。

第一类丢失更新 ：A，B 事务同时操作同一数据，A先对改数据进行了更改，B再次更改时失败然后回滚，把A更新的数据也回滚了。（事务撤销造成的撤销丢失）

第二类丢失更新：A，B 事务同时操作同一数据，A先对改数据进行了更改，B再次更改并且提交，把A提交的数据给覆盖了。（事务提交造成的覆盖丢失）

不可重复读和幻读的区别

不可重复读的重点是修改，幻读的重点在于新增或者删除。

怎么解决

解决方法：加锁（不建议使用，锁的粒度大），隔离级别，MVCC

加锁

1.脏读：在修改的时候添加排他锁，知道事务提交才释放，读取的时候共享锁，读完释放锁

2.不可重复读：读数据时加共享锁，写数据时加排他锁

3.幻读:加范围锁（间隙锁和NEXT-KEY锁）

隔离级别

有五种隔离级别：NONE（不使用事务），读未提交，读已提交，可重复读，串行化

读未提交（Read Uncommitted）：最低的隔离级别，允许读取尚未提交的数据变更， 允许脏读、幻读或不可重复读。

读已提交（Read Committed）：一个事务只能看到已经提交的事务所造成的改变，防止脏读，允许幻读和不可重复读

可重复读（Repeatable Read）: 同一个事务的多个实例在并发读取数据时，会看到同样的数据行，mysql默认，防止脏读，不可重复读，允许幻读

串行化 (SERIALIZABLE): 最高的隔离级别，不允许读-写，写-读 的并发操作 (读-读可以)，防止脏读，幻读，不可重复读

隔离级别越高，安全性越高，但是事务的并发性能越低。不建议在数据库中添加大量事务，将事务交给应用程序来控制

扩展

MySql -------- 可重复读   Oracle --------- 读已提交

InnoDB 存储引擎在 分布式事务 的情况下一般会用到(可串行化)隔离级别。

why

Mysql主从复制，是基于binlog复制的;而binlog是一个记录数据库更改日志文件. binlog有三种格式，分别是 statement:记录的是修改SQL语句 row：记录的是每行实际数据的变更 mixed：statement和row模式的混合

Mysql5.0版本以前，binlog只支持statement这种格式！而这种格式在读已提交(Read Commited)隔离级别下，主从复制是有bug的，因此Mysql将可重复读作为默认的隔离级别。

事务传播属性

1) required（默认属性） 如果存在一个事务，则支持当前事务。如果没有事务则开启一个新的事务。 被设置成这个级别时，会为每一个被调用的方法创建一个逻辑事务域。如果前面的方法已经创建了事务，那么后面的方法支持当前的事务，如果当前没有事务会重新建立事务。

2) Mandatory 支持当前事务，如果当前没有事务，就抛出异常。

3) Never 以非事务方式执行，如果当前存在事务，则抛出异常。

4) Not_supports 以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。

5) requires_new 新建事务，如果当前存在事务，把当前事务挂起。

6) Supports 支持当前事务，如果当前没有事务，就以非事务方式执行。

7) Nested 支持当前事务，新增Savepoint点，与当前事务同步提交或回滚。 嵌套事务一个非常重要的概念就是内层事务依赖于外层事务。外层事务失败时，会回滚内层事务所做的动作。而内层事务操作失败并不会引起外层事务的回滚。

添加事务

MySQL中事务隐式开启的,也就是说,一个sql语句就是一个事务,当sql语句执行完毕,事务就提交了。

C:\Users> mysql -uroot -p   #进入数据库 

Enter password: ****** #输入密码
# 查看是否开启了自动提交     autocommit为ON 表示开启了自动提交。
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.05 sec)
mysql>

 

开启事务	START TRANSACTION   或者 begin
提交	commit；
回滚	ROLLBACK；
设计标记点	SAVEPOINT point_name;
回滚到标记点	ROLLBACK TO point_name；

 

# mysql中是默认开启事务自动提交的
SET autocommit = 0；#关闭

SET autocommit = 1；#开启（默认）

#手动处理事务

-- 1.关闭自动提交 
SET autocommit = 0；

-- 开启一个事务  START TRANSACTION   或者 begin
START TRANSACTION  

　　#sql操作
　　update count set money = money -100 where name ='A';   -- A-100
　　
　　SAVEPOINT p;  -- 回滚标记

　　update count set money = money +100 where name ='B'; -- B+100

commit； --提交事务
ROLLBACK ； -- 回滚

ROLLBACK TO p； -- 回滚到标记点

SET autocommit = 1； -- 恢复默认值，否则后面的sql都无法提交成功

转载自：SQL事务（非常详细） (biancheng.net)

例如，有包含如下记录的 CUSTOMERS 表：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+

打开一个 MySQL 命令行窗口（我们称它为 A 窗口），使用事务向表中插入两条数据：

mysql> BEGIN;
mysql> INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES ('Chaitali', 25, 'Mumbai', 6500.00 );
mysql> INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES ('Hardik', 27, 'Bhopal', 8500.00 );

再打开另外一个 MYSQL 命令行窗口（我们称它为 B 窗口），使用 SELECT 命令查看 CUSTOMERS 表的内容：

mysql>  SELECT * FROM CUSTOMERS;
+----+---------+-----+-----------+--------+
| id | name    | age | address   | salary |
+----+---------+-----+-----------+--------+
|  1 | Ramesh  |  32 | Ahmedabad |   2000 |
|  2 | Khilan  |  25 | Delhi     |   1500 |
|  3 | Kaushik |  23 | Kota      |   2000 |
+----+---------+-----+-----------+--------+

您看，A 窗口对表的修改并没有影响到 B 窗口，B 窗口只能查看到修改之前的内容，这说明 A 窗口的修改动作并没有立即更新到物理数据库，所以在其它会话窗口中无法看到。

在 A 窗口中提交事务：

mysql> COMMIT;

在 B 窗口中再次查看 CUSTOMERS 表的内容：

mysql>  SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

您看，在 A 窗口提交事务以后，对表所做的修改才真正更新到物理数据库中，所以才能在其它会话窗口中查看到 A 窗口中插入的数据。

回滚事务

回滚意味着撤销尚未保存到物理数据库中的操作，具体语法格式如下：

ROLLBACK;

事务执行过程中如果发生了某种故障，事务不能继续执行，就可以撤销事务，此时对数据库的修改并不会保存到物理数据库中。撤销意味着事务结束，并且执行失败。

例如，有包含如下记录的 CUSTOMERS 表：

+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

使用事务删除最后两个用户，并回滚：

mysql> BEGIN;
mysql> DELETE FROM CUSTOMERS WHERE ID=4;
mysql> DELETE FROM CUSTOMERS WHERE ID=5;
mysql> ROLLBACK;

mysql> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

您看，回滚事务以后，物理数据库中的数据并没有发生改变，表中的内容和事务执行之前的内容一致。

回滚标记点

ROLLBACK 命令默认回滚整个事务，也即事务中的所有修改操作都无效。但是 SQL 允许回滚事务的一部分，此时需要在事务中设置一个标记点，在该标记点之后的 SQL 语句将被回滚，之前的 SQL 语句将被成功执行。

设置标记点使用 SAVEPOINT 命令，具体语法如下：

SAVEPOINT point_name;    #point_name 为标记点名字。

回滚到标记点使用 ROLLBACK TO 命令，具体语法如下：

ROLLBACK TO point_name;

例如，有包含如下记录的 CUSTOMERS 表：

+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

使用事务删除最后两个用户，并回滚到标记点：

mysql> BEGIN;
mysql> DELETE FROM CUSTOMERS WHERE ID=4;
mysql> SAVEPOINT sp;
mysql> DELETE FROM CUSTOMERS WHERE ID=5;
mysql> ROLLBACK TO sp;

mysql> SELECT * FROM CUSTOMERS;
+----+---------+-----+-----------+--------+
| id | name    | age | address   | salary |
+----+---------+-----+-----------+--------+
|  1 | Ramesh  |  32 | Ahmedabad |   2000 |
|  2 | Khilan  |  25 | Delhi     |   1500 |
|  3 | Kaushik |  23 | Kota      |   2000 |
|  5 | Hardik  |  27 | Bhopal    |   8500 |
+----+---------+-----+-----------+--------+

您看，我们回滚到标记点 sp，只有 ID 为 4 的用户被删除，ID 为 5 的用户依然留在数据库中。

锁机制

 https://b23.tv/2qtHB6c

粒度区分

• 行锁：加锁粒度小，加锁资源开销大，InnoDB才支持，InnoDB的行锁是针对索引加的锁，不通过索引条件检索的数据，升级为表锁
  • 共享锁：读锁。多个一个事务可以对同一数据共享同一把锁，持有锁的事务都可以访问数据，但是只能读不能修改
    • select  ....  lock in share mode;
  • 排他锁：写锁。只有一个事务可以对其读写，其他事务只能等该事务释放，不能对其加任何锁，InnoDB会对 insert，update，delect语句自动添加排他锁
    • select  .....   for update;
    • 不允许事务以select ...... for update 和     select .... lock in share mode 的方式读取事务，但是可以以 select .. from.. 形势查询数据，普通查询没有索引
  • 自增锁：通常针对于Mysql当中的自增字段，如果有事务回滚的情况，那么数据回滚，自增序列不会回滚。
• 表锁：加锁粒度大，加锁资源开销小，MyISAM和InnoDB都支持
  • 表共享读锁：多个事务对同一张表进行访问，持有锁的事务乐可以同时访问表数据，但不可以修改
  • 表排他写锁：只有一个事务可以得到排他锁，操作表数据，其他的事务要等所释放。
  • 意向锁：不与行锁冲突。是InnoDB自动添加的一种锁，不需要用户维护，解决表锁与之前可能存在的行锁冲突 
• 全局锁：对整个数据库加锁  Flush tables with read lock，加锁之后整个数据库实例都处于只读状态，所有的数据变更操作都会被挂起，一般用于全库备份的时候。

乐观锁和悲观锁是人们提出的一种概念，想法，并不是mysql的锁机制

乐观锁：就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。

• 乐观锁的实现不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本，一种是通过版本号，一种是通过时间戳。
• 给表加一个版本号或时间戳的字段，读取数据时，将版本号一同读出，数据更新时，将版本号加1。
• 当我们提交数据更新时，判断当前的版本号与第一次读取出来的版本号是否相等。如果相等，则予以更新，否则认为数据过期，拒绝更新，让用户重新操作。
•  乐观锁是基于程序实现的，所以不存在死锁的情况，适用于读多的应用场景。如果经常发生冲突，上层应用不断的让用户进行重新操作，这反而降低了性能，这种情况下悲观锁就比较适用。

• #第一步：查询要卖的商品，并获取版本号。
  begin;
  select nums, version from tb_goods_stock where goods_id = {$goods_id};

  # 如果满足条件，则减少库存。(更新时判断当前version与第1步中获取的version是否相同)
  update tb_goods_stock set nums = nums - {$num}, version = version + 1 where goods_id = {$goods_id} and version = {$version} and nums >= {$num};
  # 判断更新操作是否成功执行，如果成功，则提交，否则就回滚。

   

悲观锁：就是对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据。所以在整个数据处理过程中，需要将数据锁定。（行锁和表锁都属于悲观锁）

锁算法

常见的锁算法   user.id(1,4,9)   update user set xxx where id =4;

记录锁（Record Lock）：锁一条具体的数据 　　

　　　 精准条件命中，并且命中的条件字段是唯一索引

间隙锁（Gap Lock）：RR隔离级别下（可重复读），会加间隙锁，锁一定的范围，而不是具体的值，防止幻读

  遵循左开右闭原则，范围查询且记录不存在，查询条件必须命中索引，(负无穷-1],(1-4],(4-9],(9-正无穷)

NEXT- KEY：间隙锁+记录锁，InnoDB行锁默认算法，锁定的是具体的数值，范围查询内的所有空隙，相邻的下一区间。

 

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

1. 原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
2. 原则 2：查找过程中访问到的对象才会加锁。
3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

前提：可重复读隔离级别，id主键索引

select * from   table where  id  = 5  for update；  值存在，使用的是记录锁  Record Lock

select * from   table where   id  = 11  for update； 

1. 值不存在，需要加 next-key lock，id = 11 所属区间为 (10,15] 的前开后闭区间；
2. 因为是等值查询，不需要锁 id = 15 那条记录，next-key lock 会退化为间隙锁；最终区间为 (10,15) 的前开后开区间；
3. insert  id = 12的数据失败，因为使用的是间隙锁，锁的范围（5,10），但是update  id=10是可以的

select * from   table where   id  ≥ 10  and  id <11 for update； 

1. 开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
2. 范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。
3. insert id=8 true，insert id =13 true，update id=15 false

死锁

死锁是指的两个或者两个以上的事务在执行过程中，因为争夺锁资源而造成的一种互相等待的现象。若沒有外力推动，他们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁

产生死锁的必要条件：

互斥条件：进程要求对所分配的资源进行排它性控制，即在一段时间内某资源仅为一进程所占用。
请求和保持条件：当进程因请求资源而阻塞时，对已获得的资源保持不放。
不剥夺条件：进程已获得的资源在未使用完之前，不能剥夺，只能在使用完时由自己释放。
环路等待条件：在发生死锁时，必然存在一个进程--资源的环形链。

如何查看死锁

使用命令 show engine innodb status 查看最近一次死锁

InnoDB Lock Monitor 打开锁监控，每15秒输出一次日志。使用完毕之后建议关闭，否则会影响数据库性能

两种策略

通过 innodb_lock_wait_timeout 来设置超时时间，一直等待直到超时,默认是50s，超时自动释放锁回滚事务

发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其他事务继续执行