文档章节

Mysql InnoDB行锁实现方式

mickelfeng
 mickelfeng
发布于 01/18 16:54
字数 1648
阅读 20
收藏 4

mysql中InnoDB存储引擎的行锁和表锁

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
 
在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。下面通过一些实际例子来加以说明。
(1)在不通过索引条件查询的时候,InnoDB确实使用的是表锁,而不是行锁。
 
在如表20-9所示的例子中,开始tab_no_index表没有索引:

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

表20-9         InnoDB存储引擎的表在不使用索引时使用表锁例子
session_1

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where id = 1 ;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_no_index where id = 1 for update;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)

session_2

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where id = 2 ;
+------+------+
| id   | name |
+------+------+
| 2    | 2    |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tab_no_index where id = 2 for update;
等待

在如表20-9所示的例子中,看起来session_1只给一行加了排他锁,但session_2在请求其他行的排他锁时,却出现了锁等待!原因就是在没有索引的情况下,InnoDB只能使用表锁。当我们给其增加一个索引后,InnoDB就只锁定了符合条件的行,如表20-10所示。
 
创建tab_with_index表,id字段有普通索引:

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> alter table tab_with_index add index id(id);
Query OK, 4 rows affected (0.24 sec)
Records: 4  Duplicates: 0  Warnings: 0

表20-10    InnoDB存储引擎的表在使用索引时使用行锁例子
  www.2cto.com  
session_1
session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 ;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 2 ;
+------+------+
| id   | name |
+------+------+
| 2    | 2    |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tab_with_index where id = 1 for update;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)
 
mysql> select * from tab_with_index where id = 2 for update;
+------+------+
| id   | name |
+------+------+
| 2    | 2    |
+------+------+
1 row in set (0.00 sec)
(2)由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。应用设计的时候要注意这一点。
 
在如表20-11所示的例子中,表tab_with_index的id字段有索引,name字段没有索引:
 
mysql> alter table tab_with_index drop index name;
Query OK, 4 rows affected (0.22 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> insert into tab_with_index  values(1,'4');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab_with_index where id = 1;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
| 1    | 4    |
+------+------+
2 rows in set (0.00 sec)
表20-11    InnoDB存储引擎使用相同索引键的阻塞例子
  www.2cto.com  
session_1
session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 and name = '1' for update;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)
 
