一、MySQL存储引擎：
| 对比项 | MyISAM | innoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁。操作一条记录也会锁住整个表 | 行锁。操作时只锁某一行 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引，还缓存真实数据 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
| 默认安装 | Y | Y |
-
-- mysql现在已提供什么存储引擎: show engines; -- mysql查询当前默认的存储引擎 show variables like '%storage_engine%'; -- 要看某个表用了什么引擎，ENGINE=InnoDB show create table 表名; -- 查看当前的数据库使用的事务隔离级别 show variables like 'tx_isolation'
二、MyISAM引擎下的锁
-
锁的分类
1.1 从数据操作的类型分：读锁和写锁；
读锁（共享锁）：只对同一份数据，多个操作可以同时共享而不相互影响
写锁（排它锁）：当前写操作没有完成前，他会阻断其他写锁和读锁
1.2 从数据操作的粒度分：表锁和行锁；
-
表锁
特点：偏向于MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低；
查看锁状态、加锁、解锁：
-- 查看表上加过的锁，1：表示加锁，0：表示未加锁
show open tables;
-- 手动增加表锁
lock table 表名 read(write), 表名2 read(write),其他;
-- 解锁
unlock tables;-
表锁-读锁
对表加读锁，在两个session中进行对比如下：
| session1 | session2 |
|---|---|
| 给指定的表加读锁 lock table tb_member_account read；
| 连接终端； use 指定的数据路名；
|
| session1和session2都可以查看被上读锁的表 | |
| 不可更改被加读锁的表
| 更改被加读锁的表会一直阻塞，直到超时或者表被解锁以后才会更改成功
|
| 不可以增删查改加锁表以外的其他表
| 可以增删查改加锁表以外的其他表
|
-
表锁-写锁
对表加写锁，在两个session中进行对比如下：
| session1 | session2 |
|---|---|
| 给指定的表加读锁 lock table tb_member_account read；
| 待session1加写锁以后，session2在连接终端 |
| 当前session对锁定的表进行增、删、查、改都是可以的
| 其他的session对锁定进行增、删、查、改的时候，会产生阻塞
备注：如果产生不阻塞，请把where查询条件换一下，因为mysql有缓存，第二次查询会从缓存中取得，影响锁效果显示。 |
| 不可以增、删、查、改被锁定表以外的表
| 可以增、删、查、改被锁定表以外的表
|
-
MyISAM引擎下锁总结：
-
MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。MySQL的表级锁有两种模式:
1、表共享读锁
2、表独占写锁
| 锁类型 | 可否兼容 | 读锁 | 写锁 |
|---|---|---|---|
| 读锁 | 是 | 是 | 否 |
| 写锁 | 是 | 否 | 否 |
-
结论:读锁会阻塞写，但是不会阻塞读，写锁会阻塞读和写
1、对MyISAM表的读操作（加读锁），不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
2、MyISAM表的写操作（加写锁），会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
-
MyISAM不适合做写为主表的引擎，因为它的读写锁调度是写优先。因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞
-
分析表锁定
可以通过检查：table_locks_waited 和 table_locks_immediate状态变量来分析系统上的表锁定：
show status like 'table%';
table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每次立即获取锁其值就自动+1；
table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值+1），此值高则说明存在着较严重的表级锁争用情况（需要优化）；
三、INNoDB 引擎下的锁
-
行锁
-
偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，发送锁冲突的概率最低，并发度也最高，INNoDB和MyISAM最大不同有两点：一支持事务（Transaction），二是采用了行级锁
- 事务（Transaction）及其ACID属性：
1、事务是由一组SQL语句组成的逻辑单元，事务具有以下4个属性，通常简称为事务的ACID属性
属性一：原子性（Atomicity），事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行；
属性二：一致性（consistent），在事务开始和完成时，数据都必须保持一致状态，这就意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性，事务结束时，所有的内部数据结构（如B树索引等）也都必须是正确的；
属性三：隔离性（isolation），数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”执行环境，这意味着事务处理过程的中间状态对外部是不可见的，反之亦然。
属性四：持久性（Durable），事务完成之后，他对于数据的修改是永久性的，即使出现系统故障也能够保持。
-
并发事务处理带来的问题
1、更新丢失（Lost Update）：当两个或者多个事务选择同一行，然后根据最初选定的值更新该行时，由于每个事务都不知道其他事物的存在，就会放生更新丢失问题（最后的更新覆盖了其他事务所做的更新），如果在一个事务完成并提交事务之前，另一个事务无法访问同一条数据，则可以避免此类问题；
2、脏读（Dirty Reads）：一个事务正在对一条记录作修改，在这个事务完成之前，这条记录的数据就处于不一致的状态，这时另一个事务来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做了进一步的处理，就会产生以未提交的数据做依赖，这种现象就叫做“脏读”，例如：事务A读取了事务B已修改但是尚未提交的数据，事务A还在这个数据的基础上做了操作，此时如果B事务回滚，A读取的数据是无效的，不符合一致性要求。
3、不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了变化，或者某些记录已经被删除了，这种现象就叫做“不可重复读”，例如：事务A读取到了事务B已经提交到的修改数据，不符合隔离性。
4、幻读（Phantom Reads）：一个事务按照以前相同的条件重复查询以前已经检索过的数据，却发现其他事物插入了满足其检索条件的新数据，这种现象叫做“幻读”，例如：事务A读取到了事务B提交的新增数据，不符合隔离性；
幻读和脏读有点类似：脏读是事务B里面修改了数据，幻读是事务B里面新增了数据
-
事务的隔离级别
“脏读”，“不可重复读”，“幻读” 其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
| 隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 未提交读（Read uncommitted） | 最低级别，只保证不读取物理上损坏的数据 | 是 | 是 | 是 |
| 已提交读（Read committed） | 语句级 | 否 | 是 | 是 |
| 可重读读（Repeatable Read） | 事务级 | 否 | 否 | 是 |
| 可序列化（Serializable） | 最高级别，事务级 | 否 | 否 | 否 |
说明：数据库的隔离级别越高，并发副作用越小，但是付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然和“并发”是矛盾的，同时，不同的应用对读一致性和事务隔离程度的要求也是不同，因此需要按照情况设计隔离级别
查看当前数据库的事务隔离级别：
show variables like 'tx_isolation';
-
索引失效行锁变表锁
1、当选中某一行时，如果是通过主键或者索引选中的，这个时候是行级锁；
2、如果是通过其它条件(非索引列)选中的，这个时候行级锁会升级成表锁，其它事务无法对当前表进行更新或插入操作；
3、如果表中某一个索引是varchar型，当时在条件里面没有加单引号‘’，也会有行锁变成表锁，其它事务无法对当前表进行更新或插入操作；
--查询session的自动提交参数autocommit
show variables like 'autocommit';
--数据库默认是自动提交，关闭自动提交操作如下：
set autocommit = 0;
--查询一个表的索引
show index from 表名 ;
CREATE TABLE `user` (
`name` VARCHAR(32) DEFAULT NULL,
`count` INT(11) DEFAULT NULL,
`id` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
-- 这里，我们建一个user表，主键为id
-- A通过主键执行插入操作，但事务未提交
update user set count=10 where id=1;
-- B在此时也执行更新操作
update user set count=10 where id=2;
-- 由于是通过主键选中的，为行级锁，A和B操作的不是同一行，B执行的操作是可以执行的
-- A通过name执行插入操作，但事务未提交
update user set count=10 where name='tom';
-- B在此时也执行更新操作
update user set count=10 where id=2;
-- 由于是通过非主键或索引选中的，升级为为表级锁，B则无法对该表进行更新或插入操作，只有当A提交事务后，B才会成功执行-
间隙锁危害
当我们用范围条件而不是用相等条件检索/更新数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内并不存在的记录，叫做间隙（GAP），InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁（Next-key锁）；
危害： 因为Query执行过程中通过范围查找/更新的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在，从而造成在锁定的时候，无法插入锁定键值范围内的任何数据，在某种场景下很可能会对性能造成很大的危害
-- 用户A
update user set count=8 where id>2 and id<6
-- 用户B
update user set count=10 where id=5;
如果用户A在进行了上述操作后，事务还未提交，则B无法对2~6之间的记录进行更新或插入记录，会阻塞，当A将事务提交后，B的更新操作会执行。
-
如果锁定一行 for update
如果在一条select语句后加上for update，则查询到的数据会被加上一条排它锁，其它事务可以读取，但不能进行更新和插入操作
-- A用户对id=1的记录进行加锁
select * from user where id=1 for update;
-- B用户无法对该记录进行操作
update user set count=10 where id=1;
-- A用户commit以后则B用户可以对该记录进行操作适用范围：
1、A用户消费，service层先查询该用户的账户余额，若余额足够，则进行后续的扣款操作；这种情况查询的时候应该对该记录进行加锁
2、否则，B用户在A用户查询后消费前先一步将A用户账号上的钱转走，而此时A用户已经进行了用户余额是否足够的判断，则可能会出现余额已经不足但却扣款成功的情况
3、为了避免此情况，需要在A用户操作该记录的时候进行for update加锁
4、在数据库中操作的时候如下：
-
监控和分析行锁
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗，可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有明显的优势了。但是InnoDB的行级锁定也有其脆弱的一面，当我们使用不当的时候，可能会让其整体性能表现不能比MyISAM高，甚至可能会更差。
可以通过查看Innodb_row_lock状态变量来分析系统上的行锁争夺情况：
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time ：从数据库启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花费的平均时间长度；
Innodb_row_lock_time_max：从数据库启动到现在等待最长的一次所花费的时间；
Innodb_row_lock_waits：从数据库启动到现在总共等待的次数；
注意：尤其是当等待次数很高（Innodb_row_lock_waits），而且每次等待时长（Innodb_row_lock_time_avg）也不小的时候，我们就需要分析系统为什么会有这么多的等待，然后根据分析结果着手进行优化；
3.优化建议
-
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
-
合理设计索引，尽量缩小锁的范围
-
尽可能减少索引条件，避免间隙锁
-
尽量控制事务大小，减少锁定资源量和时间长度
-
尽可能低级别事务隔离
扫一扫
2171
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
