表级锁定（table-level）

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制
该锁定机制最大的特点是实现逻辑非常简单 带来的系统负面影响最小 所以获取锁和释放锁的速度很快
由于表级锁一次会将整个表锁定 所以可以很好的避免困扰我们的死锁问题
当然 锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高 致使并大度大打折扣
使用表级锁定的主要是MyISAM、MEMORY、CSV等一些非事务性存储引擎

行级锁定（row-level）

行级锁定最大的特点就是锁定对象的颗粒度很小 也是目前各大数据库管理软件所实现的锁定颗粒度最小的
由于锁定颗粒度很小 所以发生锁定资源争用的概率也最小 能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能
虽然能够在并发处理能力上面有较大的优势 但是行级锁定也因此带来了不少弊端
由于锁定资源的颗粒度很小 所以每次获取锁和释放锁需要做的事情也更多 带来的消耗自然也就更大了
此外 行级锁定也最容易发生死锁
使用行级锁定的主要是InnoDB存储引擎

InnoDB锁定模式及实现机制

InnoDB是目前事务型存储引擎中使用最为广泛的存储引擎 所以这里我们就主要分析一下InnoDB的锁定特性
总的来说 InnoDB的锁定机制和Oracle数据库有不少相似之处
InnoDB的行级锁定同样分为两种类型 共享锁和排他锁
共享锁：也叫读锁（S锁） 一个事务并发读取某一行记录所需要持有的锁
排它锁：也叫写锁（X锁） 一个事务并发更新或删除某一行记录所需要持有的锁
而在锁定机制的实现过程中为了让行级锁定和表级锁定共存 InnoDB也同样使用了意向锁（表级锁定）的概念 也就有了意向共享锁和意向排他锁这两种

当一个事务需要给自己需要的某个资源加锁的时候 如果遇到一个共享锁正锁定着自己需要的资源的时候 自己可以再加一个共享锁 不过不能加排他锁
但是如果遇到自己需要锁定的资源已经被一个排他锁占有之后 只能等待该锁释放资源之后自己才能获取锁定资源并添加自己的锁定
即对于读锁 若锁住了某一行 其他事务也可以读 但不可以写（可以在相同行加读锁）
对于写锁 若锁住了某一行 其他事务既不可以读 也不可以写

而意向锁的作用就是当一个事务在需要获取资源锁定的时候 如果遇到自己需要的资源已经被其他锁占用的时候 该事务可以需要锁定行的表上面添加一个合适的意向锁
如果自己需要一个共享锁 那么就在表上面添加一个意向共享锁
而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话 则先在表上面添加一个意向排他锁
意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在
所以可以说InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX）
我们可以通过以下表格来总结上面这四种锁的共存逻辑关系：

IS IX
IS 兼容 兼容
IX 兼容 兼容
S X
IS 兼容 互斥
IX 互斥 互斥

注意这里的共享锁和排它锁指的都是表级锁，意向锁是不会与行级的**共享锁和排它锁互斥的**
比如说事务A获取到了表中某行记录的排它锁，而且未提交，事务B想要获取表的表锁
所以因为共享锁和排它锁是互斥的，所以事务B的加锁请求会被阻塞
意向锁的作用就在于：
事务B可以直接通过这个意向排它锁来确定事务A必然持有某些数据行的排它锁
而不需要去检查表中的每一行来判断记录是否加了排它锁

如果一个事务请求的锁模式与当前的锁兼容 InnoDB就将请求的锁授予该事务
反之 如果两者不兼容 该事务就要等待锁释放
意向锁是InnoDB自动加的 不需用户干预
对于UPDATE、DELETE和INSERT语句 InnoDB会自动给涉及数据集加排他锁（X)
对于普通SELECT语句 InnoDB不会加任何锁
事务可以通过以下语句显示给记录集加共享锁或排他锁

  1. begin
  2. select * from table where id = 1 lock in share mode;  --共享锁
  3. commit
  5. begin
  6. select * from table where id = 1 for update;  --排它锁
  7. commit

