5、mysql锁

最新推荐文章于 2024-07-21 19:06:05 发布

方二华

最新推荐文章于 2024-07-21 19:06:05 发布

阅读量36

点赞数

分类专栏： # mysql 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq407155634/article/details/129422572

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

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;