mysql是并发处理数据的,而数据又是共享资源,所以mysql必须运用锁才能实现并发的安全访问。
锁与事务
由于mysql的并发最小单位是事务,所以锁的有效范围在一个事务内。从开启锁到commit。
锁住的对象可以是单行,多行,表。事务也可能会很长,若两个事务互相等待对方的锁,就会死锁,事务无法结束,一般简单的死锁mysql会主动停止一个事务,释放锁; 或是等待超时。
为了防止事务很长,锁的时间太久,尽量把更新操作或加锁操作放到事务的最后位置。
乐观锁与悲观锁
乐观锁,更新时比较数据(即CAS操作),在数据库中,通过用户增加version字段,在update时匹配版本。由于不会锁住数据,读取性能较好,若写入很多时,版本变化过快,会造成大量失败更新, 所以适合读多写的场景。
悲观锁,独占被锁住的资源,mysql提供的各种锁都属于该类,独占资源后,写入的成功率很高,所以适合写多读少的场景。
mysql的锁
读锁(共享锁 share S锁)
读操作可以重入,与写锁排斥
用处:在读取某个数据的时候,不希望数据被别人修改,同时自己也不会修改该数据。 加写锁也能达到目的,但区别就是读锁大家都可以加,而写锁只能被一个人加; 在写程序时,经常是同一段逻辑并发执行,如果用写锁,就只能允许一个事务加锁,并发受影响; 而读锁可以一起加,并发执行。
使用:select * from t where id=1 lock in share mode;
写锁(排他锁 exclusive X锁)
独占锁,数据库修改都会自动加写锁
用处:占用资源,对资源进行修改,在锁释放前,其他事务无法操作该资源,实现了并发安全。
使用:手动加 select * from t where id=1 for update; 语句 update / insert / delete 会自动加
意向锁(intention)
当表内被加了写锁或读锁后,会给表设置一个状态,方便其他事务加锁时候查看,不用再去索引树查询锁情况
根据行锁的类型,又分为意向共享锁IS ,意向排他锁IX。
使用:引擎自动加锁,用户不需操作。
写锁按照锁的范围不同分为
表锁
锁住整个表,不会死锁,实现简单,高效;但是无法实现事务的隔离级别,写入并发低;若不需要事务,读多写少比较适合。
myisam引擎只实现了表锁;
使用:mysql会自动加表锁,也可手动加
lock table t read/write; //给表t加读写锁
unlock tables; //释放锁
show open tables; //查询哪些表被锁了 in_use=1
行锁
按数据行锁定,锁的标记并没有存在数据块上,而是加在了主键索引上。
当对非索引字段查询加锁时,在RR事务级别会锁住扫描过的所有行和间隙,范围差不多是表锁,但不是表锁,因为mysql要保证RR级别的隔离性。若是RC级别,只会锁住满足条件的所有行。
使用:手动加 select * from t where id=1 for update; 语句 update / insert / delete 会自动加
间隙锁
mysql的数据是松散的存放在B+树上的叶子节点,只要主键索引大小满足,可以在中间位置插入数据,当成功插入数据后,就会使其他事务的范围查询就可能出现幻读。
间隙锁就是锁住两个数据块之间的间隙,防止有其他事务插入数据;以解决幻读问题。由于只有RR事务级别才需要解决幻读,所以间隙锁只有RR级别可用。
使用:按范围锁 from t where id>2 and id锁住间隙中任意一个不存在的值,如存在数据id=1,2,10,11。执行 from t where id=5 for update; 会锁住2
临键锁
行锁与临键锁的组合
锁等待分析
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits 0 --当前正在等待锁的数量
Innodb_row_lock_time 54505 --总的锁定时间(从启动)
Innodb_row_lock_time_avg 6813 --锁平均时间*******
Innodb_row_lock_time_max 23051 --最长一次锁的时间
Innodb_row_lock_waits 8 --总共锁等待的次数*******
锁相关的系统表
-- 事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 锁,8.0为performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 锁等待,8.0为performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 释放锁,trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id
-- 查看锁等待详细信息, 可以通过记录分析死锁原因
show engine innodb status;
锁优化
- 手动加锁时,必须使用索引加锁,否则可能会锁住整表。
- 对范围加锁时,尽可能减少范围大小。
- 加锁行为放到事务最后部分,最大化减少锁时间。
- 间隙锁在尾部时,最好提供一个有限范围,否则会锁住无穷大。
- 尽可能低的使用较低的事务级别,能在业务中实现的,尽量在业务实现。