本文主要记录学习MyISAM 和 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可以访问oder表,不阻塞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来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。
这是一篇学习文章,关于MySQL的锁机制又多了几分了解,以后在写SQL和排查问题时候,尽量避免死锁和更快定位问题所在。