数据库优化——MySQL锁问题

摘要

本文为数据库优化系列文章的第三篇文章 :《数据库优化——MySQL锁问题》

更多文章参见

数据库优化 :
http://blog.csdn.net/leyounger/article/details/70157087

数据库优化——常用SQL优化:
http://blog.csdn.net/leyounger/article/details/71583158

MySQL锁问题

不同存储引擎的锁机制

MyISAM => 表锁

MEMORY => 表锁

BDB（被InnoDB取代） => 页面锁+表锁

InnoDB => 行锁+表锁

不同锁的特性

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

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

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

适应场景（最好还是具体问题，具体分析）

表锁：适合查询为主，只有少量按索引更新数据的应用，如Web应用

行锁：适合有大量索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

延伸阅读 ： 《基础：OLTP 和 OLAP》

---

1. MyISAM表锁

• 查询表级锁争用情况

mysql> show status like 'table%';

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

• MySQL表级锁的锁模式

主要有表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）

锁兼容性	None	读锁	写锁
读锁	是	是	否
写锁	是	否	否

可见，MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞同一表的写请求，而写操作会同时阻塞读操作和写操作。

MyISAM的读和写是串行的

• 并发插入

上文提到过MyISAM的读和写是串行的，但这是对总体而言的，在一定的条件下，MyISAM表也支持查询和插入操作的并发进行

//MyISAM有一个系统变量concurrent_insert，它可以取0，1，2三个值

0 ： 不允许并发插入 

1 ： 如果MyISAM表中没有空洞（即表的中间没有被删除的行）
     MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录，这也是MySQL的默认设置

2： 无论MyISAM表中有没有空洞，都允许在表尾并发插入记录

• MyISAM锁优先级

永远是写锁优先！！！！

即使是写锁比读锁先入队，MyISAM还是会让写锁先执行！！！！

//大量的写操作会block读操作，因此MyISAM不适合有大量更新操作的系统

//当然，可以通过设置来调节MyISAM的调度行为

1. 指定启动参数low-priority-updates，使得MyISAM引擎默认给予读请求优先的权利

2. 通过SET LOW_PRIORITY_UPDATES=1，使得该链接发出的更新请求优先级降低

3. 通过指定INSERT, UPDATE, DELETE语句的LOW_PRIORITY属性，降低该语句优先级

上述方法要不是读优先，要不就是写优先，非常不好，不如我们设置一个threshold，来作为控制读写的trigger

//我们可以设置系统参数
max_write_lock_count

//当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一个获得锁的机会。

长时间运行的查询操作，会让写进程“饿死“，应当避免使用过长的查询语句，应当将其分解为较短的语句或使用中间表来减少锁占用的时间。

---

2. InnoDB锁问题

InnoDB和MyISAM的最大不同有两点：

1. 支持事务（Transaction）
2. 采用了行级锁

1. 背景知识

• 事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有如下的属性

1. 原子性
事务是数据库操作的最小单位，其中的SQL语句要不全部执行，要不全部不执行

2. 一致性
> 事务开始和完成时，数据必须保持一致状态，也就是说，如果事务是并发多个，系统也必须如同串行事务一样操作。

> 这类似于分布式系统中的同步问题

3. 隔离性
数据库系统保证事务不受外部并发操作影响，也就是说，事务处理过程的中间状态是不可见的

4. 持久性
事务对于数据的修改是永久性的，即使系统出现故障也不会丢失

• 并发事务处理带来的问题

并发大大提高了系统的处理速度和吞吐量，但是也存在着数据同步等诸多问题：

1. 更新丢失

感谢分布式系统课程，让我知道了有串行等价这个玩意，大概意思就是系统中两个原子操作在并发环境下执行所得到的结果，应当与串行环境下一致，这样才可以说是串行等价的，也就是安全的

举个栗子，value原值等于1，A将value增加2，B将value增加3

串行环境下：value的值应该为6

事务 A	事务 B
BEGIN	–
UPDATE value FROM 1 TO 3
value = 3	BEGIN
–	UPDATE value FROM 3 TO 6
–	value = 6

-

并发环境下：出现更新丢失

事务 A	事务 B
BEGIN	–
UPDATE value FROM 1 TO 3	BEGIN
value = 3	UPDATE value FROM 1 TO 4
–	value = 4

2.脏读（脏数据）http://baike.baidu.com

3.不可重复读 http://baike.baidu.com

4.幻读 http://baike.baidu.com

• 
  
