5.锁机制与数据库结构

 

1. 事务回滚（不能自动回滚）

人们对事务的解释如下：事务由作为一个单独单元的一个或多个SQL语句组成，如果其中一个语句不能完成，整个单元就会回滚（撤销），所有影响到的数据将返回到事务开始以前的状态。因而，只有事务中的所有语句都成功地执行才能说这个事务被成功地执行。

这句话本身没有什么问题，问题是我给理解错了，我测试中问题描述为如下: 　mysql事务中有两条insert语句，其中第二条语句是错误的，在运行完事务后，第一条仍然插进去了，代码如下。

//创建表：
CREATE TABLE `test_tab` (
`f1`  int(11) NOT NULL ,
`f2`  varchar(11)  DEFAULT NULL ,
PRIMARY KEY (`f1`)
)
ENGINE=InnoDB

//执行事务：
START TRANSACTION;
INSERT INTO test_tab VALUES    (1, '2');
INSERT INTO test_tab VALUES    (1, '3');
COMMIT;

（错误：这只是我一开始的认为） 一开始认为只要把事务写出来，最后用commit提交一下，数据库会自动判断这些语句是否全执行成功，如果成功则把所有的数据插入到数据库，如果有一条失败就自动回滚至原始状态！显然我认为错了。

[SQL]START TRANSACTION;
受影响的行: 0
时间: 0.000s

[SQL]
INSERT INTO test_tab VALUES (1, '2');
受影响的行: 1
时间: 0.001s

[SQL]
INSERT INTO test_tab VALUES (1, '3');
[Err] 1062 - Duplicate entry '1' for key 'PRIMARY'

我们看结果可以知道INSERT INTO test_tab VALUES (1, '3');这一句因为主键冲突运行失败，从而这一条下面的commit也没有执行。

需要注意的是：这时已经开启了一个事务，并且已经执行了一条正确的插入语句，虽然没有体现在数据库中，但如果以后在该连接中又执行了一条commit 或begin或start transaction（新开一个事务会将该链接中的其他未提交的事务提交，相当于commit！）你会发现已经将刚才的INSERT INTO test_tab VALUES (1, '2');写进了数据库。

所以事务的回滚不是这么理解的，正确的理解应该是，如果事务中所有sql语句执行正确则需要自己手动提交commit；否则有任何一条执行错误，需要自己提交一条rollback，这时会回滚所有操作，而不是commit会给你自动判断和回滚。

2. 事务日志文件应对服务器异常终止情况

事务:ACID

服务器挂了，但是还没有 commit,会自动回滚 ？

1. 事务在操作过程中没有进行commit,但是服务器异常终止

如果服务器重启会执行 undo log: 把数据还原为初始状态

1. 务在操作过程中进行commit,但是还没来得及把数据写入到磁盘中 服务器异常终止

 

 

innodb_flush_log_at_trx_commit 设置事务commit之后的日志文件的写入时间和数据写入时间,数值不同可能影响的数据情况不一样

 

3. innodb行锁与表锁的转变与注意

InnoDB 行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁；否则，InnoDB使用表锁

在不通过索引（主键）条件查询的时候，InnoDB是表锁而不是行锁。

通常begin-end用于定义一组语句块

InnoDB表锁定机制的列子（讲之前去掉prefix的主键设置）

session1	session2
查看数据表结构
begin(begin-end用于定义一组语句块)查询数据
查询数据并加上锁机制(排他锁?什么是排他锁)
| 查询数据
|
commit	这个时候就可以显示数据
|

总结：就是在没有使用索引的情况下InnoDB就会使用表级锁（共享锁不会有这个情况）

InnoDB行锁定机制的列子（主键是一个特殊的索引）

session1	session2
给count中的prefix设置主键
再次测试
begin
|
可以看到目前锁只针对于d1d这条数据有效
|
事务演示
开启事务-新增一条数据
|
commit;

注意：如果查询的条件没有带索引，那么行锁则会转为表锁即时表中字段有主键；所以在查询的时候建议使用索引字段查询

4. innodb间隙锁

可以理解为是对于一定范围内的数据进行锁定，如果说这个区间没有这条数据的话也是会锁住的；主要是解决幻读的问题,如果没有添加间隙锁，如果其他事物中添加id在1到100之间的某条记录，此时会发生幻读；另一方面，视为了满足其恢复和赋值的需求。

