MySQL之并发控制、锁、事务

759876uyt

连接管理器：监听在某个套接字上，接收请求、创建线程、做用户认证，建立安全的会话连接
线程池：用来缓存MySQL的线程，实现线程复用，提高MySQL的性能
缓存器：里面有许多缓存槽，一个缓存槽中可以缓存多个数据，缓存那些值不经常变化的数据，供下次相同请求从缓存中直接响应，加速MySQL处理性能。但缓存也有缺点，如想要的数据不管有没有在缓存槽中缓存，连接管理器和解析器都要每次去缓存中看一看，会加大开销。并且不是所有数据都能缓存，如经常变化的数据（如时间）。

并发控制：任何时候当出现两个及以上的人同时访问某一个数据时，都会带来并发控制的问题，要做到让以个人的操作不影响到其他人的操作，这流需要并发控制

MVCC（多版本并发控制）：每个人在对数据进行操作时，操作的并不是原始数据，而是其副本或者快照，操作完成后再将多个快照进行合并（一般在某个时间值上创建快照，并按照时间点合并快照，且以后一个快照为准）

锁：

• 读锁：也叫做共享锁（读的时候允许让多人同时读，但任何时候都不允许任何人写）
  
• 写锁：也叫做独占锁（读的时候允许其他人读，但不允许写；而写的时候即不能让其他人写，也不能让其他人读）
  
  

mysql> lock tables test1 read|write; 加读|写锁
mysql> unlock tables; 解锁

锁粒度（从大到小）

• 表锁 锁一个页面
  
• 页锁 锁一个数据块，一个数据块中存储着多个行
  
• 行锁 锁一个行（实体）
  
  
  

注：MySQL服务器只支持表锁，行锁要由存储引擎来完成

会话1 mysql> lock tables test1 read; 加读锁
会话2 mysql> select * from test1; 可以查看
+-----+---------+-----+
| cid | name    | sid |
+-----+---------+-----+
|   1 | zxl     | A   |
|   2 | jiamian | B   |
|   3 | fade    | C   |
+-----+---------+-----+
3 rows in set (0.03 sec)

会话2 mysql> insert into test1 (cid,name,sid) values (4,'faded','D');插不进去
会话1 mysql> unlock tables; 解锁
会话2 mysql> insert into test1 (cid,name,sid) values (4,'faded','D');
    Query OK, 1 row affected (2 min 13.83 sec) 现在才插进去了


事务：一个RDBMS（关系数据库管理系统）要支持事务，必须满足四个要求（ACID）
原子性：用户转账 A-500，B+500 要么A和B都完成，要么都不完成；事务引起的数据操作要么完成，要      么不完成
一致性：用户转账 A-500，则B必须加500，及完成之前和完成之后事务要一致；事务状态平稳过度
持久性：一个事务一旦完成，就算server崩溃了，下次启动后这个完成的事务也必须有效
隔离型：一个事务的中间操作不能影响另一个事务的执行

如果一个事务的完成要经过4步，但执行到第三步时server崩溃了，则必须对执行的前3步进行撤销，并对数据进行还原，但具体是如何实现的？
答：依靠日志

事务的日志分为两种

• 重做日志 每次操作都要写入重做日志中，server崩溃后，重启后会根据重做日志中的记录重新执行一遍操作
  
• 撤销日志 每次执行操作时都要将原来的状态保存下来，server崩溃后，可以按照撤销日志中记录的原状态还原回去
  
  

关于日志的记录，一般日志只是记录了操作，而不是数据本身，所以执行起来非常快

事务一旦提交，不管数据有没有同步到磁盘，都表示事务已经完成；而一般来说所有的操作都先是在内存中执行一遍后，再斜土日志中，最后才写入磁盘的数据存储文件中（如innodb的.ibd数据文件）

如果事务已近提交，但是并未同步到磁盘是，server崩溃了，这时在sever重启时，如果日志中已近有记录如何操作，则会根据日志进行数据修复，将数据写入磁盘上（如果数据量很大的话，修复起来很慢，你若在中间ctrl+c，则很可能导致数据崩溃或者错乱），如果操作未记录入日志中，则会执行撤销操作

事务的隔离级别，下面的隔离级别按照从高到低排序（即并发能力从大到小排序）
read uncommitted（读未提交）：只要别人对数据进行了修改，我立马就能看到
read committed(读提交）：只有当别人将事务提交了，我才能看到修改
repatable read（可重读）：mysql默认的，不管别人有没有提交，只有当我的事务提交了才能看到修                  改）
seriablizable（串行）：别人完成事务后，我才能对数据进行操作


mysql> show global variables like '%iso%'; 查看当前MySQL的隔离级别
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.08 sec)
mysql> set session 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)

事务的状态有以下几种：

• 正在执行语句
  
• 部分提交（事务已进执行完毕，但最后一条语句还处于往磁盘写入的状态）
  
• 失败的（事务已经完成，但是提交失败）
  
• 中止的（事务没有完成就中止）
  
• 已提交（事务已经成功提交）
  
  

注：事务一旦提交就无法撤销

只有从第一个语句到最后一个语句写入磁盘，才算事务提交且执行成功

事务：并发执行

• 提高吞吐量和资源利用
  
• 减少等待时间
  
  
  

事务调度

• 可恢复调度：任何一个事务进行调度时，如果出现交叉执行，都不会对另一个事务的调度产生影响
  
• 无级连：避免事务撤销时彼此之间影响
  
  

并发控制依赖的技术手段：

• 锁
  
• 时间戳
  
• MVCC和快照隔离
  
  

例：

mysql> start transaction; 启动事务

mysql> delete from test1 where name='jiamian';
Query OK, 1 row affected (0.26 sec)

mysql> select * from test1;
+-----+-------+-----+
| cid | name  | sid |
+-----+-------+-----+
|   1 | zxl   | A   |
|   3 | fade  | C   |
|   4 | faded | D   |
+-----+-------+-----+
3 rows in set (0.00 sec)

mysql> ROLLBACK; 回滚，撤销
Query OK, 0 rows affected (0.02 sec)

mysql> select * from test1;
+-----+---------+-----+
| cid | name    | sid |
+-----+---------+-----+
|   1 | zxl     | A   |
|   2 | jiamian | B   |
|   3 | fade    | C   |
|   4 | faded   | D   |
+-----+---------+-----+
4 rows in set (0.00 sec)

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

mysql> select @@autocommit;
+--------------+   
| @@autocommit |
+--------------+
|    1     |
+--------------+
1 row in set (0.00 sec)

autocommit是否开启了事务自动提交 1表示开启，如果不是每次明确启动事务（start transaction），则每执行一个操作都自动提交

建议：明确使用事务，并且关闭自动提交

>set session autocommit=0;

MySQL支持事务的保存点，如一个事务的执行有100个操作，当执行到75个时出错了，可以撤销到保存点的位置（如每10个操作一个保存点），则可以撤销到第70个保存点，而不必从第一个操作重新执行

>start transaction;
>delete from tutors where tid=18;
>savepoint num1; 设定保存点num1
>delete from tutors where tid=14;
>savepoint num2;
>delete from tutors where tid=12;
>savepoint num3;
>select * from tutors;
>rollback to num3;
>selete * from tutors;
这是你会发现tid=12的还原了
>commit;