MySQL锁机制

最新推荐文章于 2024-10-11 17:04:33 发布

cufu3352

最新推荐文章于 2024-10-11 17:04:33 发布

阅读量106

点赞数

文章标签：数据库

MySQL中主要有两种锁：行级锁和表级锁：
行级锁(row-level)：特点是锁定对象的粒度小，发生锁定资源争用的概率也小，能够给予应用程序尽可能大的并发处理能力，从而提高一些需要高并发应用系统的整体性能。
但是行级锁定也有不少弊端，由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要的操作就更多,带来的消耗自然也就更大了，此外,行级锁定也最容易发生死锁。

表级锁(table-level)：该锁定机制最大的特点是实现逻辑非常简单,带来的系统处理成本最小,所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定,所以很好地避免困扰我们的死锁问题。当然，锁定颗粒度大带来最大的负面影响就是出现锁定资源争用的概率也会最高,致使并发性大打折扣。

表级锁有两种模式：

在MySQL中，常见存储引擎有myisam和innodb，其中myisam采用的是表级锁，而innodb采用的是行级锁。下面分别说说这两种存储引擎的锁机制。

myisam：采用表级锁，实现了表共享锁、表独占锁两种模式的表级锁。对于表的读请求，不会阻塞其他用户对同一表的读请求，但是会阻塞对同一表的写请求；
而对myisam的写操作，会阻塞其他用户对同一表的读、写操作。 myisam表的读、写操作之间是串行的。

myisam的加锁：在执行select操作前，会自动给涉及的所有表加读锁；在执行更新操作（update、delete、insert）前，会自动给涉及的表加写锁，这个过程不需要用户干预。

myisam的锁调度：之前说过，myisam的读锁和写锁是互斥的、串行的，一个进程请求myisam表的读锁时，此时另一个线程请求同一表写锁，这时候mysiam怎么调度？
答案是：写进程优先获得锁，不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁前面，这是因为myisam认为一般写请求比读请求重要。
也正是这样，myisam不太适合有大量更新和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

myisam的并发插入： myisam也支持查询、插入并发进行。
当current_insert = 0时，不允许并发插入
当current_insert = 1时，如果myisam表没有空洞（表的中间没有被删除的行），允许在一个用户select时，另一个用户在表尾插入记录。这也是myisam的默认设置。
当current_insert = 2时，无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

innodb：与myisam最大的区别有两点，一是innodb支持事务，二是innodb采用了行级锁。在innodb中，默认的事务隔离级别是repeatable（在oracle中是read commited）
innodb通过以下两种手段来实现事务的隔离级别：
一种是读取数据时对其加锁，阻止其他事务对数据进行修改（repeatable）。
另一种是不用任何加锁，而是通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照提供一定级别的（语句级或事务级）的一致性读取，
从用户的角度看，好像数据库提供了同一数据的多个版本，因此这种技术叫做多版本并发控制（ MultiVersion Concurrency Control,简称MVCC ），它是锁的辅助手段。

MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。

innodb实现了两种类型的行锁：
共享锁(S)：允许其它事务获取相同数据的共享锁，阻止其他事务获得相同数据集的排他锁。

排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁，这里不做研究。

InnoDB行锁实现方式： InnoDB行锁是通过给索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应的数据行来实现的。

InnoDB这种行锁实现特点意味着：在写数据时只有通过索引条件检索数据，InnoDB才使用行锁,否则，InnoDB将会加很多锁（所有满足条件的记录+Gap），然后在MySQL Server层中过滤，将不满足条件的row unlock掉。

innodb实现MVCC的关键点：记录行的两个隐藏字段，更新时间（版本）、过期时间（版本），以及undo log文件（回滚段）
select：只有满足以下条件才会被返回（RR隔离级别下）：
1，记录的更新版本小于当前事务版本
2，过期版本要么为空（该行没被删除过），要么删除版本号大于事务版本（该行是被该事务后面启动的事务删除）

delete：将原来数据行复制一份到undo log中，将undo log中的备份作为记录的过期版本，不是直接删除。

update：将原来数据行复制一份到undo log中，将undo log中的备份作为记录的过期版本；将当前事务版本作为记录的更新版本

insert：新插入的行，更新版本为该事务的版本

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？在InnoDB中：
快照读：简单的select操作，属于快照读，不加锁。
    select * from table where ?;
普通select语句是不会被阻塞的，不管数据加了什么锁。

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
    select * from table where ? lock in share mode;
    select * from table where ? for update;
    insert into table values (…);
    update table set ? where ?;
    delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。
其中，除了第一条语句（share mode），对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

分析以下两条SQL的加锁情况：
select * from test where id = 10;
delete from test where id = 10;
要分析SQL语句的加锁情况，就得提前知道以下前提条件：
1，id是否为主键？
2，如果不是主键，id上是否有索引？
3，如果id有索引，是否唯一索引？
4，隔离级别？
5，执行计划