虽然session_2访问的是和session_1不同的记录,但是因为使用了相同的索引,所以需要等待锁:
mysql> select * from tab_with_index where id = 1 and name = '4' for update;
等待
(3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
  www.2cto.com  
在如表20-12所示的例子中,表tab_with_index的id字段有主键索引,name字段有普通索引:
 
mysql> alter table tab_with_index add index name(name);
Query OK, 5 rows affected (0.23 sec)
Records: 5  Duplicates: 0  Warnings: 0
表20-12    InnoDB存储引擎的表使用不同索引的阻塞例子
 
·          session_1
·          session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 for update;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
| 1    | 4    |
+------+------+
2 rows in set (0.00 sec)
   www.2cto.com  
Session_2使用name的索引访问记录,因为记录没有被索引,所以可以获得锁:
mysql> select * from tab_with_index where name = '2' for update;
+------+------+
| id   | name |
+------+------+
| 2    | 2    |
+------+------+
1 row in set (0.00 sec)
 
由于访问的记录已经被session_1锁定,所以等待获得锁。:
mysql> select * from tab_with_index where name = '4' for update;
(4)即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。关于MySQL在什么情况下不使用索引的详细讨论,参见本章“索引问题”一节的介绍。
  www.2cto.com  
在下面的例子中,检索值的数据类型与索引字段不同,虽然MySQL能够进行数据类型转换,但却不会使用索引,从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划,我们可以清楚地看到了这一点。
 
例子中tab_with_index表的name字段有索引,但是name字段是varchar类型的,如果where条件中不是和varchar类型进行比较,则会对name进行类型转换,而执行的全表扫描。
 
mysql> alter table tab_no_index add index name(name);
Query OK, 4 rows affected (8.06 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> explain select * from tab_with_index where name = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab_with_index
type: ALL
possible_keys: name
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from tab_with_index where name = '1' \G
*************************** 1. row ***************************
id: 1  www.2cto.com  
select_type: SIMPLE
table: tab_with_index
type: ref
possible_keys: name
key: name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

本文转载自:https://www.cnblogs.com/lushilin/p/6135374.html

共有 人打赏支持
mickelfeng

mickelfeng

粉丝 235
博文 2751
码字总数 596619
作品 0
成都
高级程序员
私信 提问
SQL优化之一则MySQL中的DELETE、UPDATE 子查询的锁机制失效案例

前言 开发与维护人员避免不了与 in/exists、not in/not exists 子查询打交道,接触过的人可能知道 in/exists、not in/not exists 相关子查询会使 SELECT 查询变慢,没有 join 连接效率,却不...

全部原谅
2018/07/27
0
0
MySQL数据库InnoDB存储引擎源代码调试跟踪分析

导读目录: 1 早期结论... 3 2 测试一:死锁检测... 4 3 测试二:cursor测试... 6 4 测试三:external_lock测试... 6 5 测试四:杂项测试... 6 6 测试五:autocommit测试... 7 7 测试六:unl...

吴问志
2011/12/06
0
0
重新学习Mysql数据库2:『浅入浅出』MySQL 和 InnoDB

作为一名开发人员,在日常的工作中会难以避免地接触到数据库,无论是基于文件的 sqlite 还是工程上使用非常广泛的 MySQL、PostgreSQL,但是一直以来也没有对数据库有一个非常清晰并且成体系的...

你的猫大哥
2017/12/10
0
0
InnoDB,5项最佳实践,知其所以然?

缓存讲了一个月《缓存架构,一篇足够》。今天,开始写数据库。 第一篇,说说MySQL两个最常用的存储引擎,MyISAM和InnoDB。照自己的理解,把一些知识点总结出来,不只说知识点,多讲“为什么”...

架构师之路
2018/08/08
0
0
MySQL存储引擎InnoDB与Myisam的六大区别

MySQL有多种存储引擎,每种存储引擎有各自的优缺点,可以择优选择使用: MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。 MySQL支持...

Junn
2013/12/11
0
2

没有更多内容

加载失败,请刷新页面

加载更多

Android安全防护防护———加密算法

摘要 这篇文章本来早就应该写了,但是由于项目一直开发新的需求,就拖后了。现在有时间了,必须得写了。现在Android应用程序对安全防范这方面要求越来越高了。特别是金融行业,如果金融app没...

北辰丨丶
22分钟前
1
0
MongoDB ObjectId详解及使用

MongoDB ObjectId详解及使用 2017年09月13日 14:25:18 universsky2015 阅读数:11802 版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/universsky2015/article...

linjin200
26分钟前
1
0
Caffe在CentOS和Ubuntu上安装

1 CentOS上安装中需要配置第三方源 #sudo yum install epel-release 如果不安装第三方软件源,很多依赖包都不能通过yum安装 Makefile blas = open 2 Unbuntu PYTHON_INCLUDES=/usr/local/li...

shengjuntu
28分钟前
1
0
一线 IT 公司开发转管理,我是怎么从 0 到 1 的?

在某一线互联网公司的任职生涯马上就要结束了,回想起来,从 16 年校招加入,到今年年初离职,在这快三年的时间里,公司在飞速地发展和变化,我也从一个刚入职场的初级后台开发成长为带着十来...

编辑部的故事
28分钟前
1
0
Hibernate 自动建表默认编码格式改为UTF-8

Hibernate 自动建表时报编码错误: Caused by: java.sql.SQLException: Incorrect string value: '\...' for column 'cust_name' at row 1 一般情况我们使用的mysql方言为:org.hibernate.d......

gwl_
31分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部