MySQL锁机制详解

       锁是指一种软件机制，用来控制防止某个用户（进程会话）在已经占用了某种数据资源时，其他用户做出影响本用户数据操作或导致数据非完整性和非一致性问题发生的手段。所以，数据库锁机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问时变得有序所设计的一种规则。

---

一、MySQL锁的级别

按照锁级别划分，锁可分为共享锁、排他锁。

1.1 共享锁（读锁）

针对同一块数据，多个读操作可以同时进行而不会互相影响。即读锁是共享的，相互之间不会阻塞。多个客户同一时间可以读取同一个资源，而互不干扰。

共享锁只针对写（inset、update、delete）操作时候加锁，在未对写操作提交之前，其他事务只能够获取(select)最新的记录但不能够写操作。

1.2 排他锁（写锁）

当前写操作没有完成前，阻断其他写锁和读锁。

---

二、MySQL锁粒度

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计的，所以各存储引擎的锁粒度也有较大区别。

总地来说，MySQL各存储引擎使用了三种类型（级别）的锁粒度：行级锁定、页级锁定和表级锁定。

2.1 行级锁定（row-level）

行级锁定最大的特点就是锁定对象的颗粒度很小，它是目前各大数据库管理软件所实现的锁定颗粒度最小的。MySql行级锁只在存储引擎层实现。由于锁定颗粒度很小，发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力，从而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁须要的操作就更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

用法：

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

2.2 表级锁定（table-level）

和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。它会锁定整张表，一个入户在对表进行写（插入、删除、更新）操作前，需要先获取写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁的时候，其他用户才能获得读锁，读锁之间不会相互阻塞。

该锁定机制最大的特点是实现逻辑非常简单，带来的系统处理成本最小，所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好地避免困扰我们的死锁问题。

当然，锁定颗粒度大带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。

尽管存储引擎可以管理自己锁，MySql本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如Alter Table之类的语句使用表锁，而忽略存储引擎的锁机制。

在MySQL数据库中，使用表级锁定的主要是MyISAM、Memory、CSV等一些非事务性存储引擎，

用法

共享锁(s 锁 读锁)
LOCK TABLE table_name [ AS alias_name ] READ
排他锁(x 锁 写锁 )
LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

2.3 页级锁定（page-level）

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中并不太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力同样也介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据须要消耗的内存数量越来越多，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。

在MySQL数据库中，使用表级锁定的主要是MyISAM、Memory、CSV等一些非事务性存储引擎，而使用行级锁定的主要是InnoDB存储引擎和NDB Cluster存储引擎，页级锁定主要是BerkeleyDB存储引擎的锁定方式。

---

三、MVCC多版本并发锁

英文全称为Multi-Version Concurrency Control,翻译为中文即 多版本并发控制。MVCC其实广泛应用于数据库技术，像Oracle,PostgreSQL等也引入了该技术，即适用范围广。MVCC，是行级锁的一种变种，基于提升并发性考虑，在很多情况下避免了加锁操作。虽然实现机制不同，但大多都实现了非阻塞的读操作，写操作也只锁定必要的行。数据库默认隔离级别：RR（Repeatable Read，可重复读），MVCC主要适用于Mysql的RC,RR隔离级别。

3.1 基本原理

MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

3.2 基本特征

每行数据都存在一个版本，每次数据更新时都更新该版本。

修改时Copy出当前版本随意修改，各个事务之间无干扰。

保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

3.3 InnoDB存储引擎MVCC的实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚，不在本文范畴）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

MVCC下InnoDB的增删查改是怎么work的

创建一张存储引擎为testmvcc的表，sql为:

CREATE TABLE testmvcc (
 id int(11) DEFAULT NULL,
 name varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（1）插入数据

记录的版本号即当前事务的版本号

执行一条数据语句：insert into testmvcc values(1,"test");

假设事务id为1，那么插入后的数据行如下：

（2）在更新操作的时候

采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。

比如，针对上面那行记录，事务Id为2 要把name字段更新

update table set name= 'new_value' where id=1;

（3）删除操作的时候

就把事务版本号作为删除版本号。比如

delete from table where id=1;

（4）查询操作：

从上面的描述可以看到，在查询时要符合以下两个条件的记录才能被事务查询出来：

1) 删除版本号未指定或者大于当前事务版本号，即查询事务开启后确保读取的行未被删除。(即上述事务id为2的事务查询时，依然能读取到事务id为3所删除的数据行)

2) 创建版本号 小于或者等于 当前事务版本号 ，就是说记录创建是在当前事务中（等于的情况）或者在当前事务启动之前的其他事物进行的insert。

（即事务id为2的事务只能读取到create version<=2的已提交的事务的数据集）

（5）MVCC下读的两种形式

.在MVCC下，读操作可以分为两种：快照读、当前读

 1）快照读

 select * from tbl_name where ...

 2）当前读

 select * from tbl_name where ... for update;

 update

 delete

 insert

---

四、开发中注意的问题

4.1 Innodb中的行锁与表锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

在不通过索引条件查询的时候（排他锁）,InnoDB 确实使用的是表锁,而不是行锁。

由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。应用设计的时候要注意这一点。

当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。

即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的,如果 MySQL认为全表扫 效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。

4.2 Innodb中行级锁与死锁

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。