搜尋
首頁資料庫mysql教程MySQL學習之聊InnoDB中鎖的狀況

本篇文章带大家聊聊MySQL,介绍一下InnoDB中锁的情况。有一定的参考价值,有需要的朋友可以参考一下,希望对大家有所帮助。

MySQL學習之聊InnoDB中鎖的狀況

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.01 sec)

一,锁的基本介绍

相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

行级锁类型:

Record Lock(记录锁):当个记录的锁(锁住单条记录)

记录锁只会锁住索引的记录,如果InnoDB存储表在建立的时候没有任何索引,那么这个锁会使用隐式的主键来进行锁定,如下图

MySQL學習之聊InnoDB中鎖的狀況

Gap Lock(间隙锁):锁定一个范围,不包括记录本身(只锁数据前面的GAP)

如下图为的锁就是GAP锁,就是不允许其他事务在索引列8之前的间隙插入新的记录,也就是(3 , 8)这个区间。gap锁 的作用仅仅是为了防止插入幻影记录的而已

MySQL學習之聊InnoDB中鎖的狀況

Next-Key Lock(临键锁):同时锁住记录和记录前面的GAP,也就是Next-Key Lock = Record Lock Gap Lock。

MySQL學習之聊InnoDB中鎖的狀況

二,锁的分类

共享锁 Share Locks (简称S锁,属于行锁)

排它锁 Exclusive Locks (简称X锁,属于行锁)

意向共享锁 Intention Share Locks (简称IS锁,属于表锁)

意向排它锁 Intention Exclusive Locks (简称IX锁,属于表锁)

自增锁 AUTO-INC Locks(属于表锁)

下面具体介绍下每种类型的锁,我们先建一张innodb的表,sql如下

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

共享锁

共享锁就是多个事务对于同一个数据可以共享一把锁,都能访问数据库,但是只能读不能修改;

事务A:

select * from tab_with_index lock in share mode;

事务B:

select * from tab_with_index where id =1; // 可以查询数据

update tab_with_index set name = 'aa' where id = 1 ;

注意:这里的修改语句会堵塞住,直到事务A提交之后才能操作成功。

排它锁

排它锁不能与其他锁并存,如一个事务获取了一个数据行的排它锁,其他事务就不能在获取该行的锁,只有当前获取排它锁的事务可以对数据进行修改。(delete,update,create默认是排它锁)

事务A:

select * from tab_with_index where id =1 for update;

事务B:

select * from tab_with_index where id =1; //可以获取结果

select * from tab_with_index where id =1 for update; // 堵塞

select * from tab_with_index where id = 1 lock for share mode; // 堵塞

注意:事务B两个sql都会堵塞住,也就是获取不到共享锁也获取不到排它锁,直到事务A提交之后才能操作成功。

意向共享锁和意向排它锁

意向共享锁:表示事务准备给数据行加入共享锁,也就是说一个数据行在加共享锁之前必须先获取该表的IS锁。

意向排它锁:表示事务准备给数据行加入排它锁,也就是说一个数据行加排它锁之前必须先获取该表的IX锁。

IS锁和IX锁是表级锁,他们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁,以避免用遍历的方式来查看表中有没有上锁的记录,也就是说其实IS锁和IX锁是兼容的,IX锁和IX锁是兼容的。 《MySQL是怎样运行的》

自增锁

针对自增列自增长的一个特殊的表级别锁。

show variables like 'innodb_autoinc_lock_mode'; 
-- 默认值1,代表连续,事务未提交则ID永久丢失

MySQL學習之聊InnoDB中鎖的狀況

三,InnoDB锁

1、事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元,事务具有4属性,通常称为事务的ACID属性。

