吴川市规划建设局网站,进行网站建设视频,单位网站用途类型,域名备案 网站名称【MySQL系列】- MYSQL锁机制 文章目录 【MySQL系列】- MYSQL锁机制一、表级锁表加读锁表独占锁释放锁 二、行级锁间隙锁#xff08;Gap Locks#xff09;临键锁#xff08;Next-key Locks#xff09; 三、页级锁四、共享锁#xff08;S锁#xff09;五、排他锁#xff0…【MySQL系列】- MYSQL锁机制 文章目录 【MySQL系列】- MYSQL锁机制一、表级锁表加读锁表独占锁释放锁 二、行级锁间隙锁Gap Locks临键锁Next-key Locks 三、页级锁四、共享锁S锁五、排他锁X锁六、死锁6.1 死锁的产生的原因6.2 如何避免死锁 七、总结 数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问变得有序所设计的一种规则。 MySQL 数据库由于其自身架构的特点存在多种数据存储引擎每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计所以各存储引擎的锁定机制也有较大区别。
一、表级锁
表级锁每次操作锁住整张表。锁定粒度大发生锁冲突的概率最高并发度最低。应用在MyISAM、InnDB、BDB等存储引擎中对于表级锁一般分 1).表锁 2). 元数据锁 3). 意向锁。
表加读锁
lock tables 表名.. read; -- 是指可以对多张表加锁表独占锁
lock tables 表名... write; -- 可以对多张表加独占锁释放锁
unlock tables; -- 释放锁客户端短裤连接二、行级锁
行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录的加锁。
对于行级锁主要分为以下三类
行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持间隙锁Gap Lock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持。临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持。
InnoDB实现了以下两种类型的行锁
共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排它锁。排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁。
SQL行锁类型说明INSERT…排他锁自动加锁UPDATE…排他锁自动加锁DELETE…排他锁自动加锁SELECT…不加任何锁SELECT… LOCK IN SHARE MODE共享锁手动加锁SELECT…FOR UPDATE排他锁手动加锁
间隙锁Gap Locks
间隙锁是封锁索引记录中的间隔或者第一条索引记录之前的范围又或者最后一条索引记录之后的范围。
产生间隙锁的条件RR事务隔离级别下
1). 使用普通索引锁定2). 使用多列唯一索引3). 使用唯一索引锁定多行记录。对于使用唯一索引来搜索并给某一行记录加锁的语句。只会产生记录锁不会产生间隙锁。
打开间隙锁设置
查看是否启用间隙锁
show variables like innodb_locks_unsafe_for_binlog;innodb_locks_unsafe_for_binlog默认值为OFF,既启用间隙锁。因为此参数是只读模式如果想要禁用间隙锁需要修改my.cnf 重启才能生效。
[mysqld]
innodb_locks_unsafe_for_binlog 1结论
对于指定查询某一条记录的加锁语句如果该记录不存在会产生记录锁和间隙锁如果记录存在则会产生记录锁如WHERE Id1 FOR UPDATE;对于查找某一范围的查询语句会产生间隙如WHERE ID BETWEEN 5 AND 7 FOR UPDATE;在普通索引列上不管是何种查询只是加锁都会产生间隙锁。
临键锁Next-key Locks
临键锁是记录锁与间隙锁的组合它的封锁范围既包含索引记录又包含索引区间。 临键锁的主要目的也是为了避免幻读Phantom Read。如果把事务的隔离级别降级为RC临键锁则也会失效。 三、页级锁
锁就是在 页的粒度 上进行锁定锁定的数据资源比行锁要多因为一个页中可以有多个行记录。当我 们使用页锁的时候会出现数据浪费的现象但这样的浪费最多也就是一个页上的数据行。页锁的开销 介于表锁和行锁之间会出现死锁。锁定粒度介于表锁和行锁之间并发度一般。
四、共享锁S锁
共享锁Shared Lock,又称S锁、读锁。针对行锁。当有事务对数据加读锁后其他事务只能对锁定的数据加读锁不能加写锁排他锁所以其他事务只能读不能写。
加锁方式
select * from Table where id2 lock in share mode;释放方式
commit 或 rollback;五、排他锁X锁
X锁英文为Exclusive Lock既为排他锁。也可以称为Write Lock;X锁是具有排他性。既一个写锁会阻止其他的X锁和S锁。当一个事务需要修改一条记录时需要先获取该记录的X锁。
X锁加锁格式
select ... for update;六、死锁
由于Mysql有多种类型在实际开发中可能产生死锁。
6.1 死锁的产生的原因
死锁是指两个或两个以上的进程在执行过程中因资源的争夺造成的一种相互等待的现象。若无外来的作用他们都将无法推进下去此时的系统处于死锁状态或系统产生了死锁。
死锁的关键在于两个或以上的Session加锁的顺序不一致。
对应的解决死锁问题的关键就是让不同的Session加锁有次序。
6.2 如何避免死锁
以固定的顺序访问表和行。比如两个更新数据的事务事务A更新数据的顺序为1,2事务B更新数据顺序为2,1这样可能会造成死锁大事务拆小事务。大事务更倾向于死锁如果业务运行将大事务拆小。在同一个事务中尽可能做到一次锁定所需所有资源减少死锁概率。降低隔离级别。如果业务允许将隔离级别调低也是比较好的选择比如将隔离级别从RR调整为RC可以避免很多原因gap锁造成的死锁。为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁死锁的概率大大增加。
七、总结
InnoDB存储引擎由于实现了行级锁定虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些但是在整体并发处理能力方面要远远优于MyISAM的表锁的。当系统并发量较高的时候InnoDB的整体性能和MyISAM相比就会有比较明显的优势 但是InnoDB的行级锁同样也有其脆弱的一面当我们使用不当的时候可能会让InnoDB的整体性能表现不仅不能比MyISAM高甚至可能会更差
尽可能让所有数据检索都能通过索引来完成避免无索引行锁升级为表锁合理设计索引尽量缩小锁的范围尽可能减少索引条件及索引范围避免间隙锁尽量控制事务大小减少锁定资源量和时间长度尽可使用低级别事务隔离需要业务层面满足需求
