Mysql---锁

最新推荐文章于 2024-01-24 09:35:38 发布

SamXCode

最新推荐文章于 2024-01-24 09:35:38 发布

阅读量558

点赞数

分类专栏： MySQL 文章标签：锁 Mysql 调优事务

本文链接：https://blog.csdn.net/samxie0816/article/details/47077023

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Mysql支持三种锁

行锁
开销大，加锁慢，锁粒度小，会出现死锁。并发度高
表锁
开销小，加锁快，锁粒度大，不会出现死锁，并发度低
行锁
开销、加锁时间和并发度介于行锁和表锁之间，也会出现死锁

InnDB支持行锁，MyISAM只支持表锁

MyISAM

MyISAM的表级锁分为共享读锁和独占写锁。

None	读锁	写锁
读锁	是	是
写锁	是	否

读操作与写操作之间、写操作之间是串行的。
在自动加锁的情况下，MyISAM总是一次性获取SQL语句所需要的全部锁。所以MyISAM不会出现死锁。

MyISAM不支持事务

MyISAM不太适合有大量更新操作和查询操作的应用,适合更新操作

一个进程请求某个表的读锁，同时另一个进程请求某个表的写锁，那么写进程会先得到锁。不仅如此，在MyISAM的锁等待序列中，写锁优先于读锁，写锁会插入到读锁前面。可以通过low-priority-updates提高读锁的优先级。

InnDB

事务（Transation）及其ACID属性

原子性 Atomicity

事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性 Consistent

在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
隔离性 Isolation

数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性 Durable

事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务带来的问题

更新丢失

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。
脏读

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。
不可重复读

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变。
幻读

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据。

不可重复读是指同一事务两次读的数据不一样，重在修改；幻读是指同样的条件下，两次读的记录数不一样，重点在于新增或删除

更新丢失应该通过应用的配合来消除

事务隔离级别

读未提交 READ_UNCOMMITTED
会出现脏读、不可重复读和幻读问题，并发度最高
读提交 READ_COMMITTED
锁定正在读取的行，会出现不可重复读和幻读问题
重复读 REPEATABLE_READ
锁定所有读取的行，会出现幻读问题
可序列化 SERIALIZABLE
锁定整个表，并发度低

数据库实现事务隔离的方式

一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

InnoDB的锁模式

共享锁 S

允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁 X

允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
意向共享锁 IS 表锁

事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁 IX 表锁

事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁

S	IS	X	IX
S	兼容	兼容	冲突
IS	兼容	兼容	冲突
X	冲突	冲突	冲突
IX	冲突	兼容	冲突

InnoDB的行锁实现

InnoDB的行锁是通过索引项加锁实现的，所以InnoDB只通过索引检索数据才会使用行锁，否则使用表锁

间歇锁 Next-Key

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：
Select * from emp where empid > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

引入间歇锁可以防止幻读

INSERT…SELECT…和 CREATE TABLE…SELECT…语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中应尽量避免使用。

什么时候使用表锁

事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。