常见知识整理-mysql 锁，隔离级别

最新推荐文章于 2024-04-01 12:24:55 发布

brucelwl

最新推荐文章于 2024-04-01 12:24:55 发布

阅读量174

点赞数 1

分类专栏：其它分类文章标签： mysql 数据库

本文链接：https://blog.csdn.net/u013202238/article/details/114019648

版权

其它分类专栏收录该内容

11 篇文章 0 订阅

订阅专栏

MyISAM与InnoDB关于锁方面的区别是什么

MyISAM使用的是表级锁，不支持行级锁
InnoDB支持行级锁，也支持表级锁

因为表级锁是锁住整张表，所以索引和锁没有关系。
但如果是InnoDB，InnoDB中的行级锁是基于索引实现，而不是数据行。所以只有SQL涉及到索引时，才会使用行级锁，否则是表级锁（读锁或写锁）。
MyISAM，InnoDB中默认读锁都是共享锁，写锁是排它锁（独占锁）。

对于MyISAM

当数据被select时，会自动添加上共享读锁，此时其它连接对表做更新时(insert，update，delete)，需要被阻塞等待，但是可以进行读。
当数据被更新时，会自动添加上排它写锁，此时其它连接对表的curd操作都需要阻塞等待.

手动添加表锁(读锁/写锁)

lock tables person_info_myisam read | write;

手动释放表锁

unlock tables;

查询表级锁争用情况
如果Table_locks_waited比较高，那么说明存在严重的表级锁争用情况

 SHOW STATUS LIKE'table%';

将共享的读锁，变成独占锁

select * from userinfo where id = 123 for update

对于InnoDB

InnoDB用的是二段锁： 即加锁和解锁分成两个步骤，先对同一个事务里的一批操作分别加锁，commit时在对事务里加上的锁进行统一的解锁。

关闭InnoDB事务自动提交
该设置仅针对当前连接生效

SHOW VARIABLES LIKE 'autocommit'  #查看事务开启状态
SET autocommit = 0;               #关闭事务自动提交

InnoDB添加读共享锁
select操作默认没有加锁，添加读锁之后，如果没有COMMIT;则，其它连接对同一行无法创建独占锁（例如update，delete操作），

InnoDB中该语句并没有上锁，此时其它连接可以对user_id =12的数据执行更新操作。（注：非阻塞select）
user_id 是索引

SELECT * FROM order_info WHERE user_id = 12

如下语句用于显式添加 共享读锁，其它连接无法对user_id =12的数据执行更新操作（无法创建排它锁），但是可以对user_id不为12的数据做更新操作.

SELECT * FROM order_info WHERE user_id = 12 LOCK IN SHARE MODE;

原因： user_id 是索引，此时使用的是行级锁. 索引值不同，互不影响。

查询行级锁争用情况查询

show status like 'innodb_row_lock%';

关于InnoDB锁总结

在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

数据库锁的分类

按锁的粒度划分：表级锁，行级锁，页级锁（bdb引擎支持）
按锁的级别划分：共享锁，排它锁
按锁的方式划分：自动锁，显示锁
按操作划分：分为DML（数据查询语言）锁，DDL（数据定义语言）锁
按使用方式划分：分为乐观锁（代码实现version字段），悲观锁（数据库锁机制实现）

MyISAM适合的场景

频繁执行全表count语句（MyISAM内部通过一个变量保存总数，而InnoDB每次都需要扫表统计）
对数据进行增、删、改的频率不高，查询非常频繁
没有事务

InnoDB适合的场景

数据CRUD都相当频繁
可靠性要求比较高，要求支持事务

数据库事务的四大特性

ACID
原子性（Atomic）：事务包含的所有操作，要么全执行，要么全不执行。
一致性（Consistency）：事务一致性
隔离性（Isolation）
持久性（Durability）：数据库持久化

事务隔离级别以及各级别下的并发访问问题

隔离级别从低到高如下：

read uncommitted(未提交读)
read committed(提交读)
repeatable read(重复读)
serializable(串行读)

查看mysql事务隔离级别：

SELECT @@transaction_isolation  #默认 REPEATABLE-READ

修改事务隔离级别
这里修改为: read uncommitted

set session transaction isolation level read uncommitted

更新丢失： 即一个事务的更新，覆盖了另一个事务的更新。
mysql所有事务隔离级别在数据库层面上均可避免
脏读： 在read uncommitted级别下，A更新数据1，但未提交，此时B读取了A未提交事务的数据，但可能A回滚事务数据实际并未更新，导致B读取了脏数据，出现了脏读。
Read-Committed 事务隔离级别以上可避免
不可重复读： 即同一个事务中两次读取的通过一条数据结果不一样，
Repeatable-read 事务隔离级别以上可避免
幻读： A查询出3条数据，更新时却更新了4条，可能B在这期间新增了一条。serializable事务隔离级别可避免，所有的sql都会加锁。

事务隔离级别	更新丢失	脏读	不可重复读	幻读
read uncommitted	避免	发生	发生	发生
read committed	避免	避免	发生	发生
repeatable read	避免	避免	避免	发生
serializable	避免	避免	避免	避免

InnoDB可重复读(RR)隔离级别下如何避免幻读

当前读：select...lock in share mode，select...for update，update，delete，insert
就是读取或更新的都是最新的数据
快照读：不加锁的非阻塞读，默认select ,(非serializable隔离级别下，serializable隔离级别本身就是串行，该模式下快照读退化成当前读)，快照读的实现是基于多版本并发控制即MVCC，MVCC可以认为是行级锁的变种，性能高于行级锁，但读取的可能并不是数据的最新版本。