MySQL：关于数据库的锁李青玄博客

MySQL：关于数据库的锁

为什么会有锁？

在存在并发操作数据的时候，必然需要一种机制来保证数据的完整性和一致性，锁就是这个目标的技术实现。

锁的概念类型分类：

在Mysql中，锁的类型有很多种，分别有不同的功能，但是根据具体的概念可以分为2类：

悲观锁
乐观锁

悲观锁：

悲观锁也叫 ’悲观并发控制‘，主要依靠于数据库提供的锁机制来实现。

理念是：对数据的操作保持悲观态度，例如A事务对数据进行读写等操作时，悲观地认为数据会被其他的会话修改，所以在操作之前会先对目标数据进行上锁。

其他的会话就会被阻塞，需要等待A事务操作完成，释放锁后，才可以操作。

乐观锁：

乐观锁也叫‘乐观并发控制’，主要依靠于使用者在程序设计中自己实现，通常使用时间戳或版本号方式。

理念是：对数据的操作保持乐观态度，例如A事务对数据进行读写操作时，乐观地认为操作的数据在不产生锁的情况下多个事务不会互相影响。

在提交数据更新之前，才会去检查在A事务获取数据之后，有没有其他事务更新了数据，如果其他的事务更新了数据，则A事务进行回滚，如果没有被修改，则完成提交。

什么时候使用悲观锁，什么时候使用乐观锁？

如果经常发生读写冲突，又希望修改，提交数据成功率高，那么可以使用悲观锁。

如果对性能要求高，允许脏读的话，则可以考虑使用乐观锁。

锁的功能类型：

从锁的功能上来看，可以分为共享锁（也称S锁）、排他锁（独占锁，也称X锁）、意向锁等。

共享锁又称为共享读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把读锁，都能访问到数据，但是其他事务不能获得相同数据集的写锁（排它锁）。

排他锁又称为排他写锁，简称X锁，顾名思义，排他锁就是不能与其他锁并存，如一个事务获取了一个数据集的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于一般的Select语句，InnoDB不会加任何锁。

除此之外InnoDB还有两个锁：

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁

意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需要用户干预。

锁的粒度类型：

从锁的粒度上来看，有：表锁，页锁，行锁。在MySQL中，最常见的就是表锁和行锁。

MyISAM引擎只有表锁，而InnoDB既有表锁，也有行锁。

MySQL表锁：

表锁的特点：

开销小，加锁快，不存在死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

表锁有两种模式：

1、表共享读锁（Table Read Lock）

共享读锁：是；共享写锁：否

2、表独占写锁（Table Write Lock）

共享读锁：否；共享写锁：否

解释：

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但是会阻塞其他用户对同一表的写请求，就是读共享，写不共享。

对MyISAM表的写操作，会阻塞其他用户对同一表的读写请求，两个都不共享。

MyISAM表的读和写操作之间，写和写操作之间都是串行的。（当一个线程获得对一个表的写锁后，只有拥有锁的线程才可以对表进行操作，其他线程的读和写都会阻塞等待，直到锁被释放。）

表锁的使用：

MyISAM 在执行查询语句 SELECT 前，会自动给所有涉及到的表加读锁。

在执行更新操作INSERT UPDATE DELETE 前，会自动给所有涉及到的表加写锁。

一般情况下，这个过程不需要用户干预，是MyISAM自动完成的，也可以在需要的时候使用LOCK TABLE命令给MyISAM表显式加锁（给MyISAM显式加锁，一般是为了一定程度模拟事务操作，实现对某一时间点多个表的一致性读取）举例:

LOCK TABLES order READ LOCAL,order_detail READ LOCAL;
SELECT SUM(price) FROM order_detail;
SELECT SUM(total) FROM order;
UNLOCK TABLES;

上面就是一个显式加锁模拟事务操作的例子，关于LOCAL关键字，其作用是在满足了MyISAM表并发插入条件的情况下，允许其他用户向表尾插入记录。

一次获得了SQL语句涉及表的全部锁，这也是MyISAM表不会出现死锁的原因。

总结：

在执行 LOCK TABLES 后，当前会话只能访问显式加锁的表，无法访问未加锁的表，其他会话不受影响
在执行LOCK TABLES 后，如果加的是读锁，那么当前会话只能执行查询操作，非当前会话可以执行UPDATE操作，但会被阻塞.
在满足一定的条件下，可以执行INSERT操作，不会被阻塞。被加锁的会话不会读取到其他会话插入的数据。何为‘满足一定条件’？MyISAM存储引擎有一个系统变量 concurrent_insert （并发插入）,专门用来控制并发插入的行为。当concurrent_insert=0时，不允许并发插入。当concurrent_insert=1（AUTO）时，MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录，也是默认设置当concurrent_insert=2时，不管表有没有空间，都允许在表结尾并发插入。
MyISAM引擎的读锁和写锁是互斥的，读操作之间是串行的，当2个进程同时分别请求同一表的读和写操作，My SQL会如何处理呢？MyISAM默认的锁调度机制是写优先。但是用户也可以通过设置 LOW_PRIORITY_UPDATES 参数，或者在INSERT UPDATE DELETE 语句中，指定LOW_PRIORITY选项来调节读写锁的调度。另外MySQL也提供了一种折中的办法来调节读写冲突，通过系统参数 max_write_lock_count设置的值，当一个表的读锁达到参数的值后，MySQL就会暂时把写请求的优先级降低，给读进程一定获得锁的机会。

MySQL行锁

行锁的特点：

主要存在于InnoDB引擎中，锁定粒度小，并发度高，存在死锁.

另外，InnoDB引擎的实现是基于多版本的并发控制协议-MVCC(Mulit-Version Concurrency Control),

MVCC最大的好处是：读不加锁，读写不冲突，而MyISAM无论是读写，都会隐式加锁。

但也有例外，MVCC并发控制中，读也分为快照读和当前读

快照读，简单的 SELECT 操作，读取记录的可见历史版本，不用加锁

当前读，特殊的读操作，插入，更新，删除等属于当前读，读取记录的实时版本，并且返回的记录都会上锁，保证其他并发事务无法对该记录进行修改。

对索引上锁

InnoDB行锁的实现，不是对数据行上锁，而是对索引项上锁，也就意味着：只有通过索引检索数据，InnoDB才会使用行锁，否则将会转为表锁。

间隙上锁

在使用范围查询的时候，行锁的机制是对符合的索引项检索的范围内的索引上锁，哪怕范围中有不存在的记录，这个称为间隙（GAP），InnoDB也会对这个间隙加锁

什么情况下使用InnoDB表锁?

一般情况下，应保证使用行锁，在特殊的事务中，可以使用表锁

1、事务需要更新大部分或者全部的数据，表又比较大，如果使用行锁，那么这个开销很大，会影响事务的执行效率，同时也会阻塞其他事务的锁等待，和锁冲突，这种情况下，可以考虑使用表锁来提高执行效率

2、事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚，这种情况可以考虑一次性锁定涉及到的表，从而避免死锁，减少开销。

如果上面2个场景过多出现，应该考虑使用MyISAM表。

总结：

1、行锁是对索引项上锁，而不是数据行上锁，如果是范围检索，还会对间隙上锁。

2、InnoDB表绝大多数情况下，都应该保证使用行锁，以此配合事务和并发操作

3、SELECT普通查询一般情况下不会上锁，MVCC并发控制协议：读不加锁，读写不冲突。

MySQL：关于数据库的锁