IT评测·应用市场-qidao123.com技术社区

标题: MySQL 锁机制 [打印本页]

作者: 南七星之家 时间: 2024-9-10 11:20
标题: MySQL 锁机制
优质博文：IT-BLOG-CN
定义：锁是盘算机和谐多个进程或线程并发访问某一资源的机制。
一、表锁（偏读）

MyISAM 引擎，开销小，加锁快，无死锁、锁定粒度大、发生锁冲突的粒度最高，并发度低。
【1】手动增加表锁：lock table 表名1 read(write),表名2 read(write),其他；
【2】查看那些表加锁：show open tables；
【3】释放表：unlock tables，也可以在客户端断开的时候自动释放；
【结论】： 当 session1 对 my_lock 表加了 read 表锁后，①、不能对别的表进行操作。②、当 session 对 my_lock 进行写操作时，会挂起排队等待解锁。当 session1 对 my_lock 表加了 write 表锁后，①、当 session 读 my_lock 表时，会壅闭等待 session1 释放锁。
表锁紧张是 MyISAM 引擎的特点，紧张用于查询操作。MyISAM 在执行查询语句前，会自动给涉及到的所有表加读锁，在执行增删改之前，会给所有的表加写锁。
【如何分析表锁定】： 可以通过检查table_locks_waited 和 table_locks_immediate状态来分析系统上的表锁定。

show status like 'table%';

复制代码

■ table_locks_immediate：产生表级锁定的次数，表示可以立刻获取锁的查询次数，每获取锁一次值加1；
■ table_locks_waited：出现表级锁定争用而发生等待的次数（不能立刻获取锁）；
二、行锁（偏写）

行锁就是针对数据表中行记载的锁。好比变乱A更新了一行，而这时候变乱B也要更新同一行，则必须等变乱A的操作完成后才气进行更新。
行锁分为如下两种：
共享锁（Shared Locks）： 简称S锁。在变乱要读取一条记载时，必要先获取该记载的S锁；
独占锁（Exclusive Locks）： 简称X锁。在变乱要改动一条记载时，必要先获取该记载的X锁；
读取一条记载时必要获取一下该记载的S锁，实在这是不严谨的。如果只是平凡的读，那么是不会加锁的。想要在读取记载时获取记载的锁有两种SELECT语句：
【1】对读取的记载加S锁：

SELECT ... LOCK IN SHARE MODE;

复制代码

【2】对读取的记载加X锁:

SELECT ... FOR UPDATE;

复制代码

写操作所加的锁：
【1】DELETE： 对一条记载做 DELETE操作的过程实在是先在 B+树中定位到这条记载的位置，然后获取这条记载的X锁，然后再执行 delete mark操作。
【2】UPDATE： ① 如果未修改该记载的键值而且被更新的列占用的存储空间在修改前后未发生变化，则先在 B+树中定位到这条记载的位置，然后再获取记载的 X锁；② 如果未修改该记载的键值而且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在 B+树中定位到这条记载的位置，然后获取记载的 X锁，将该记载彻底删除掉（就是把记载彻底移入垃圾链表），末了再插入一条新记载。也就是会获取 X锁和隐式锁。③ 如果修改了该记载的键值，则相当于在原记载上做 DELETE操作之后再来一次 INSERT操作，加锁操作就必要按照 DELETE和 INSERT的规则进行了。
【3】INSERT： 通过一种称之为隐式锁来保护这条新插入的记载在本变乱提交前不被别的变乱访问。
如果你的变乱中必要锁多个行，要把最大概造成锁冲突、最大概影响并发度的锁尽量以后放。
  行锁偏向 InnoDB 引擎，开销大，加锁慢、会出现死锁、锁定粒度小、发生冲突的概率低，并发度高。
InnoDB 与 MyISAM 最大的不同是：①、一个支持变乱，②、采用了行级锁。
  【结论】： 当 session1 对一行数据修改，但未提交时，session2 修改行数据时会壅闭，但是可以查询，但查到的是旧数据。
取消自动提交：set autocommit=0;
  【无索引行锁升级为表锁】： 当索引失效后，会导致此问题。
【间隙锁危害】： 当我们使用范围条件而不是相等条件检索数据，并哀求共享锁或排它锁时，InnoDB 会给符合条件的已有数据记载的索引项加锁；对于键值在条件范围内但不存在的记载，叫做 “间隙（GPA）”，InnoDB 也会对这个 “间隙” 加锁，这种锁机制就是所谓的“间隙锁”（Next-Key锁），解决幻读问题。
由于 Query 执行过程中通过范围进行查找，他会锁定整个范围内索引的索引键值，纵然这个键值不存在。间隙锁有一个致命的缺点，就是当锁定一个范围之后，纵然某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据，在某些场景下这大概会对性能造成很大的危害。
【如何加行锁】：

【行锁分析下令如下】：

show status like 'innodb_row_lock%';

复制代码

■ Innodb_row_lock_current_waits：当前正在等待锁定的数量。
■ Innodb_row_lock_time：从系统启动到现在锁定总时间长度。
■ Innodb_row_lock_time_avg：每次等待所花的平均时间。
■ Innodb_row_lock_time_max：从系统启动到现在等待最长的一次时间。
■ Innodb_row_lock_waits：系统启动后到现在统共等待的次数。
三、隐试锁 Gap Lock以及 next-key lock

Gap Lock以及next-key lock是为了解决幻读的。产生幻读的缘故原由是，行锁只能锁住行，但是新插入记载这个动作，要更新的是记载之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。间隙锁，锁的就是两个值之间的空隙。如下，表t，初始化插入了6个记载，这就产生了7个间隙。

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

复制代码

当你执行 select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记载加上了行锁，还同时加了7个间隙锁。如许就确保了无法再插入新的记载。间隙锁之间是不存在冲突的，比方：
SessionASessionBbegin;select * from t where c=7 lock in share mode;begin;select * from t where c=7 for update; 这里 session B并不会被堵住。由于表t 里并没有 c=7这个记载，因此 session A加的是间隙锁(5,10)。而 sessionB 也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不答应插入值。但它们之间是不冲突的。间隙锁和行锁合称 next-key lock， 每个 next-key lock是前开后闭区间。如果用 select * from t for update要把整个表所有记载锁起来，就形成了7个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
间隙锁造成的死锁： 我用两个 session来模拟并发，并假设往表里插入一条 id=9的数据。

【1】session A 执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10);
【2】session B 执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
【3】session B 试图插入一行(9,9,9)，被session A的间隙锁挡住了，只好进入等待；
【4】session A试图插入一行(9,9,9)，被session B的间隙锁挡住了；
间隙锁的引入，大概会导致同样的语句锁住更大的范围，这实在是影响了并发度的；
四、全表扫描

假如 country列上未建索引，以是只能采用全表扫描的方式来执行这条查询语句，扫描过的行会先加锁，然后再释放掉：

SELECT * FROM hero WHERE country = '魏' LOCK IN SHARE MODE;

复制代码

对于UPDATE …和DELETE …的语句来说，在遍历聚簇索引中的记载，都会为该聚簇索引记载加上X型行锁，然后：
【1】如果该聚簇索引记载不满足条件，直接把该记载上的锁释放掉；
【2】如果该聚簇索引记载满足条件，则会对相应的二级索引记载加上 X型行锁。
页锁：开销和加锁时间界于行表锁之间；会出现死锁；锁定粒度界于行表锁之间，并发度一样平常。（了解即可）

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)