MySQL官网间隙锁的使用 https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks

MySQL官网间隙锁属性解释 https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog

默认情况下，innodb_locks_unsafe_for_binlog是0(禁用)，这意味着启用了间隙锁定：InnoDB使用下一个键锁进行搜索和索引扫描。若要启用该变量，请将其设置为1。这将导致禁用间隙锁定：InnoDB只使用索引记录锁进行搜索和索引扫描。

create table y (
    id int primary key ,
    a int
);
insert into y values(1,1);
insert into y values(2,2);
insert into y values(3,3);
insert into y values(4,4);
insert into y values(5,5);
insert into y values(10,5);
insert into y values(11,5);
insert into y values(12,5);
insert into y values(13,5);

session1	session2
START TRANSACTION	START TRANSACTION
UPDATE y SET a = 4 WHERE id between 4 and 11;
现在就已经锁住了4-11的数据
| 现在先修改小与id为4的数据
| UPDATE y SET a = 4 WHERE id = 1;
| 添加成功，来一个超过11的
| UPDATE y SET a = 4 WHERE id = 13;
| 测试在区间内的数据
| UPDATE y SET a = 5 WHERE id = 5;
| 这个时候锁等待
释放
ROLLBACK;	执行成功
| ROLLBACK;

5. MySQL对于死锁的处理方式

5.1 为什么会死锁

官方定义如下：两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。

 

MySQL有两种死锁处理方式：

• 等待，直到超时（innodb_lock_wait_timeout=50s）。
• 发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）。

由于性能原因，一般都是使用死锁检测来进行处理死锁。

死锁检测

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

回滚

检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

5.2 对于锁与事务的建议

5.2.1 对于锁的建议

收集死锁信息：
  利用命令 SHOW ENGINE INNODB STATUS查看死锁原因。
  调试阶段开启 innodb_print_all_deadlocks，收集所有死锁日志。

减少死锁：
  使用事务，不使用 lock tables 。
  保证没有长事务。
  操作完之后立即提交事务，特别是在交互式命令行中。
  如果在用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)，尝试降低隔离级别。
  修改多个表或者多个行的时候，将修改的顺序保持一致。
  创建索引，可以使创建的锁更少。
  最好不要用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)。
  如果上述都无法解决问题，那么尝试使用 lock tables t1, t2, t3 锁多张表

5.2.2 对于事务的建议

innodb存储引擎由于实现了行几所，颗粒更小，实现更复杂。但是innodb行锁在并发性能上远远要高于表锁页锁。在使用方面可以尽量做到以下几点；

1. 控制事务大小，减少锁定的资源量和锁定时间长度。
2. 人所有的数据检索都通过索引来完成，从而避免因为无法通过索引加锁而升级为表锁。
3. 减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的数据。
4. 在业务条件允许下，尽量使用较低隔离级别的事务隔离。减少隔离级别带来的附加成本。
5. 河里使用索引，让innodb在索引上面加锁的时候更加准确。
6. 在应用中尽可能做到访问的顺序执行
7. 如果容易死锁，就可以考虑使用表锁来减少死锁的概率

6. MySQL基础结构

简单版的结构 

MySQL = 客户端 + 服务端
    客户端 = Connection(语言连接器例如：PHP-pdo，MySQLi)
    服务端 = SQL层 + 存储引擎层
        SQL层 = 链接/线程处理 + 查询缓存 + 分析器 + 优化器
        存储引擎 = InnoDB + MariaDB + .....

Connection：这一块其实主要是其他语言的连接，并不属于MySQL本身；主要是其他语言对于MySQL的连接操作的工具比如PHP中的：pdo，mysqli或者Navicat for MySQL

SQL层：功能主要包括权限判断，SQL解析功能和查询缓存处理等。

  1. 链接/线程处理：客户端通过 连接/线程层 来连接MySQL数据库，连接/线程层主要用来处理客户端的请求、身份验证和数据库安全性验证等。

  2. 查询缓存和查询分析器是SQL层的核心部分，其中主要涉及查询的解析、优化、缓存、以及所有内置的函数，存储过程，触发器，视图等功能。

  3. 优化器主要负责存储和获取所有存储在MySQL中的数据。