MYSQL锁相关

MySQL锁概述：

相对于其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。
1，MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);
2，BDB存储引擎使用的是页面锁(page-level locking)，但也支持表级锁；
3，InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下使用的是行级锁。
事务加锁方式
一次性锁协议：不会死锁
两阶段锁协议：会死锁

各种锁的特点和性能：
-表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
-行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
-页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

标题MyISAM表锁

MySQL表级锁有有两种模式:
表共享读锁(Table Read Lock) 和 表独占写锁(Table Write Lock)

共享读锁：

共享写锁：

查询表级锁争用情况：通过：show status like ‘table%’，如果Table_locks_waited的值比较高,则说明存在比较严重的表级锁争用情况

加锁方式：myisam会在执行select前给sql加读锁，在update或者insert及delete前加写锁，不需要手动加入；
不支持锁升级，及只能查询加锁的表不能访问未加锁的表，同时读锁只能查询；MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。
并发插入
MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2

• 当concurrent_insert设置为0时，不允许并发插入。
• 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
• 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

MyISAM的锁调度
myisam存储引擎的读写锁是互斥的，且写优先获取锁对象。基于这种情况可能会出现读锁获取阻塞现象。基于这种情况情况，可以通过设置参数，实现锁调度。

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

InnoDB锁

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

• 更新丢失：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他 - 事务的存在，就会发生丢失更新问题；
• 脏读：一个事务修改中未提交的数据被其它线程读取到，造成数据不一致的问题；
• 不可重复读：一个事务第一次读取到数据，由于此时其它线程修改了数据，造成第二次读取的数据不一致 了；
• 幻读：一个事务第一次查询数据后，由于其它事务插入了满足条件的数据，第一个事务第二次读取到多出的数据。

事务的隔离级别

查看mysql的当前事务隔离级别：SELECT @@tx_isolation;

MVCC
通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。
在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外)

select * from table where ?; 

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

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 ?; 

获取InonoD行锁争用情况
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

InnoDB的行锁模式及兼容性

• 共享锁（s）又称读锁：A事务读取当前数据，准许其它事务数据读取但不准修改。
• 排他锁（x）又称写锁：A事务获取了当前数据的写锁，其它事务不能做任何操作。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，没有索引的情况下，InnoDB只能使用表锁。

间隙锁（Next-Key锁）
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

总结

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

对于InnoDB表，本文主要讨论了以下几项内容：
（1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
（2）介绍了InnoDB间隙锁（Next-key)机制，以及InnoDB使用间隙锁的原因。
在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
选择合理的事务大小，小事务发生锁冲突的几率也更小；
给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。