mysql的锁
作者:bin什么是mysql锁?
锁是协调多线程或线程并发请求同一个资源的机制,mysql的锁机制相对简单,可以笼统的分为3大类:
1、表级锁:开销小,加锁快,无死锁,粗粒度,冲突概率高,并发度能低;
2、行级锁:开销大,加锁慢,会死锁,细粒度,冲突概率低,并发度能高;
3、页面锁:开销中,加锁中,会死锁,粒度中,并发一般。
一、InnoDB锁
InnoDB锁与MyISAM最大的2个不同点,1、支持事物,2、采用行锁;
使用for upadte上锁,来做并发控制,update语句默认加了排他锁,select的结果那么默认走的是MVCC方式
1、锁的类型
1、共享锁(又叫读锁S):锁定行后,(其它session)可以加共享锁,(其它)不可以加排他锁:
select * from emp where id = 1 lock in share mode;
2、排他锁(又叫写锁X):锁定行后,(其它)不可以加共享锁,(其它)不可以加排他锁:
select * from emp where id = 1 for update;
2、表锁
表锁是mysql server级别的锁,innoDB引擎中是row lock,可以使用如下命令锁一个表(read/write):
lock table t write; //加X锁 unlock tables; //解锁
3、行锁
值得注意对是,如果innoDB不通过索引加锁,那就需要遍历所有数据,那么innoDB将对所有遍历过的记录进行加锁,实现效果和表锁一样了。
我们使用以下表来做演示,便于理解
CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 insert into test values(1,1); insert into test values(2,2); insert into test values(4,4); insert into test values(5,5); insert into test values(7,7); insert into test values(10,10); insert into test values(11,11);
加锁的原则:
1.默认加锁都是next-key锁,范围加锁时,就是若干个连续的next-key锁,如(7,10]
2.在唯一索引时,next-key锁会降级为record锁。如[7]
3.如果遍历到到值与查找到值不相等,那么next-key,就会降级为gap锁。如(7,10)
行锁类型:
next-key锁:默认的锁,即record + gap合并,锁一个范围,并且会把当前记录也锁进去,左开右闭(7,10]
select * from t where name = 10
record锁:锁定当前行,例如锁的是一个唯一索引时,next-key降级为record锁[7]
select * from t where id = 7
gap锁:锁2个确定值的间隙,例如找不到与查找值相等的值,就由next-key降级为gap锁(7,10)
select * from t where id = 8
next-key锁 + gap锁的验证
锁7记录,但是通过name(不唯一)去锁,找到值与where相等即7,那么给7上next key(5,7]锁,然后因为不是唯一索引的,后面可能还有7,如果还有一个7,那就又一个next key,但是继续往后找到的是10不是7,就将next key降级为gap(7,11)锁,锁住的就是(5,7] + (7,11)
MySQL [sessionA]> select * from test where name = 7 for update; +----+------+ | id | name | +----+------+ | 7 | 7 | +----+------+
我们会发现7的前后,包括7都被锁住了,
MySQL [sessionB]> insert into test set id = 6, name = 77; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MySQL [sessionB]> insert into test set id = 8, name = 88; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MySQL [sessionB]> update test set name = 77 where id = 7; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction //5和10未被锁 MySQL [sessionB]> mysql> update test set name = 100 where id = 5; Query OK, 1 row affected (0.01 sec) MySQL [sessionB]> update test set name = 1111 where id = 10; Query OK, 1 row affected (0.00 sec)
gap锁验证
如果锁定不存在都记录8:
MySQL [test]> select * from test where id = 8 for update; Empty set (0.00 sec)
那么会将间隙锁起来,也是一个gap锁,会把8、9都锁起来
mysql> update test set name = "a8" where id = 7; Query OK, 1 row affected (0.00 sec) MySQL [sessionB]> insert into test set id = 8, name = 88; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MySQL [sessionB]> insert into test set id = 9, name = 99; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction //10未锁 MySQL [sessionB]> update test set name = 101 where id = 10; Query OK, 1 row affected (0.00 sec)
遍历过的记录,都会加锁
例如如下order by,即便其中name是非唯一索引,那么会使用next-key,然后使用了order by,那么就会对所有排序的记录加锁,这里只和where加锁的字段有关,和order by哪个字段无关
select * from test where name = 11 order by id desc lock in share mode;
4、意向锁
意向锁是表锁级别的锁,表示一个意向,用于解决表锁和行锁冲突的问题,在加表锁时,需要先确定表中没有加行锁才行,如果没有意向锁,那么就要遍历所有记录。
意向锁就是在加行锁前,需要对表加一个意向锁,如果已经有意向锁,那么表锁就会阻塞等待。
意向锁分2种:
意向共享锁(IS)指示一个事务打算设置共享行锁时,需要先设置此锁。
意向独占锁(IX)指示一个事务打算设置排他行锁时,需要先设置此锁。
锁兼容|冲突矩阵如下:
5、插入意向锁
插入意向锁不是上面说的意向锁,是一个gap锁,表示对间隙的插入意向
例如4-7记录之间存在5、6,两个间隙,那么事物A插入5时,如果使用next-key锁的话,会影响事物B插入6,这样并发性能很低。
为了解决这个并发问题,引入了插入意向锁,事物A插入5时,事物B同时可以插入6,互不影响。(update和delete都还是原来的常规行锁record、next-key、gap等,只有insert才有插入意向锁)
6、自增锁
通常我们定义的AUTO_INCREMENT自增字段,在进行自增的时候,也需要加锁,避免出现重复的值。
自增锁不依赖事物,如果事物A申请了id = 2,事物B申请了id =3 ,事物A回滚,事物B提交成功,
那么此时自增就到id=4了,会出现id=2这个间隙(空洞)。
主从同步时为了id数据一致,应该将binlog的格式设置称row才行。
6、死锁的出现:
2个session对同一行添加锁共享锁:
即2个共享锁都对数据进行更新,就会出现死锁,导致退出;
(session_1)mysql> select * from emp where id = 1 lock in share mode; (session_2)mysql> select * from emp where id = 1 lock in share mode; (session_2)mysql> update emp set ename = 'qi7' where id = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 (session_1)mysql> update emp set ename = 'qi7' where id = 1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
2个session对同一个行进行排他锁,进入死循环:
(session_1)mysql> select * from emp where id = 1 for update; (session_2)mysql> select * from emp where id = 2 for update; --等待 (session_1)mysql> select * from emp where id = 2 for update; (session_2)mysql> select * from emp where id = 1 for update; --等待
这样session_1和session_2都进入等待,即出现了死锁
如果遇到上面的问题(访问多个表),应该约定好通过相同的顺序进行访问,这样可以大大降低出现死锁概率;
7、查看死锁产生的原因(mysql 5.1后)
show engine innodb status\G;
mysql 5.1前:
show innoDB status;
二、myISAM的锁
1、锁状态与模式
该引擎只支持表锁,我们可以通过以下方式查看锁的争用状态
mysql> show status like 'Table_lock%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 26280 | | Table_locks_waited | 0 | +-----------------------+-------+
Table_locks_waited值比较高则说明争用严重
锁的模式:
共享锁:对MyISAM读,其它用户可读,不可写
独占锁:对MyISAM写,其它用不不可读,不可写
2、给表加锁
MyISAM在select操作时会自动加上读锁,在update、delete、insert时会自动加上写锁,我们一般不需要直接用lock table去锁表,显式去lock表时通常是为了方便调试,或者2条sql先后查寻2个表,且值要相同时,这时我们需要主动的去锁这2个表:
lock tables emp read local, emp_1 read local; select count(id) from emp; select count(id) from emp_1; unlock tables;
可以使用如下命令锁一个表(read/write):
lock table emp write;
也可以使用如下命令锁多个表:
lock tables emp read local, emp_1 read local
添加了read锁之后,所有线程都不能对这个表写(包括自己),并且当前线程,不能读写其他表。
如果通过别名访问,那么应该别名锁定:
mysql> lock table emp as e read; mysql> select * from emp as e;
3、并发插入
MyISAM有一个变量concurrent_insert;去控制并发行为:
mysql> show variables like '%insert'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | concurrent_insert | AUTO | +-------------------+-------+
0(NEVER) : 不允许并发
1(AUTO) :在表中不存在空洞时允许并发(空洞:表中间被删除行)
2(ALWAYS) : 不管有没有空洞,都允许
值得注意的是:
我们可以通过修改concurrent_insert来解决表在查询和插入时争用的问题,例如我们将concurrent_insert改为ALWAYS(2),然后定期的去整理碎片(OPTIMIZE TABLE emp),回收删除记录产生的空洞;
4、MyISAM锁的调度(⭐️)
MyISAM读写锁时互斥的,读写操作也是串行的,如果存在下面的情况:
重现:一个进行先请求读锁,另一个进程后请求写锁,并且2个都进入等待队列,那么写锁会插队到读锁前,这是因为mysql认为写比读重要;
问题:大量更新操作,会让查询操作没办法获得读锁(例如用户登陆等待很久)
解决办法:
1、设置low-priority-updates,给引擎默认给予读请求更高的优先级;
2、当前session设置low-priority-updates=1,使发出的更新请求优先级降低;
3、降低指定insert、update、delete语句的low-priority优先级;
4、给系统参数max_write_lock_count配置一个合适的值,当读锁达到这个值时,暂时降低写锁优先级;