一、锁的分类

1、按锁的粒度划分，可分为表级锁、行级锁、页级锁
2、按锁级别划分，可分为共享锁、排他锁
3、按使用方式划分，可分为乐观锁、悲观锁

1.1 按粒度划分的锁

1.1.1 表级锁（偏向于读）

优缺点：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
支持引擎：MyISAM、MEMORY、InNoDB
表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

1.1.2 行级锁

优缺点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
支持引擎：InnoDB
行级锁定分为行共享读锁（共享锁）与行独占写锁（排他锁）
行锁的锁定颗粒度在MySQL中是最细的，应用于InnoDB存储引擎，只针对操作的当前行进行加锁。并发情况下，产生锁等待的概率较低，支持较大的并发数，但开销大，加锁慢，而且会出现死锁。
在InnoDB中使用行锁有一个前提条件：检索数据时需要通过索引。因为InnoDB是通过给索引的索引项加锁来实现行锁的。
在不通过索引条件查询的时候，InnoDB会使用表锁，这在并发较大时，可能导致大量的锁冲突。此外，行锁是针对索引加锁，存在这种情况，虽然是访问的不同记录，但使用的是同一索引项，也可能会出现锁冲突。提示：不一定使用了索引检索就一定会使用行锁，也有可能使用表锁。因为MySQL会比较不同执行计划的代价，当全表扫描比索引效率更高时，InnoDB就使用表锁。因此需要结合SQL的执行计划去分析锁冲突。
行锁会产生死锁，因为在行锁中，锁是逐步获得的，主要分为两步：锁住主键索引，锁住非主键索引。如：当两个事务同时执行时，一个锁住了主键索引，在等待其他索引；另一个锁住了非主键索引，在等待主键索引。这样便会发生死锁。InnoDB一般都可以检测到这种死锁，并使一个事务释放锁回退，另一个获取锁完成事务。

1.1.3 页级锁

对于行级锁与表级锁的折中，开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

表锁定与行锁定实际操作数据库

1.2 按锁的级别划分

1.2.1 共享锁（读锁）

一个事务获取了一个数据行的共享锁，本事务可以对该数据进行修改，其他事务都能访问到数据，但是只能读不能修改，且不允许对数据行加排他锁。

用法：SELECT … LOCK IN SHARE MODE;前边必须使用begin

1.2.2 排他锁（写锁）

一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

用法：SELECT … FOR UPDATE;前边必须使用begin

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

假设一张表t，有id和name两个字段主键为id

select * from t where id = 1 for update; /*明确指定主键，并且有此笔记录，row lock*/
select * from t where id = 11 for update; /*明确指定主键，若查无此笔记录，无lock*/
select * from t where name ='qweq' for update; /*无主键，table lock*/
select * from t where id <> 2 for update; /*主键不明确，table lock*/
select * from t where id like 3 for update; /*主键不明确，table lock*/

表级锁分为：读锁和写锁（lock table 表名称 read(write),表名称2 read(write)...）
行级锁分为：共享锁和排他锁

二、MyISAM存储引擎的锁

2.1 支持表锁（偏向于读）

MyISAM在执行SQL语句时，会自动为SELECT语句加上共享锁，为UPDATE/DELETE/INSERT操作加上排他锁
MyISAM读写、写写之间是串行的，读读之间是并行的
由于表锁的锁定粒度大，读写又是串行的，因此如果更新操作较多，ＭyISAM表可能会出现严重的锁等待

2.2 并发锁

在存储引擎中有一个系统变量concurrent_insert，专门控制其并发插入的行为：

concurrent_insert=0时，不允许并发插入功能。
concurrent_insert=1时，允许对没有空洞（即表的中间没有被删除的行）的表使用并发插入，新数据位于数据文件结尾（缺省）。
concurrent_insert=2时，不管表有没有空洞，都允许在数据文件结尾并发插入。

只需在加表锁命令中加入“local”选项，即：lock table tbl_name local read，在满足MyISAM表并发插入条件的情况下，其他用户就可以在表尾并发插入记录，但更新操作会被阻塞，而且加锁的用户无法访问到其他用户并发插入的记录。

在Mysql 5.5.2及以下版本concurrent_insert参数使用数值型默认为1，从5.5.3版本开始concurrent_insert参数用枚举值默认为AUTO。

2.3 锁调度

前面讲过，MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

设置MyISAM调度行为：
a、通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
b、通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
c、通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
d、系统参数max_write_lock_count设置一个合适的值；当一个表的读锁达到这个值后，MySQL便暂时将写请求的优先级降低，给读进程一定获得锁的机会。

MySQL中UPDATE的LOW_PRIORITY解决并发问题

三、InnoDB存储引擎的锁

与InnoDB与MyISAM的最大不同有两点:

支持事务
采用行锁

3.1 支持事务

1. 事务ACID（原子性、一致性、隔离性和持久性）
2. 事务的并发处理导致的问题

更新丢失：读取数据后，被其他事务覆盖数据
脏读：读取数据后，更新数据的事务回滚了，也就是读取的数据不正确
不可重复读：由于其他事务的插手，在同一事务中两次相同的查询数据是不同的（由于修改导致）
幻读：由于事务1有了新增或删除操作，事务2读取不到事务1操作的数据行，因此再操作对应的行就会被阻塞。（特别注意：幻读并不是两次查询的结果不同）

3. 事务隔离级别
更新数据丢失不仅仅是数据库事务控制器解决，主要由应用解决。本来是为了实现事务的并发，以下操作对于并发的副作用越来越小，但付出的代价越来越大。