下面假定一些前提条件，看看是如何加锁的：
1， id主键+RC隔离级别
此时，select操作不加锁，采用的是快照读
对于delete，只需要将主键上id = 10的记录加上X锁即可

2，id 唯一索引+ RC（假如主键是name）
select操作不加锁，采用的是快照读
delete：首先会将unique索引上的id=10索引记录加上X锁，同时，会根据id索引存储的主键信息读取到的name列（innodb辅助索引存储的是主键的值），
然后将聚簇索引上的name = ‘abc’ 对应的主键索引项加X锁。这里为什么会对聚簇索引加锁？如果并发的一个SQL：update test set id = 100 where name = ‘abc'；此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。所以这种情况下，会加两个X锁

3，id非唯一索引+RC
select操作不加锁，采用的是快照读
delete：与第二种情况差不多，所有满足SQL查询条件的记录，都会被加锁，同时，这些记录在主键索引上的记录，也会被加锁。

4，id无索引+RC
select操作不加锁，采用的是快照读
delete：由于id列上没有索引，因此只能走聚簇索引，进行全表扫描，这里既不是加表锁，也不是只给满足条件的记录加锁，而是将聚簇索引所有记录都加上X锁。在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

5，id主键+RR
与id主键+RC完全一致

6，id唯一索引+RR
与id唯一索引+RC完全一致

7，id非唯一索引+RR
select操作不加锁，采用的是快照读
delete：与第三种情况区别的是，除了非唯一索引与聚簇索引加锁之外，为了保证同一个事务内两次当前读（select * from test where id = 10 for update）返回相同的结果（多条记录的可重复多读），需要禁止其他事务插入id=10的记录，所以这就需要间隙锁，将小于10（直到遇到存在的id），等于10，大于10（直到遇到存在的id）的区间都加上间隙锁。

8，id无索引+RR
select：依然是快照读
delete：全表扫描，聚簇索引下的所有记录都会加X锁，同时，聚簇索引每条记录之间的间隙，还要加上间隙锁。这种情况下，除了不加锁的快照度，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。就算有MySQL Server进行优化，也是非常恐怖的事。

9，Serializable
delete操作与RR隔离级别情况下一致，但是，所有的select都是当前读，需要加锁（共享锁S）。

关于间隙锁补充：

InnoDB的间隙锁(Gap锁)：当我们写数据时检索到不存在的键值（不管是范围检索，还是等值检索），那么这个键值（不存在）左右的空白区域（直到遇到存在的键值）都会被加上间隙锁,
其它事务想插入加上间隙锁的记录时，会被阻塞。
如：表里有id=1,2,10的记录，update table set xx where id <= 10，这时，id=3到id=9会被加上间隙锁，11以后也会加上间隙锁（这里不是很明白，只要触发了边界，边界之后都会加上间隙锁）。
表里有id=1,2,10的记录，update table set xx where id = 7，这时，从3-6,8-9都会被加上间隙锁。

InnoDB使用间隙锁的目的,一方面是为了防止幻读，以满足相关隔离级别的要求,另外一方面,是为了满足其恢复和复制的需要。

由于MySQL的恢复机制(复制其实就是在slave mysql不断做基于binlog的恢复)，如果不使用上述的间隙锁，可能到slave那边的binlog就会不正常。
这种sql被称为不确定(non-deterministic)的SQL语句。这个跟mysql的系统变量关:innodb_locks_unsafe_for_binlog(默认是off).

所以禁用如下语句:

insert into target_tab select * from source_tab where …

create table new_tab … select … from source_tab where …

小结：

对于MyISAM表,主要讨论了以下几点.

(1)共享读锁(S)之间是兼容的,但共享读锁(S)与排他锁(X)之间,以及排他锁(X)之间是互斥的,也就是说读和写是串行的.

(2)在一定条件下,MyISAM允许查询和插入并发执行,我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题.

(3)MyISAM默认的锁调度机制是写优先,这并不一定适合所有应用,用户可以通过设置LOW_PRIORITY_UPDATES参数,或在 Insert,update,delete语句中指定LOW_PRIORITY选项来调节读写锁的作用.

(4)由于表锁的锁定粒度大,读写之间又是串行的.因此,如果更新操作较多,MyISAM表可能会出现严重的锁等待,可以考虑采用InnoDB表来减少冲突。

对于InnoDB表,主要讨论以下几项内容

*InnoDB的锁是基于索引实现的，写数据时如果不通过索引访问数据，InnoDB将会产生间隙锁，甚至将所有记录锁住。

*介绍了InnoDB间隙锁(Gap)机制，以及InnoDB使用间隙锁的原因。