原子性(Actomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

2、并发事务带来的问题

相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持更多用户的并发操作,但与此同时,会带来一下问题:

脏读: 一个事务正在对一条记录做修改,在这个事务并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”的数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”

不可重复读:一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了!这种现象叫做“不可重复读”。

幻读: 一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”

上述出现的问题都是数据库读一致性的问题,可以通过事务的隔离机制来进行保证。

数据库的事务隔离越严格,并发副作用就越小,但付出的代价也就越大,因为事务隔离本质上就是使事务在一定程度上串行化,需要根据具体的业务需求来决定使用哪种隔离级别


脏读 不可重复读 幻读
read uncommitted
read committed
repeatable read

serializable


可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果发现锁争用比较严重,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

3、InnoDB的行锁模式及加锁方法

共享锁(S) :又称读锁(lock in share mode)。允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。  排他锁(X) :又称写锁(for update)。允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。

mysql InnoDB引擎默认的修改数据语句:update,delete,insert都会自动给涉及到的数据加上排他锁,select语句默认不会加任何锁类型,如果加排他锁可以使用select …for update语句,加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的,也不能通过for update和lock in share mode锁的方式查询数据,但可以直接通过select …from…查询数据,因为普通查询没有任何锁机制。

4、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!

1、在不通过索引条件查询的时候,innodb使用的是表锁而不是行锁

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
session1 session2
set autocommit=0 select * from tab_no_index where id = 1; set autocommit=0 select * from tab_no_index where id =2
select * from tab_no_index where id = 1 for update

select * from tab_no_index where id = 2 for update;

session1只给一行加了排他锁,但是session2在请求其他行的排他锁的时候,会出现锁等待。原因是在没有索引的情况下,innodb只能使用表锁。

2、创建带索引的表进行条件查询,innodb使用的是行锁

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
session1 session2
set autocommit=0 select * from tab_with_indexwhere id = 1; set autocommit=0 select * from tab_with_indexwhere id =2
select * from tab_with_indexwhere id = 1 for update

select * from tab_with_indexwhere id = 2 for update;

3、由于mysql的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是依然无法访问到具体的数据(这里是表锁)

alter table tab_with_index drop index id;
insert into tab_with_index  values(1,'4');
session1 session2
set autocommit=0 set autocommit=0
select * from tab_with_index where id = 1 and name='1' for update

select * from tab_with_index where id = 1 and name='4' for update 虽然session2访问的是和session1不同的记录,但是锁的是具体表,所以需要等待锁

總結

對於InnoDB表,本文主要討論了以下幾項內容: (1)InnoDB的行鎖是基於索引實現的,如果不透過索引存取數據,InnoDB會使用表鎖。 (2)在不同的隔離等級下,InnoDB的鎖定機制和一致性讀取策略不同。

在了解InnoDB鎖定特性後,使用者可以透過設計和SQL調整等措施減少鎖定衝突和死鎖,包括:

  • 盡量使用較低的隔離等級;精心設計索引,並儘量使用索引存取數據,使加鎖更精確,從而減少鎖衝突的機會;
  • 選擇合理的事務大小,小事務發生鎖衝突的幾率也更小;
  • 給記錄集明確加鎖時,最好一次性要求足夠等級的鎖。例如要修改資料的話,最好直接申請排他鎖,而不是先申請共享鎖,修改時再請求排他鎖,這樣容易產生死鎖;
  • 不同的程式存取一組表時,應盡量約定以相同的順序存取各表,對一個表而言,盡可能以固定的順序存取表中的行。這樣可以大幅減少死鎖的機會;
  • 盡量用相等條件存取數據,這樣可以避免間隙鎖對並發插入的影響; 不要申請超過實際需要的鎖級別;除非必須,查詢時不要顯示加鎖;
  • 對於一些特定的事務,可以使用表鎖來提高處理速度或減少死鎖的可能性。

【相關推薦:mysql影片教學

以上是MySQL學習之聊InnoDB中鎖的狀況的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述
本文轉載於:掘金社区。如有侵權,請聯絡admin@php.cn刪除
图文详解mysql架构原理图文详解mysql架构原理May 17, 2022 pm 05:54 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql怎么替换换行符mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

mysql的msi与zip版本有什么区别mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PM

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

mysql怎么去掉第一个字符mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

mysql怎么将varchar转换为int类型mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

MySQL复制技术之异步复制和半同步复制MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

带你把MySQL索引吃透了带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。

mysql怎么判断是否是数字类型mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
2 週前By尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
4 週前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
4 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

MantisBT

MantisBT

Mantis是一個易於部署的基於Web的缺陷追蹤工具,用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序,非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具,幫助Web開發人員更好地理解保護網路應用程式的過程,並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞,難度各不相同。請注意,該軟體中

SublimeText3 英文版

SublimeText3 英文版

推薦:為Win版本,支援程式碼提示!

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

將Eclipse與SAP NetWeaver應用伺服器整合。

Dreamweaver Mac版

Dreamweaver Mac版

視覺化網頁開發工具