MySQL锁的问题
锁的概述
- 锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。
- 在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。
锁的分类
- 从对数据库操作的粒度分
- 表锁:操作时,会锁定整个表
- 行锁:操作时,会锁定当前操作行。
- 从对数据操作的类型分
- 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会相互影响
- 写锁(排它锁):当前操作没有完全之前,它会阻断其他写锁和读锁。
Mysql锁
-
相对其他数据库而言,Mysql的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。
-
存储引擎 表级锁 行级锁 页面锁 MyISAM 支持 不支持 不支持 InnoDB 支持 支持 不支持 MEMORY 支持 不支持 不支持 BDB 支持 不支持 支持
-
-
MySQL这3种锁的特性可大致归纳如下:
-
锁类型 特点 表级锁 偏向MyISAM存储引擎,开销小,加锁快;不会出现死锁;锁粒度大,发生锁冲突的概率最高,并发度低 行级锁 偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁粒度最小,发生锁冲突的概率最低,并发度最高 页级锁 开销和加锁介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般。 -
从上述特点可见,很难笼统的锁哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按所有条件更新数据的应用,如web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并查询的引用,如一些在线事务处理(OLTP)系统
-
MyISAM表锁
MyISAM存储引擎只支持表锁,这也是Mysql开始几个版本中唯一支持的锁类型。
如何加表锁
-
MyISAM在执行查询语句(SELECT)前,会自动给设计的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE 命令给MYISAM表显示加锁。
-
显示加锁语法
-
加读锁:lock table table_name read; 加写锁:lock table table_name write;
-
-
实例
-
创建表
-
create table myisam_lock( `id` int(11) unsigned not null auto_increment, `name` varchar(100) default null, primary key(`id`) )engine=myisam charset=utf8 auto_increment=1;
-
-
案例一:读操作
-
客户端一
-
获得tb_book表的读锁
-
lock table myisam_lock read;
-
查查询询 查询成功
-
select * from myisam_lock;
-
-
-
客户端二
- 执行查询操作select * from myisam_lock;也能查询成功
-
客户端一查询未锁的表
- select * from myisam_test;
ERROR 1100 (HY000): Table ‘myisam_test’ was not locked with LOCK TABLES
- select * from myisam_test;
-
客户端二查询未锁的表,能查询到结果
-
客户端一进行插入操作报错,不能插入
-
insert into myisam_lock values(1,'1'); ERROR 1099 (HY000): Table 'myisam_lock' was locked with a READ lock and can't be updated mysql>
-
-
客户端二进行插入操作,进入阻塞,当客户端一unlock tables;后,客户端二完成插入操作。
-
-
案例二:写操作
-
客户端一:获取写锁
-
lock table myisam_lock write;
-
-
客户端一执行读取成功
-
客户端一插入数据成功
-
客户端二:插入数据阻塞
-
客户端二:读取数据阻塞
-
-
-
总结
-
myisam锁模式相互兼容性
-
锁模式 None 读锁 写锁 读锁 是 是 否 写锁 是 否 否
-
-
由上表可见
- 对Myisam表的读操作,不会阻塞其他用户对同一张表的读请求,但会阻塞对同一张表的写请求
- 对Myisam表的写操作,则会阻塞其他用户对同一张表的读和写操作
- 简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁,既会阻塞写,也会阻塞读
-
此外,Myisam的读写锁调度是写优先,这也是myisam不适合做写为主的表的存储引擎的原因。因为写锁后,其他线程不能做任何操作,大量更新会使得查询很难获得锁,从而造成永久阻塞。
-
查看锁的争用情况
- 使用show open tables;命令查看锁的争用情况
-
In_user : 表当前被查询使用的次数。如果该数为零,则表是打开的,但是当前没有被使用。
-
Name_locked:表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。
-
- show status like ‘Table_locks%’;
-
Table_locks_immediate : 指的是能够立即获得表级锁的次数,每立即获取锁,值加1。
-
Table_locks_waited : 指的是不能立即获取表级锁而需要等待的次数,每等待一次,该值加1,此值高说明存在着较为严重的表级锁争用情况。
-
InnoDB行锁
行锁介绍
- 行锁特点:偏向InnoDB存储引擎,加锁开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。
背景知识
-
事务及其ACID属性
-
事务具有4种特性,简称ACID属性
-
ACID属性 含义 Atomicity(原子性) 一个事务是不可分割的,是最小的执行单位,一个事务中的所有操作要么全部成功,要么全部失败 Consistent(一致性) 事务要保证数据库从一个一致性状态,转换到另一个一致性状态。在事务开始到结束,所有数据都要保持一致 Isolation(隔离性) 数据库中一个事务的内部操作,不会被其他并发事务所影响,事务内部的数据在未结束前,对其他事务是不可见的,数据库提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境下运行 Durable(持久性) 事务一旦提交,对数据库的修改是永久的,不会因为其他事务操作而破坏,也不会因为数据库系统的宕机而到时数据的丢失。
-
-
并发事务所带来的问题
-
问题 含义 丢失更新(Lost Update) 当两个事务或多个事务选择同一行,最初的事务修改的值,会被后面的事务修改的值覆盖。 脏读(Dirty Read) 一个事务读取到了另个一事务未提交的数据。当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另一个事务也访问这个数据,然后使用了修改后的数据 不可重复读(Non-Repeatable Reads) 一个事务在前后读取同一条SQL时候,另一个事务对表进行了修改并提交,导致第一个事务两次读取到的结果不一致。一个事务在读取某些数据后的某个时间,再次读取以前读取过的数据,却发现和以前的不一致 幻读(Phantom Reads) 一个事务需要插入数据,查询后发现目前表中没有该数据可以插入,此时,另一个事务插入了前一个事务所要插入的数据,并且提交了。前一个事务在执行插入操作后,数据库报错唯一性错误,数据无法插入,但是再次读取后还是发现可以插入。
-
-
事务隔离级别
-
为了解决上述提到的事务并发问题,数据库提供一定的事务隔离机制来解决这些问题。数据库事务越严格,并发副作用越小,但是付出的代价就越大,并发的效率越低。因为事务隔离实质上就是使用事务一定程度上的“串行化”进行,这显然和“并发”是矛盾的。
-
数据库的隔离级别有4个,由低到高依次为Read Uncommitted(RU),Read Committed(RC),Repeatable Read(RR),Serializable,这四个级别可以逐个解决脏读,不可重复读,幻读这几类问题
-
隔离级别 丢失更新 脏读 不可重复读 幻读 Read Uncommitted × √ √ √ Read Committed × × √ √ Repeatable Read × × × √ Serializable × × × × -
备注 : √ 代表可能出现 , × 代表不会出现 。
-
-
Mysql默认的事务隔离级别是Repeatable Read,查看方式
- show variables like ‘tx_isolation’;
-
InnoDB行锁模式
-
InnoDB实现了两种类型的锁
- 共享锁(S):又称读锁,简称S锁,共享锁就是多个事务对同一数据读取可以共享一把锁,但是只能读不能修改
- 排他锁(X):又称写锁,简称X锁,排他锁就是不能与其他锁并存,如果一个事务获取了一个数据行的排他锁,其他事务就不能再获取这行数据的其他任何锁,包括共享锁和排他锁,但是已经获取到排他锁的事务是可以对数据行进行读取和修改的(锁的重入)
-
对于UPDATE,DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
-
对于普通SELECT语句,InnoDB不会加任何锁;
-
可以通过以下语句显示的给记录集加上共享锁或者排他锁
-
共享锁(S):select * from table_name where ... lock in share mode 排他锁(X):select * from table_name where ... for update
-
案例准备工作
create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);
行锁基本演示
Session-1 | Session-2 |
---|---|
关闭自动提交功能 set autocommit=0; | 关闭自动提交功能set autocommit=0; |
可以正常的查询出全部的数据 select * from test_innodb_lock | 可以正常的查询出全部的数据select * from test_innodb_lock |
查询id 为3的数据 ;select * from test_innodb_lock where id =3; | 获取id为3的数据 ;select * from test_innodb_lock where id =3; |
更新id为3的数据,但是不提交;update test_innodb_lock set name =‘A1’ where id=3; | 更新id为3 的数据, 出于等待状态update test_innodb_lock set name =‘A1’ where id=3; |
通过commit, 提交事务 | 解除阻塞,更新正常进行 |
以上, 操作的都是同一行的数据,接下来,演示不同行的数据 : | |
更新id为3数据,正常的获取到行锁 , 执行更新 ;update test_innodb_lock set name =‘A1’ where id=3; | 由于与Session-1 操作不是同一行,获取当前行锁,执行更新;update test_innodb_lock set name =‘C1’ where id=5; |
无索引行锁升级成表锁
-
如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样。
-
查看当前表的索引:show index from test_innodb_lock;
-
案例
-
Session-1 Session-2 关闭事务的自动提交 set autocommit=0; 关闭事务的自动提交 set autocommit=0; 执行更新语句 :update test_innodb_lock set sex=‘2’ where name = 400;(这里使用了类型转换,索引失效了,行锁升级成表锁) 执行更新语句, 但处于阻塞状态:update test_innodb_lock set sex=‘2’ where id=9; 提交事务:commit 解除阻塞,执行更新成功 : 执行提交操作 :commit; -
由于执行更新时,name字段本来为varchar类型,我们是作为数组类型使用,存在类型转换,索引失效,最终行锁变为表锁;
-
间隙锁
- 当用范围条件下不是相等条件检索数据,并请求共享或排他锁,InnoDB会给符合条件的已有记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做–“间隙”(GAP)
- InnoDB也会对这个“间隙”加锁,这种锁机制就是间隙锁(gap key)
- 危害
- 因为Query执行过程中通过范围查找的话,InnoDB会锁定整个范围内所有的索引键值,即使这个键值不存在。间隙锁有一个比较致命的缺点,就是当锁定一个范围值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定范围内的任何数据。某些场景下可能会造成很大的危害
InnoDB行锁争用情况
-
通过命令查看InnoDB行锁争用情况
-
show status like 'innodb_row_lock%';
-
-
Innodb_row_lock_current_waits:当前正在等待锁定的数量
-
Innodb_row_lock_time:从系统启动到现在锁定总时间长度
-
Innodb_row_lock_time_avg:每次等待所花平均时长
-
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次锁花费的时间
-
Innodb_row_lock_waits:系统启动后到现在总共等待的次数
-
-
当等待的次数很高,而且每次等待的时长也不小的时候,就需要分析系统中为什么会有如此多的等待,然后根据分析结果制定优化计划
总结
- Innodb存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些,但是在整体并发处理能力方面要远远优于Myisam的表锁。当系统并发量较高的时候,InnoDB的整体性能和Myisam相比就会有比较明显的优势。
- 但是,InnoDB的行级锁同样也有其脆弱的一面,当我们使用不当的时候,可能会让InnoDB的整体性能变差
- 优化建议
- 尽可能让所有数据检索都能通过索引来完成,避免无索引行锁升级成表锁
- 合理设计索引,尽量缩小锁的范围
- 尽可能减少索引条件,及索引范围,避免间隙锁(间隙锁,会导致插入阻塞)
- 尽量控制事务大小,减少锁定资源量和时间长度
- 尽可能使用低级别事务隔离(需要根据业务要求)