用SELECT … LOCK IN SHARE MODE获得共享锁 主要用在需要数据依存关系时来确认某行记录是否存在 并确保没有人对这个记录进行UPDATE或者DELETE操作
但是如果当前事务也需要对该记录进行更新操作 则很有可能造成死锁
对于锁定行记录后需要进行更新操作的应用 应该使用SELECT… FOR UPDATE方式获得排它锁

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的 只有通过索引条件检索数据 InnoDB才使用行级锁 否则 InnoDB将使用表锁
在实际应用中 要特别注意InnoDB行锁的这一特性 不然的话 可能导致大量的锁冲突 从而影响并发性能
注意：
1）在不通过索引条件查询的时候 InnoDB确实使用的是表锁 而不是行锁
2）由于MySQL的行锁是针对索引加的锁 不是针对记录加的锁 所以虽然是访问不同行的记录 但是如果是使用相同的索引键 是会出现锁冲突的
3）当表有多个索引的时候 不同的事务可以使用不同的索引锁定不同的行
另外 不论是使用主键索引、唯一索引或普通索引 InnoDB都会使用行锁来对数据加锁
4）即便在条件中使用了索引字段 但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的
如果MySQL认为全表扫描效率更高 比如对一些很小的表 它就不会使用索引
这种情况下InnoDB将使用表锁 而不是行锁
因此在分析锁冲突时 记得检查SQL的执行计划 以确认是否真正使用了索引

临建锁（Next-Key锁）/间隙锁（GAP）

当我们用范围条件而不是相等条件检索数据 并请求共享或排他锁时 InnoDB会给符合条件的已有数据记录的索引项加临建锁
对于键值在条件范围内但并不存在的记录 InnoDB也会对这个“间隙”加锁 这种锁机制就是所谓的间隙锁
总结就是 范围查询时 有数据命中 加的就是**临建锁 没有数据命中 加的就是间隙锁
加锁的范围是左闭右开的
注意 只有当前事务的隔离级别为RR（可重复读）级别 才会有这两个锁**
例：
假如emp表中只有101条记录 其empid的值分别是 1,2,…,100,101 下面的SQL

  1. select * from emp where empid > 99 and empid < 101 for update; --临建锁 有一条数据命中 即empid为100的 此时其他事务可以访问99 但不能访问101
  3. select * from emp where empid > 99 and empid < 100 for update; --间隙锁 没有数据命中 此时其他事务可以访问99 但不能访问100

InnoDB使用临建锁的目的：
1）防止幻读
MySQL 默认隔离级别是RR
在这种级别下，如果你使用 select in share mode 或者 select for update 语句，那么InnoDB会使用临键锁，因而可以防止幻读
如果你使用的是普通的select语句（不加锁），那么此时 InnoDB 引擎将是使用快照读，而不会使用任何锁，因而还是无法防止幻读
对于上面的例子 要是不使用临建锁 如果其他事务修改了empid=100的记录 那么本事务如果再次执行上述语句 就会发生幻读
2）为了满足其恢复和复制的需要
很显然 在使用范围条件检索并锁定记录时 即使某些不存在的键值也会被无辜的锁定 而造成在锁定的时候无法插入锁定键值范围内的任何数据
在某些场景下这可能会对性能造成很大的危害
除了临建锁给InnoDB带来性能的负面影响之外 通过索引实现锁定的方式还存在其他几个较大的性能隐患：
a）当Query无法利用索引的时候 InnoDB会放弃使用行级别锁定而改用表级别的锁定 造成并发性能的降低
b）当Query使用的索引并不包含所有过滤条件的时候 数据检索使用到的索引键得到的数据可能有部分并不是我们想象要的（即在原有数据基础上多了一部分用不到的数据）
此时数据也会被锁定 因为临建锁锁定的是一个范围 而不是具体的索引键
c）当Query在使用索引定位数据的时候 如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分）一样会被锁定
因此在实际应用开发中 尤其是并发插入比较多的应用 我们要尽量优化业务逻辑 尽量使用相等条件来访问更新数据 避免使用范围条件
还要特别说明的是 InnoDB除了通过范围条件加锁时使用临建锁外 如果使用相等条件请求给一个不存在的记录加锁 InnoDB会使用间隙锁

死锁

在InnoDB中 除单个SQL组成的事务外 锁是逐步获得的
当两个事务都需要获得对方持有的排他锁才能继续完成事务 这种循环锁等待就是典型的死锁
在InnoDB的事务管理和锁定机制中 有专门检测死锁的机制 会在系统中产生死锁之后的很短时间内就检测到该死锁的存在
当InnoDB检测到系统中产生了死锁之后 InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚 而让另外一个较大的事务成功完成
实际上在InnoDB发现死锁之后 会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小
也就是说哪个事务所改变的记录条数越多 在死锁中就越不会被回滚掉

