MyISQM表锁

InnoDB锁问题

事务隔离级别

InnoDB的行锁模式

InnoDB行锁实现方式

Next-Key锁

恢复和复制的需要，对InnoDB锁机制的影响

什么时候使用表锁

关于死锁

具体的可以查看我的另外一篇博客，下面只摘取部分介绍！

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MyISQM表锁

MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

MyISAM在执行查询语句(SELECT) 前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、 DELETE、INSERT等)前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCKTABLE命令给MyISAM表显式加锁。

一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也是MyISAM不太适合与有大量更新和查询操作应用的原因。

无死锁！

InnoDB锁问题

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

事务的ACID属性不予介绍。

并发事务带来的问题：脏读、不可重复读、幻读和更新丢失。

下面主要介绍一下更新丢失。

更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了由其他事务所做的更新。

案例：

两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。

事务隔离级别

数据库实现事务隔离的方式，基本上可分为以下两种。

读取数据前，对其加锁，阻止其他事务对数据进行修改。
不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。

可以通过检查 innodb_row_lock 状态变量来分析系统上的行锁的争夺情况：mysql> show status like 'innodb_row_lock%';

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

InnoDB的行锁模式

InnoDB实现了以下两种类型的行锁。

共享锁(S) ：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

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

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁(IntentionLocks)，这两种意向锁都是表锁。

意向共享锁(IS) ：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁(IX) ：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

注意：

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句， InnoDB会自动给涉及数据集加排他锁（X）；对于普通SELECT语句，InnoDB不会加任何锁。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。

InnoDB行锁分为3种情形。

Record lock：对索引项加锁。
Gap lock：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。
Next-key lock：前两种的组合，对记录及其前面的间隙加锁。

InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样！

在实际应用中，要特别注意InnoDB行锁这一特性，否则可能导致大量的锁冲突，从而影响并发性能。下面通过一些例子来说明。

在不通过索引条件查询时，InnoDB会锁定表中的所有记录。
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB也会对所有记录加锁。

Next-Key锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的Next-Key锁。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用Next-Key锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用Next-Key锁！

如下，假设emp表共101条记录，其empid的值分别是1、2、…、100、101：

-- 会对empid大于100（包括并不存在的）“间隙”加锁
select * from emp where empid > 100 for update;
-- 会对empid=102的这条记录加锁，尽管这条记录不存在
select * from emp where empid = 102 for update;

恢复和复制的需要，对InnoDB锁机制的影响

MySQL通过BINLOG记录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制。

对于insert into target_tab select * from source_tab where ...和create table new_tab ... select .. from source_tab where...，这种SQL语句，用户并没有对source_tab做任何更新操作，但MySQL对这种SQL做了特别处理，对source_tab加了共享锁，没有使用多版本数据一致性读技术！

所以，INSERT…SELECT…和CREATE TABLE…SELECT…语句，可能会阻止对源表的并发更新。如果查询比较复杂，会造成严重的性能问题，读者在应用中应尽量避免使用。实际上，MySQL将这种 SQL叫做不确定（non-deterministic）的 SQL，属于“Unsafe SQL”，不推荐使用。

什么时候使用表锁

在InnoDB下，使用表锁要注意以下两点。

表锁不是由 InnoDB 存储引擎层管理的，而是由其上一层——MySQL Server 负责的，仅当autocommit=0，innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
在用LOCK TABLES对 InnoDB表加锁时要注意，要将AUTOCOMMIT设为 0，否则MySQL不会给表加锁；事务结束前，不要用 UNLOCK TABLES释放表锁，因为 UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。