锁是计算机协调多个进程或线程并发访问某一资源的机制。
因为数据也是一种供许多用户共享的资源,如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素,所以进一步学习 MySQL,就需要去了解它的锁机制。
本文主要记录学习了 MyISAM 和 InnoDB 这两个存储引擎,而且更加关注的是 InnoDB(因为经常用?)
MySQL 锁概述:
相对其他数据库而言,MySQL 的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。
比如,MyISAM 和 MEMORY 存储引擎采用的是表级锁(table-level locking);BDB 存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB 存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。 MySQL 这 3 种锁的特性可大致从开销、加锁速度、死锁、粒度、并发性能几点进行归纳。
①:表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
②:行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
③:页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如 Web 应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。由于 BDB 已经被 InnoDB 取代,即将成为历史(所以现在基本都在使用 InnoDB 存储引擎)。
MyISAM 存储引擎只支持表锁,这也是 MySQL 开始几个版本中唯一支持的锁类型。
mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 4 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 4 |
| Table_open_cache_misses | 8 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
5 rows in set (0.00 sec)
如果 Table_locks_waited 的值比较高,则说明存在着较严重的表级锁争用情况。
MySQL 中的表锁兼容性:
请求锁模式矩阵结果表示是否兼容当前锁模式 | None | 读锁 | 写锁 |
---|---|---|---|
读锁 | 是 | 是 | 否 |
写锁 | 是 | 否 | 否 |
也就是说,在 MyISAM 读模式下,不会阻塞其它用户的同一表读操作,但是会阻塞写操作;而在写模式下,会同时阻塞其它用户同一表的读写操作。
新建一个 user 表,引擎是 MyISAM:
mysql> desc user;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| address | varchar(60) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
session A | session B |
---|---|
获得 user 表的锁锁定mysql> lock table user write;Query OK, 0 rows affected (0.00 sec)mysql>select * from user;Empty set (0.00 sec)mysql> insert into user(id, name, age, address) values(1, 'test', 18, 'test address');Query OK,1 row affected (0.02 sec) | |
mysql> select * from user\G被阻塞了,一直卡住在这,没有返回结果 | |
mysql> unlock tables;Query OK, 0 rows affected (0.00 sec) | 等待 |
mysql> select * from user\G********** name: testage: 18address: test address1 row in set (5 min 29.61 sec) |
可以看出,通过 lock table user write 将 user 表锁住后,其它用户进行对该表操作时,都会被阻塞。
在用 LOCK TABLES 给表显式加表锁时,必须同时取得所有涉及到表的锁,并且MySQL 不支持锁升级。也就是说,在执行 LOCK TABLES 后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。
其实,在自动加锁的情况下也基本如此,MyISAM 总是一次获得 SQL 语句所需要的全部锁。这也正是 MyISAM 表不会出现死锁(Deadlock Free)的原因。
session A | session B |
---|---|
获得 user 表的读锁定mysql> lock table user read;Query OK, 0 rows affected (0.00 sec) | |
mysql> select * from user where id = 1 \G中从查询速度中可以看出,sessionB 并没有被阻塞1 row in set (0.00 sec) | |
由于没有获取 order 表的读锁定,所以不能查询 order 表。mysql> select * from order;ERROR 1100 (HY000): Table 'order' was not locked with LOCK TABLES | 但是 session B 可以访问ode r表,不阻塞。mysql> select * from order;Empty set (0.00 sec) |
获得读锁定时,不能进行写操作mysql> update user set name = 'wahaha' where id = 1;ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated | 其它 session 进行更新操作时,会被阻塞mysql> update user set name = 'wahaha' where id = 1;等待ing |
释放锁mysql> unlock tables;Query OK, 0 rows affected (0.00 sec) | 等待 |
mysql> update user set name = 'wahaha' where id = 1;Query OK, 1 row affected (1 min 6.43 sec) |
MyISAM 表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。MyISAM 存储引擎有一个系统变量 concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为 0、1 或 2。
MyISAM 存储引擎的读锁和写锁是互斥的,读写操作是串行的。 但它认为写锁的优先级比读锁高,所以即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前! 这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。 可以通过一些设置来调节 MyISAM 的调度行为。
虽然上面 3 种方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用(如用户登录系统)中,读锁等待严重的问题。
另外,MySQL 也提供了一种折中的办法来调节读写冲突,即给系统参数 max_write_lock_count 设置一个合适的值,当一个表的读锁达到这个值后,MySQL 就暂时将写请求的优先级降低,给读进程一定获得锁的机会。 上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点:一些需要长时间运行的查询操作,也会使写进程“饿死”!因此,应用中应尽量避免出现长时间运行的查询操作。
不要总想用一条 SELECT 语句来解决问题,因为这种看似巧妙的 SQL 语句,往往比较复杂,执行时间较长,在可能的情况下可以通过使用中间表等措施对 SQL 语句做一定的 “分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。
InnoDB 与 MyISAM 的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。行级锁与表级锁本来就有许多不同之处,另外,事务的引入也带来了一些新问题。
学习 Spring 的时候,一般通过注解 @Transitional 就能启动 spring 的事务管理,在 MySQL 中也同样支持事务的四个原则 ACID:
相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持更多的用户。但并发事务处理也会带来一些问题,主要包括以下几种情况。
数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上 “串行化”进行,这显然与“并发”是矛盾的。同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力。
4 种隔离级别比较
读数据一致性及允许的并发副作用隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|---|
未提交读(Read uncommitted) | 最低级别,只能保证不读取物理上损害的数据 | 是 | 是 | 是 |
已提交读(Read committed) | 语句级 | 否 | 是 | 是 |
可重复读(Repeatable read) | 事务级 | 否 | 否 | 是 |
可序列化(Serializable) | 最高级别,事务级 | 否 | 否 | 否 |
检查 InnoDB_row_lock 状态变量来分析:
mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
如果 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比较高,表示锁争用情况比较严重。
InnoDB 实现了一下两种类型的行锁:
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。(感觉与MyISAM 的表锁机制类似)
InnoDB 行锁模式兼容性列表:
请求锁模式矩阵结果表示是否兼容 当前锁模式 | X | IX | S | IS |
---|---|---|---|---|
X | 冲突 | 冲突 | 冲突 | 冲突 |
IX | 冲突 | 兼容 | 冲突 | 兼容 |
S | 冲突 | 冲突 | 兼容 | 兼容 |
IS | 冲突 | 兼容 | 兼容 | 兼容 |
如果一个事务请求的锁模式与当前的锁兼容,InnoDB 就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。 意向锁是 InnoDB 自动加的。 对于 UPDATE、DELETE 和 INSERT 语句,InnoDB 会自动给设计数据集加排他锁(X);对于普通的 SELECT 语句,InnoDB 不会加锁。 可以通过以下语句显示给记录集加共享锁或排他锁:
用 SELECT ... IN SHARE MODE 获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行 UPDATE 或者DELETE 操作。
但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用 SELECT... FOR UPDATE 方式获得排他锁。
所以在使用共享锁模式下,查询完数据后不要进行更新操作,不然又可能会造成死锁;要更新数据,应该使用排他锁模式。
InnoDB 行锁是通过给索引上的索引项加锁来实现的,这一点 MySQL 与 Oracle 不同,后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB 这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB 才使用行级锁,否则,InnoDB 将使用表锁!(这个问题遇到过,由于没加索引,行锁变表锁)
可以通过 explain 执行计划查看是否真正使用了索引。
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB 会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB 也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。
举个?:
假如 emp 表中只有 101 条记录,其 id 的值从 1~101,下面的 sql: select * from emp where id > 100 for update; 是范围条件查询,InnoDB 不仅会对符合条件的 id 值为101的记录加锁,也会对 id 大于101(并不存在的值)的“间隙”加锁。
结论:
很显然,在使用范围条件检索并锁定记录时,InnoDB 这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。
上面知识点说过,MyISAM 表锁是 deadlock free 的,这是因为 MyISAM 总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。 但在 InnoDB 中,除单个 SQL 组成的事务外,锁是逐步或得的,所以 InnoDB发生死锁是可能的。
举个?:
session A | session B |
---|---|
mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec)mysql> select * from table_1 where where id=1 for update;...做一些其他处理... | mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec)mysql> select * from table_2 where id=1 for update;... |
select * from table_2 where id =1 for update;因session_2已取得排他锁,等待 | 做一些其他处理... |
mysql> select * from table_1 where where id=1 for update;死锁 |
也就是我们死锁产生的条件,互相持有资源不释放,还有环形等待。
发生死锁后,InnoDB 一般都能自动检测到,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务。 但在涉及外部锁,或涉及表锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。 需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。
对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行 ROLLBACK 释放获得的排他锁。
这是一篇学习文章,关于 MySQL 的锁机制又多了几分了解,以后在写SQL和排查问题时候,尽量避免死锁和更快定位问题所在。
来源:掘金 原文:http://t.cn/AiCOuqnm 题图:来自谷歌图片搜索 版权:本文版权归原作者所有