• 事务隔离等级
  

幸运的是，InnoDB提供了事务隔离等级来解决并发带来的一致性问题
详细介绍看百度百科就好
baike.baidu.com

一般来说，用Read Committed级别就够了

---

2. 获取InnoDB行锁争用情况

//使用如下命令检查系统上的行锁的争夺情况
mysql> show status like 'innodb_row_lock%';

//我们需要重点关注这两项
InnoDB_row_lock_waits
InnoDB_row_lock_time_avg

//如果它们的值很高，说明行锁争用频繁，我们可以设置InnoDB Monitors来进一步观察锁冲突的表、数据行等，并分析争用的原因
mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

//然后通过下面语句来查看
mysql> SHOW INNODB status;

//监视器通过下列语句来停止查看
mysql> DROP TABLE innodb_monitor

//监视器会在show innodb status的内容中记录当前锁等待的详细信息

//注意！在用户确认问题原因后，记得删除监控表以关闭监视器，否则会导致.err文件巨大

---

3. InnoDB行锁模式及加锁方法

InnoDB有以下两种行锁

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

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

同时，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还提供两种内部使用的意向锁，它们都是表锁

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

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

锁之间的兼容性列表

当前锁与请求锁是否兼容	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	冲突
IS	冲突	兼容	兼容	兼容

如果一个事务请求的锁模式与当前的兼容，InnoDB将授予锁于该事务

若是不兼容，该事务就要等待锁释放

意向锁是InnoDB自动加的，不需要用户干预。对于UPDATE, DELETE, INSERT语句，InnoDB会自动添加排他锁；

对于普通SELECT语句，InnoDB不会添加任何锁，事务可以通过以下语句显式的给记录集加共享锁或排他锁

//共享锁
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

//排他锁
SELECT * FROM table_name WHERE ... FOR UPDATE;

用SELECT … IN SHARED MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。

但是，如果当前事务需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT … FOR UPDATE方式获得排他锁。

---

4. InnoDB行锁实现方式

InnoDB是通过给索引上的索引项加锁来实现的

划重点了哈！！！

这意味着，只用通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！！！！

要特别注意InnoDB行锁的这一特性，否则可能导致大量的锁冲突！！

(1) 在不通过索引条件查询时，InnoDB确实使用表锁，而不是行锁

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

(3) 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，也就是说，InnoDB会根据不同的索引锁定相同的行；
    另外，不论是使用主键索引、唯一索引或者普通索引，InnoDB都会使用行锁来对数据加锁

(4) 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同的执行计划的代价来决定的， 如果MySQL认为全表扫描的效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁

(5) 索引类型与比较类型不一致时，MySQL会进行类型转换，此时不能使用索引
比如对于t varchar(5)有索引Index
select * from table where t = 1; //不适用索引，因为1为int型，类型转换后无法进行索引
select * from table where t = '1'; //适用索引

---

5. 间隙锁（Next-Key 锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给该范围内的所有数据都加锁，举个栗子，假如emp表中只有101条数据，考虑下面的SQL语句:

SELECT * FROM emp WHERE empid > 100 for update;

此时，对于empid大于101（实际上并不存在）的间隙也会被加上锁

InnoDB这么做是为了防止幻读，但是，间隙锁会造成严重的锁等待。因此，实际开发中应当尽量使用相等条件来访问更新数据

注意，如果使用相等条件给一个不存在的记录加锁，InnoDB也会使用间隙锁

---

6. 关于死锁

上文曾经提到，MyISAM是不存在死锁的，因为总是一次性的获得所需的全部锁。而InnoDB除单个SQL组成的事务外，锁是逐步获得的，因此可能发生死锁

发生死锁后，InnoDB一般能够自动检测并且使一个事务释放锁并回退，但在涉及外部锁或表锁时，InnoDB需要通过设置超时参数innodb_lock_wait_timeout来解决。需要注意的是，这个参数还可以用来解决在大并发条件下，大量锁等待造成的性能问题。

//大部分的死锁问题都可以通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句来避免。接下来介绍几种避免死锁的常用方法：

(1) 如果不同的程序会并发存取多个表，应尽量越大以相同的顺序来访问表。

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

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

(4) 尽量选择较低的隔离级别。在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件的记录用SELECT...FOR UPDATE加排他锁，在没有记录符合时，两个线程都会加锁成功，此时，线程会尝试插入一条新记录，如果两个线程都这么做，就会死锁。解决办法是将隔离级别改成READ COMMITED