需要注意的就是 当产生死锁的场景中涉及到不止InnoDB存储引擎的时候 InnoDB是没办法检测到该死锁的
这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决
需要说明的是 这个参数并不是只用来解决死锁问题
在并发访问比较高的情况下 如果大量事务因无法立即获得所需的锁而挂起 会占用大量计算机资源 造成严重性能问题 甚至拖跨数据库
我们通过设置合适的锁等待超时阈值 可以避免这种情况发生

通常来说 死锁都是应用设计的问题 通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句 绝大部分死锁都可以避免
下面就通过实例来介绍几种避免死锁的常用方法：
1）在应用中 如果不同的程序会并发存取多个表 应尽量约定以相同的顺序来访问表 这样可以大大降低产生死锁的机会
2）在程序以批量方式处理数据的时候 如果事先对数据排序 保证每个线程按固定的顺序来处理记录 也可以大大降低出现死锁的可能
3）在事务中 如果要更新记录 应该直接申请足够级别的锁 即排他锁 而不应先申请共享锁 然后更新时再申请排他锁
因为此时想要申请排他锁时 其他事务可能又已经获得了相同记录的共享锁 从而造成锁冲突 甚至死锁
4）在REPEATABLE-READ隔离级别下 如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁 在没有符合该条件记录情况下 两个线程都会加锁成功
程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。
这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
5）当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。
此时，只有一个线程能插入成功，另一个线程会出现锁等待。
当第1个线程提交后，第2个线程会因主键重复出错，但虽然这个线程出错了，却会获得一个排他锁。
这时如果有第3个线程又来申请排他锁，也会出现死锁。
对于这种情况，可以直接做插入操作，然后再捕获主键重复异常，或者在遇到主键重复错误时，总是执行ROLLBACK释放获得的排他锁。

什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。
但在个别特殊事务中，也可以考虑使用表级锁：
1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突
这种情况下可以考虑使用表锁来提高该事务的执行速度。
2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。
这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。
在InnoDB下，使用表锁要注意以下两点。
a）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的
仅当autocommit=0、InnoDB_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁
这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则，InnoDB将无法自动检测并处理这种死锁。
b）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁
事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务
COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁
正确的方式见如下语句：
例如，如果需要写表t1并从表t读，可以按如下做：

  1. SET AUTOCOMMIT=0;
  2. LOCK TABLES t1 WRITE, t2 READ, ...;
  3. [do something with tables t1 and t2 here];
  4. COMMIT;
  5. UNLOCK TABLES;

InnoDB行锁优化建议

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。
当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：
a）尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定
b）合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行
c）尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录
d）尽量控制事务的大小，减少锁定的资源量和锁定时间长度
e）在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本
2）由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：
a）类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁
b）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率
c）对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率
3）可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

  1. mysql> show status like 'InnoDB_row_lock%';
  2. +-------------------------------+-------+
  3. | Variable_name                 | Value |
  4. +-------------------------------+-------+
  5. | InnoDB_row_lock_current_waits | 0     |
  6. | InnoDB_row_lock_time          | 0     |
  7. | InnoDB_row_lock_time_avg      | 0     |
  8. | InnoDB_row_lock_time_max      | 0     |
  9. | InnoDB_row_lock_waits         | 0     |
  10. +-------------------------------+-------+

InnoDB 的行级锁定状态变量不仅记录了锁定等待次数，还记录了锁定总时长，每次平均时长，以及最大时长，此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。
对各个状态量的说明如下：
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（等待总时长）这三项。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

锁冲突的表、数据行等，并分析锁争用的原因。
具体方法如下：

  1. mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

然后就可以用下面的语句来进行查看：

  1. mysql> show engine InnoDB status;

监视器可以通过发出下列语句来停止查看：

  1. mysql> drop table InnoDB_monitor;

设置监视器后，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。
为什么要先创建一个叫InnoDB_monitor的表呢？
因为创建该表实际上就是告诉InnoDB我们开始要监控他的细节状态了，然后InnoDB就会将比较详细的事务以及锁定信息记录进入MySQL的errorlog中，以便我们后面做进一步分析使用。
打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用“—console”选项来启动服务器以关闭写日志文件。