MySQL锁机制

MySQL中主要有两种锁：行级锁和表级锁：
行级锁(row-level)：特点是锁定对象的粒度小，发生锁定资源争用的概率也小，能够给予应用程序尽可能大的并发处理能力，从而提高一些需要高并发应用系统的整体性能。
但是行级锁定也有不少弊端，由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要的操作就更多,带来的消耗自然也就更大了，此外,行级锁定也最容易发生死锁。

表级锁(table-level)：该锁定机制最大的特点是实现逻辑非常简单,带来的系统处理成本最小,所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定,所以很好地避免困扰我们的死锁问题。当然，锁定颗粒度大带来最大的负面影响就是出现锁定资源争用的概率也会最高,致使并发性大打折扣。

表级锁有两种模式：
在MySQL中，常见存储引擎有myisam和innodb，其中myisam采用的是表级锁，而innodb采用的是行级锁。下面分别说说这两种存储引擎的锁机制。

myisam：采用表级锁，实现了表共享锁、表独占锁两种模式的表级锁。对于表的读请求，不会阻塞其他用户对同一表的读请求，但是会阻塞对同一表的写请求；
而对myisam的写操作，会阻塞其他用户对同一表的读、写操作。myisam表的读、写操作之间是串行的。

myisam的加锁：在执行select操作前，会自动给涉及的所有表加读锁；在执行更新操作（update、delete、insert）前，会自动给涉及的表加写锁，这个过程不需要用户干预。

myisam的锁调度：之前说过，myisam的读锁和写锁是互斥的、串行的，一个进程请求myisam表的读锁时，此时另一个线程请求同一表写锁，这时候mysiam怎么调度？
答案是：写进程优先获得锁，不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁前面，这是因为myisam认为一般写请求比读请求重要。
也正是这样，myisam不太适合有大量更新和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

myisam的并发插入：myisam也支持查询、插入并发进行。
当current_insert = 0时，不允许并发插入
当current_insert = 1时，如果myisam表没有空洞（表的中间没有被删除的行），允许在一个用户select时，另一个用户在表尾插入记录。这也是myisam的默认设置。
当current_insert = 2时，无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

innodb：与myisam最大的区别有两点，一是innodb支持事务，二是innodb采用了行级锁。在innodb中，默认的事务隔离级别是repeatable（在oracle中是read commited）
innodb实现事务的隔离级别有两种实现方式：
一种是读取数据时对其加锁，阻止其他事务对数据进行修改（repeatable）。
另一种是不用任何加锁，而是通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照提供一定级别的（语句级或事务级）的一致性读取，
从用户的角度看，好像数据库提供了统一数据的多个版本，因此这种技术叫做多版本并发控制（MultiVersion Concurrency Control,简称MVCC）
MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。

innodb实现了两种类型的行锁：
共享锁(S)：允许其它事务获取相同数据的共享锁，阻止其他事务获得相同数据集的排他锁。
排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁，这里不做研究。

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？在InnoDB中：
快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select * from table where ?;

普通select语句是不会被阻塞的，不管数据加了什么锁。

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。
其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

InnoDB事务获得共享锁和排他锁的方式:

共享锁:select * from table_name where … lock in share mode.
排他锁:select * from table_name where … for update.

InnoDB行锁实现方式：InnoDB行锁是通过给索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应的数据行来实现的。
InnoDB这种行锁实现特点意味着：在写数据时只有通过索引条件检索数据，InnoDB才使用行锁,否则，InnoDB将升级使用表锁，然后在MySQL Server层中过滤，
将不满足条件的row unlock掉。

InnoDB的间隙锁(Next-Key锁)：当我们写数据时检索到不存在的键值（不管是范围检索，还是等值检索），那么这个键值（不存在）左右的空白区域（直到遇到存在的键值）都会被加上间隙锁,
其它事务想插入加上间隙锁的记录时，会被阻塞。
如：表里有id=1,2,10的记录，update table set xx where id <= 10，这时，id=3到id=9会被加上间隙锁，11以后也会加上间隙锁（这里不是很明白，只要触发了边界，边界之后都会加上间隙锁）。
表里有id=1,2,10的记录，update table set xx where id = 7，这时，从3-6,8-9都会被加上间隙锁。

InnoDB使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,另外一方面,是为了满足其恢复和复制的需要。
由于MySQL的恢复机制(复制其实就是在slave mysql不断做基于binlog的恢复)，如果不使用上述的间隙锁，可能到slave那边的binlog就会不正常。
这种sql被称为不确定(non-deterministic)的SQL语句。这个跟mysql的系统变量关:innodb_locks_unsafe_for_binlog(默认是off).
所以禁用如下语句:

insert into target_tab select  * from source_tab where …
create table new_tab … select … from source_tab where …

小结：
对于MyISAM表,主要讨论了以下几点.
(1)共享读锁(S)之间是兼容的,但共享读锁(S)与排他锁(X)之间,以及排他锁(X)之间是互斥的,也就是说读和写是串行的.
(2)在一定条件下,MyISAM允许查询和插入并发执行,我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题.
(3)MyISAM默认的锁调度机制是写优先,这并不一定适合所有应用,用户可以通过设置LOW_PRIORITY_UPDATES参数,或在Insert,update,delete语句中指定LOW_PRIORITY选项来调节读写锁的作用.
(4)由于表锁的锁定粒度大,读写之间又是串行的.因此,如果更新操作较多,MyISAM表可能会出现严重的锁等待,可以考虑采用InnoDB表来减少冲突。

对于InnoDB表,主要讨论以下几项内容
*InnoDB的锁是基于索引实现的,写数据时如果不通过索引访问数据,InnoDB会使用表锁。
*介绍了InnoDB间隙锁(Next-Key)机制,以及InnoDB使用间隙锁的原因。
*在不同的隔离级别下 ,InnoDB的锁机制和一致性读策略不同
*锁冲突甚至死锁很难完全避免
在了解InnoDB锁特性之后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁,包括:
*尽量使用较低的隔离级别;
*精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会;
*选择合理的事务大小，小事务发生锁冲突的几率也更小。
*不同的程序访问同一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样大大减少死锁的机会。