学习总结--MYSQL 锁

MYSQL锁按特性分为3种：

行级锁：InnoDB引擎（也支持表级锁，默认是行级锁），开销大，加锁慢；会出现死锁。锁定粒度最小，发生锁冲突的概率最低，并发度最高。

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

页面锁：BDB引擎（也支持表级锁），开销和加锁时间介于行锁和表锁之间；会出现死锁；粒度也介于行锁和表锁之间，并发度一般。

MyISAM表锁：

查询表锁的争用情况：可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上表锁的争夺。

mysql> show status like 'talbe%';
+------------------------------+-------+
| Variable_name                | Value |
| Table_locks_immediate        | 2979  |
| Table_locks_waited           | 0     |
+------------------------------+-------+

如果Table_locks_waited的值比较高，说明存在较严重的表级锁争用情况

MySQL的表锁有2种模式：表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。锁模式的兼容性如下：

当前锁模式\是否兼容\请求锁模式	None	读锁	写锁
读锁	是	是	否
写锁	是	否	否

注释：对MyISAMySQL表的读操作，不会阻塞其他用户对同一表的读请求，但是会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写的操作；MyISAM表的读操作和写操作之间，以及写操作之间是串行的。即当一个线程获得对一个表的写操作之后，只有持有锁的线程可以对表进行更新操作，其他线程的读、写操作都会等待直到锁被释放为止。

MyISAM在执行select之前会自动给涉及的表加读锁，在执行更新操作（Update、Insert、Delete等）之前会自动给涉及的表加写锁。也可以用LOCK TABLE命令给表显式加锁。示例如下：

Lock tables order read local, order_detail read local;
//local选项，作用是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录

MyISAM表的并发插入：

MyISAM表的读和写是串行的。在一定条件下MyISAM表业支持插入和查询操作的并发进行。MyISAM存储引擎中有一个系统变量concurrent_insert（默认值为1），专门用来控制其并发插入的行为：

当concurrent_insert值为0时，不允许并发插入

当concurrent_insert值为1时，如果MyISAM表中没有空洞（即表中间没有被删除的行），MyISAM允许在一个进程读表的同时，灵位一个进程从表尾插入记录。

当concurrent_insert值为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

MyISAM表的锁调度：

MyISAM存储引擎中的读锁和写锁是互斥的，读写操作是串行的。那么一个进程请求某被MyISAM表的读锁，另一个进程同时请求一个表的写锁，那么写进程优先获得锁。即使读锁先请求，写锁也会插入到读锁请求之前！这个也是MyISAM不适合大量更新操作的原因。但是设置可以更改MyISAM的调度行为：

a) 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求优先的权利

b) 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低

c) 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级

d) MYSQL 提供了一个系统参数max_write_lock_count，通过设置适当的值，当一个表的读锁达到这个值之后，MySQL就暂时将写请求优先级降低。

InnoDB锁问题

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

事务：

1.事务及其属性：

事务是由一组SQL语句组成的逻辑处理单元，具有以下4个特性（ACID）：

a) 原子性（Atomicity）:事务是一个原子操作单元，要么全部执行，要么全部不执行

b) 一致性（Consistent）:事务在开始和完成时，数据都必须保持一致状态。保持数据的完整性

c) 隔离性（Isolation）:数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

d) 持久性（Durable）:事务完成之后，它对数据的修改是永久性的。

  2.并发事务处理带来的问题

a)更新丢失（Lost Update）：当多个事务选择同一行数据，由于每个事务都不知道其他事务的存在，会导致最后更新覆盖其他事务所做的更新，发生丢失更新问题。

b)脏读（Dirty Reads）：一个事务正在对一条数据进行修改，这个事务完成并提交之前，这条记录的数据就处于不一致的状态；这使另外一个事务也来读取同一天记录如果不加控制，就会读取这些“脏”数据。

c)不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生改变或者被删除了。这个就叫不可重复读。

d)幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这个就叫做幻读。

3.事务的隔离级别

由于并发事务会产生一些问题，可以通过实现事务隔离的方式里解决。基本上分一些两种：

a)一种是在读取数据之前对其加锁，防止其他事务对数据进行修改

b)另外一种是不加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（snapshot），并用这个快照来提供一定级别（语句级或者事务级）的一致性读取。这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control,简称MVCC或MCC），也常称为多版本数据库。

4种隔离级别的比较：

隔离级别\读数据一致性及允许的并发副作用	读数据一致性	脏读	不可重复读	幻读
未提交读（Read uncommitted）	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读（Read comitted）	语句级	否	是	是
可重复读（Repeatble read）	事务级	否	否	是
可序列化（Serializable）	最高级别，事务级	否	否	否

获取InnoDB行锁的争用情况：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Vale  |
| InnoDdb_row_lock_current_waits| 0     |
| InnoDdb_row_lock_time         | 0     |
| InnoDdb_row_lock_time_avg     | 0     |
| InnoDdb_row_lock_time_max     | 0     |
| InnoDdb_row_lock_waits        | 0     |
+-------------------------------+-------+

注释：如果锁争用的情况比较严重，InnoDb_row_lock_waites和InnoDB_row_lock_time_avg的值比较高，可以通过查询information_schem数据库中相关的表来查看锁情况，或者通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的情况。

InnoDB的行锁模式以及加锁方式：

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

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

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

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

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

上述锁模式的兼容情况如下：

当前锁模式\是否兼容\请求锁模式	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

注释：如果一个事务的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务。反之，如果两者不兼容就要等待锁释放。

意向锁是InnoDB自动加的。对于Update、DELETE、INSERT语句，InnoDB会自动的给涉及的数据集加排它锁（X）；对于普通SELECT，InnoDB不会加任何锁。

InnoDB行锁实现方式：

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

a) Record Lock: 对索引项加锁

b) Gap lock: 对索引项之间的“间隙”、第一天记录前的“间隙”或者一条记录后的“间隙”加锁。

c) Next-key lock: 前两种的组合，对记录以及其前面的间隙加锁。

在实际应用中要注意：

1）在不通过索引条件查询时，InnoDB会锁定表中的所有记录。因为在没有索引的情况下，InnoDB会对所有的记录加锁。

2）由于MySQL的行锁是针对索引加的锁，不受针对记录加的锁，所以虽然访问的不同的记录，但是如果是使用相同的索引键，是会出现索引冲突的。

3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同行，不论是使用主键索引、唯一索引或普通索引InnoDB都会使用行锁来对数据加锁。

4）

InnoDB在不同隔离级别下的一致性读及锁的差异

对于很多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格，产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响就越大。

死锁

如何避免死锁？

1）在应用中如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以达到降低死锁的机会。

2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按照固定的顺序来处理程序，也可以大大降低出现死锁的可能。

3）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁。而不应该先申请共享锁再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

4）在REPEATABLE-READ隔离级别下，如果两个线程同时对相同记录用SELECT……FOR UPDATE加排他锁，在没有符合该条件记录的情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新纪录，如果两个线程都这么做就造成死锁。解决方式：将隔离级别改为READ COMMITTED。

5）当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT……FOR UPDATE，判断是否存在符合条件的记录，如果没有就插入记录。此时，只有1个线程能插入成功，另一个会出现锁等待，当第一个线程提交后，第二个线程会因主键重复出错。虽然这个线程出错了，却会获得一个排他锁，这使如果第三个线程又来申请排他锁就会出现死锁。解决方式：直接做插入操作，然后再捕获主键重异常，或者在遇到主键重复错误时总是执行ROLLBACK释放获得的排他锁。

出现死锁，可以用SHOW INNODBSTATUS命令来确定最后一个死锁产生的原因。