摘要
本文为数据库优化系列文章的第三篇文章 :《数据库优化——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的最大不同有两点:
- 支持事务(Transaction)
- 采用了行级锁
1. 背景知识
- 事务及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元,事务具有如下的属性
1. 原子性
事务是数据库操作的最小单位,其中的SQL语句要不全部执行,要不全部不执行
2. 一致性
> 事务开始和完成时,数据必须保持一致状态,也就是说,如果事务是并发多个,系统也必须如同串行事务一样操作。
> 这类似于分布式系统中的同步问题
3. 隔离性
数据库系统保证事务不受外部并发操作影响,也就是说,事务处理过程的中间状态是不可见的
4. 持久性
事务对于数据的修改是永久性的,即使系统出现故障也不会丢失
- 并发事务处理带来的问题
并发大大提高了系统的处理速度和吞吐量,但是也存在着数据同步等诸多问题:
- 更新丢失
感谢分布式系统课程,让我知道了有串行等价这个玩意,大概意思就是系统中两个原子操作在并发环境下执行所得到的结果,应当与串行环境下一致,这样才可以说是串行等价的,也就是安全的
举个栗子,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
- 事务隔离等级
幸运的是,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有以下两种行锁
共享锁(S):允许一个事务读取一行,阻止其他事务获得相同数据集的排他锁
排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁
同时,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还提供两种内部使用的意向锁,它们都是表锁
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁(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