读未提交的数据（Read uncommitted）：可能有脏读、不可重复读、幻读的问题
读提交的数据（Read committed），没有脏读的问题，可能有不可重复读、幻读的问题
可重复读（Repeatable read）：没有脏读、不可重复读的问题，可能有幻读的问题（InnoDB通过多版本并发控制MVCC解决了幻读问题）
可串行化（Serializable）：没有脏读、不可重复读、幻读的问题

3.2 行锁

行级锁分为：

记录锁（Record lock）：对索引项加锁，即锁定一条记录。

间隙锁（Gap lock）：对索引项之间的“间隙”、对第一条记录前的间隙或最后一条记录后的间隙加锁，即锁定一个范围的记录，不包含记录本身

Next-key Lock：锁定一个范围的记录并包含记录本身（上面两者的结合）。

3.2.1 共享锁与排他锁

共享锁：事务对数据添加了读锁，本事务可以对该数据进行修改，其他事务也只能加读锁，期间不能修改，直到事务释放读锁；若其他事务也获得读锁，则本事务和其他事务对该数据的修改都会被阻塞
排他锁：若事务T对数据加排他锁，本事务可以读也可以写数据，其他事务不能对数据加任何锁，直到事务T释放锁

3.2.2 意向共享锁与意向排他锁

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

注意：普通select操作不会添加任何锁

注意：申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。

3.2.3 行级锁（Record lock）导致的死锁

为什么会产生死锁？

1、产生死锁原理：在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。（如UPDATE USER SET NAME=’HELLO’ WHERE ID > 5会锁定所有主键大于等于5的记录，在该语句完成前，不能对主键等于5的记录进行操作加锁）

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

3、如何避免死锁：用SHOW ENGINE INNODB STATUS;命令来确定最后一个死锁产生的原因和改进措施

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

记录一次MySQL死锁排查过程

3.2.4 行级锁的间隙锁（Next-Key lock）

1、什么时候会出现间隙锁？
用法：select * from 表名 where 字段名 > 参数 for update;
使用范围条件而不是相等条件检索数据，InnoDB除了给索引记录加锁，还会给不存在的记录（间隙）加锁，其他事务不能操作当前事务锁定的索引与间隙。

2、目的

防止幻读，避免其他事务插入数据
满足其恢复和复制的需要，MySQL的恢复机制是通过BINLOG记录来执行IUD操作来同步Slave的，这就要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，为了恢复，不能插入其他事务。

3、危害
因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被锁定，而造成在锁定期间无法插入锁定键值范围内的任何数据，在某些场景下这可能会对性能造成很大的危害。

间隙锁实例博客

3.2.5 什么时候使用表锁？

绝大部分情况使用行锁，但在个别特殊事务中，也可以考虑使用表锁

1. 事务需要更新大部分数据，表又较大

若使用默认的行锁，不仅该事务执行效率低（因为需要对较多行加锁，加锁是需要耗时的）；而且可能造成其他事务长时间锁等待和锁冲突；这种情况下可以考虑使用表锁来提高该事务的执行速度。

2. 事务涉及多个表，较复杂，很可能引起死锁，造成大量事务回滚

这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销；当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ

四、乐观锁与悲观锁

4.1 悲观锁

行锁、表锁、读锁、写锁都是在操作之前先上锁

悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

流程：
（1）在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
（2）其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

优点：
悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。

缺点：

在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；
在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

4.2 乐观锁

乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。如果系统并发量非常大，悲观锁会带来非常大的性能问题，选择使用乐观锁，现在大部分应用属于乐观锁。

版本控制机制

每一行数据多一个字段version，每次更新数据对应版本号+1。
原理：读出数据，将版本号一同读出，之后更新，版本号+1，提交数据版本号大于数据库当前版本号，则予以更新，否则认为是过期数据，重新读取数据。

使用时间戳实现

每一行数据多一个字段time。
原理：读出数据，将时间戳一同读出，之后更新，提交数据时间戳等于数据库当前时间戳，则予以更新，否则认为是过期数据，重新读取数据。

深入理解乐观锁与悲观锁

五、案例分析

5.1 表锁案例分析

5.1.1 加读锁

lock table 表名 read;

当前session和其他session都可以查询该表记录；
当前session不能查询其他没有锁定的表，其他session可以查询或更新未锁定的表；
当前session插入或更新锁定的表会报错，其他session插入或更新锁定的表会一直等待获得锁。

直到释放锁unlock tables;之后，其他session才会获得锁，插入或更新操作完成。

5.1.2 加写锁

lock table 表名 write;

当前session对锁定表的查询+插入+更新操作都可执行，其他session对锁定表的查询被阻塞，需要等待锁被释放。
直到释放锁unlock tables;之后，其他session才会获得锁，查询返回。

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。（阻塞指的是阻塞其他session）

5.1.3 表锁分析

查看哪些表被加锁了

show open tables;

如何分析表锁定

可以通过检查table_lock_waited和table_locks_immediate状态变量来分析系统上的表锁定

show status like 'table%';

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况。

5.2 行锁案例分析

/*step1：session1更新但不提交*/
set autocommit=0;
update user set salary=220 where id=1;

/*step2：session2被阻塞，只能等待*/
set autocommit=0;
update user set salary=220 where id=1;
....

/*step3：session1提交更新*/
commit;

/*step4：session2解除阻塞，更新正常进行*/

5.2.1 行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况。

show status like 'innodb_row_lock%';

对各个状态量的说明如下：

Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：

Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

5.2.2 优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离