MySQL锁机制

本文转载自：MySQL锁机制

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

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

myisam：
myisam采用的是表级锁，对于表的读请求，不会阻塞其他用户对同一表的读请求，但是会阻塞对同一表的写请求。
对myisam的写操作，会阻塞其他用户对同一表的读、写操作。
myisam表的读、写操作之间是串行的。

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

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

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

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

MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。

innodb实现了两种类型的行锁：
共享锁（S）： 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

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

InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁，这里不做研究。

update,delete,insert语句InnoDB会自动给涉及数据集加排他锁(X);对于普通select语句,InnoDB不会加任何锁.

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

共享锁:select * from table_name where … lock in share mode.

排他锁:select * from table_name where … for update.

InnoDB行锁实现方式：

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

InnoDB的间隙锁(Next-Key锁)

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁:对于键值在条件范围内但并不存在的记录,叫做”间隙锁”

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调整等措施减少锁冲突和死锁,包括:

      *尽量使用较低的隔离级别;

      *精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会;

      *选择合理的事务大小，小事务发生锁冲突的几率也更小。

      *不同的程序访问同一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样大大减少死锁的机会。