mysql 锁机制
表锁:InnoDB 支持行锁,不代表着就不支持表锁
-
表锁的 S 和 X 锁,MyISAM 的锁(不建议在 InnoDB 中使用)
-- 查看当前有表锁的表 show open tables where in_use > 0; -- 给表加 S 锁(所有事务都能读,所有事务都不能写) lock tables table_name read; -- 给表加 X 锁(拿到锁的事务可写可读,别的事务不能读也不能写) lock tables table_name write; -- 释放锁 unlock tables;
-
意向锁
- 如果 T1 给一行数据加了 X 锁,T2 想给表加锁?只能等 T1 结束 T2 才能成功。具体做法是:遍历每行数据看是否有 X 锁,如果都没有就给表加锁,如果有就阻塞
- 如果表数据量太大,这样性能就不高了,于是有了意向锁
- 如果有行锁,自动给表添加对应的锁
- 意义: 做表锁的时候不需要挨行去扫描行锁
-
自增锁:维护自增主键
-
元数据锁
- 也叫 MDL 锁,是个排他锁,alter 表结构的时候如果不持有 mdl 锁就会阻塞
- 意义:保证查询修改时和表结构是一致的
- 查询数据时,也会给表上意向锁,这时更新表结构就会阻塞,因为 mdl 锁是排他锁,意向锁没释放是不能获取到元数据锁的
行锁( record lock )
- 记录锁,存储引擎层实现,InnoDB才有
- 并发能力强,但是维护锁的消耗变大,会出现死锁
- 也有 S 和 X 锁,如果根据非索引列更新,会升级为表锁
- 增删改会自动加 X 锁,查询自动加 S 锁(查询也能手动指定为 X 锁)
- 一行记录有了 S 锁,别的事务还是能获取 S 锁,但是不能获取 X 锁
- 一行记录有了 X 锁,别的数据不能获取 S 和 X 锁
- commit; 提交事务之后会释放锁
间隙锁( gap lock)
-
是一种特殊的行数,自然也是 X 锁(排他锁)
-
删除和更新是普通的行数,如果是插入,这条数据原来不存在,给那行记录加锁呢?给间隙加锁!
-
目的是避免幻影记录即解决幻读
-
给不存在的记录加锁,这个锁就是间隙锁,该条记录的前后作为区间,开区间,锁区间不锁边界
-- 数据库存在数据:id = 1, id = 3, id = 8; -- 没有的 id=4 的记录,这就是间隙锁,具体区间是值的最近的两个值之间,这里就是(3,8) BEGIN; SELECT * FROM USER WHERE ID = 4 FOR UPDATE; -- 另一个事务,插入这个区间的值会阻塞(3和8之间的都不能插入) BEGIN; INSERT INTO USER (ID, NAME) VALUES (5, 'Marry'); -- 这个间隙锁的区间就是 (8, 正无穷) BEGIN; SELECT * FROM USER WHERE ID = 10 FOR UPDATE;
临键锁( next-key lock )
-
间隙锁的特殊形式,包括边界
-
间隙锁是锁区间不锁边界,临键锁就是既锁区间也锁边界
-
也可以理解为普通的行锁+间隙锁
-- 数据库存在数据:id = 1, id = 3, id = 8; -- 给 (3,8]这个区间加 X 锁,这就是临键锁 BEGIN; SELECT * FROM USER WHERE ID > 3 AND ID <=8 FOR UPDATE; -- 这时也是不允许插入的 BEGIN; INSERT INTO USER (ID, NAME) VALUES (5, 'Marry');
乐观锁悲观锁
- 悲观锁数据库层面做的,比如扣减库存,查询和更新都加 X 锁来保证并发安全
- where 条件要有索引,能直接定位到某条记录进行上锁
- 如果 where 条件没有索引,就是全表扫描,知道找到目标记录,结果会把扫描的记录全部加锁!
- 适用于写比较多的场景
- 乐观锁是程序层面做的,两种思路
- 版本号机制:给每条数据添加个版本,version,每次修改 version+1
- 每当有修改数据的业务,先查询该条数据的 version,比如当前是 2
- 做完业务后,更新数据时带上条件 where version = 2
- 如果更新不成功,说明 version=2 的找不到数据(别的业务更新过该条数据,version 变了)
- CAS 自旋,再次查询 version,然后更新当前 version 的数据
- 时间戳机制:和 version 思想一致,值是时间错而已
- 适用于读比较多的场景
- 版本号机制:给每条数据添加个版本,version,每次修改 version+1
死锁
-
同一张表(行锁)下的死锁
session1 session2 begin; begin; id = 1 加 X 锁
select * from user where id=1 for update;id = 2 加 X 锁
select * from user where id=2 for update;阻塞,等待 session2 释放
select * from user where id=2 for update;阻塞,等待 session1 释放
select * from user where id=1 for update;commit; commit; -
不同表下的死锁
session1 session2 begin; begin; select * from tableA where id=1 for update; select * from tableB where id=1 for update; select * from tableB where id=1 for update; select * from tableA where id=1 for update; commit; commit; -
间隙锁下的死锁(当前数据库存在id = 1, id = 3, id = 9 三条记录)
session1 session2 begin; begin; (3,9) 间隙锁
select * from user where id = 5 for update;再加一个 (3,9) 间隙锁
select * from user where id = 6 for update;插入数据,id = 7
insert into user (id, name) values (7, 'Bob');插入数据,id = 8
insert into user (id, name) values (8, 'Milk');commit; commit; -
死锁解决方案
- 等待,直到超时。时间由参数 innodb_lock_wait_timeout 决定,默认 50s(show VARIABLES like 'innodb_lock_wait_timeout';)
- 回滚 undo 最小的事务。是否死锁根据每个事务再等待那个事务结束,画一个图,如果能形成一个环,那么就是死锁。这时也能知道哪个事务做的操作最小,回滚它来释放锁
-
避免死锁思路
- 优化 sql,比如给 name = 'Marry' 的记录加锁,name 涉及成索引列(扫描的行记录都会加锁,如果能根据索引只定位一条记录,那么扫描的行就是一条)
- 避免大事务,或者拆为小事务
- 乐观锁
- 较低隔离级别,提升锁粒度