文档章节

MySQL事务隔离级别和锁

秋风醉了
 秋风醉了
发布于 2015/08/31 19:33
字数 1996
阅读 320
收藏 3

MySQL事务隔离级别和锁

表结构

create table record(
id int auto_increment primary key,
title varchar(255) not null,
shortName varchar(255) not null,
authorId int not null,
createTime datetime not null,
state int  not null,
totalView int default null
);

insert into record (title,shortName,authorId,createTime,state,totalView) 
values ('hello world 000','hello-world-0',1,'2015-10-11 08:08:08',1,10),
('hello world 111','hello-world-1',1,'2015-10-11 08:08:08',2,10),
('hello world 222','hello-world-2',2,'2015-10-11 08:08:08',3,10),
('hello world 333','hello-world-3',3,'2015-10-11 08:08:08',4,10),
('hello world 444','hello-world-4',3,'2015-10-11 08:08:08',5,10);

首先关于事务的隔离级别

http://my.oschina.net/xinxingegeya/blog/215419

http://my.oschina.net/xinxingegeya/blog/296513

还有锁的分类,粒度和策略

http://my.oschina.net/xinxingegeya/blog/215417

MySQL的多版本控制MVCC

http://my.oschina.net/xinxingegeya/blog/208821

 

RC隔离级别下的锁

在READ-COMMITTED隔离级别下,行锁的表现如下,

SessionA

开启事务

mysql>
mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>

SessionB

开启事务

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>

SessionA

在Session A中更新id = 1 的记录,如下,

mysql> update record set title = 'session a update' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

更新成功了,接下来在Session B中更新同一个id = 1的记录,

Session B

mysql> update record set title = 'session b update' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到由于Session A还没有提交,SessionA持有id = 1 的记录的行锁,所以当Session B更新时没有相应的行锁,所以锁等待超时更新失败。同时也可以看到在当前的事务下可以更新其他的纪录。

mysql> update record set title = 'session b update' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update record set title = 'session b update' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

更新id = 2 的纪录成功了。

但要注意的是,我们通过id字段进行更新的,通过id字段选择要更新的数据行,同时id字段是一个主键列,如果在没有索引的字段上查找更新会有怎么样的效果呢?我们来看一下。

Session AA

开启事务,

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

Session BB

开启事务,

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

Sessoin AA

mysql> update record set title = 'session a update' where authorId = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

更新成功。接下来在Session BB中更新authorId = 1 的数据行。按照上面说的情况,authorId列上没有索引,这样会导致锁表,但实际的效果是怎么样的呢?

Session BB

mysql> update record set title = 'session c update' where authorId = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update record set title = 'session c update' where authorId = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到更新authorId = 2 的记录没有等待锁,也就是其他数据行没有被锁住,而更新authorId = 1 的数据行时却发现锁等待超时(Session AA事务还没有提交)。

我们前面也说了,当更新非索引列时会把整个表锁住,这是怎么回事?

这时因为当通过authorId更新时,mysql存储引擎不知道要锁定哪些数据行,因为authorId上没有索引,所以返回整个表的数据行,同时锁住。然后mysql服务器层进行过滤,同时解锁不符合条件的数据行(调用存储引擎的unlock操作)。

最后提交 Session AA的事务,

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

然后在Session BB中执行更新,提交,

mysql> update record set title = 'session b update' where authorId = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

 

RR隔离级别下的锁

我们来看一下RR隔离级别下的锁,首先我们把authorId列上加上非唯一索引。

alter table record add index idx_author_id (authorId);

Session A

开启事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

Session B

开启事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

Session A

执行以下更新语句

mysql> select * from record;
+----+------------------+---------------+----------+---------------------+-------+-----------+
| id | title            | shortName     | authorId | createTime          | state | totalView |
+----+------------------+---------------+----------+---------------------+-------+-----------+
|  1 | hello world 000  | hello-world-0 |        1 | 2015-10-11 08:08:08 |     1 |        10 |
|  2 | hello world 111  | hello-world-1 |        1 | 2015-10-11 08:08:08 |     2 |        10 |
|  3 | hello world 222  | hello-world-2 |        2 | 2015-10-11 08:08:08 |     3 |        10 |
|  4 | hello world 333  | hello-world-3 |        3 | 2015-10-11 08:08:08 |     4 |        10 |
|  5 | hello world 444  | hello-world-4 |        3 | 2015-10-11 08:08:08 |     5 |        10 |
|  6 | session a update | hello-world-0 |        4 | 2015-10-11 08:08:08 |     1 |        10 |
|  7 | hello world 666  | hello-world-0 |        5 | 2015-10-11 08:08:08 |     1 |        10 |
|  8 | hello world 666  | hello-world-0 |        6 | 2015-10-11 08:08:08 |     1 |        10 |
+----+------------------+---------------+----------+---------------------+-------+-----------+
8 rows in set (0.00 sec)

mysql> update record set title = 'session a update' where authorId = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

这个更新一定是加了X锁的(排它锁)和GAP锁(间隙锁);

Session B

执行以下插入语句,其中authorId = 4;

mysql> insert into record (title,shortName,authorId,createTime,state,totalView)  values ('hello world 666','hello-world-0',4,'2015-10-11 08:08:08',6,10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到锁等待超时,看一下是等待什么锁,

> select * from INNODB_LOCKS

******************** 1. row *********************
    lock_id: 11604:64:4:11
lock_trx_id: 11604
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `test`.`record`
 lock_index: idx_author_id
 lock_space: 64
  lock_page: 4
   lock_rec: 11
  lock_data: 5, 7
******************** 2. row *********************
    lock_id: 11603:64:4:11
lock_trx_id: 11603
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `test`.`record`
 lock_index: idx_author_id
 lock_space: 64
  lock_page: 4
   lock_rec: 11
  lock_data: 5, 7
2 rows in set

可以看到lock_mode项是 X,GAP。X表示排他锁,GAP间隙锁。

可以看到lock_mode项是 X,GAP。X表示排他锁,GAP间隙锁。

可以看到lock_mode项是 X,GAP。X表示排他锁,GAP间隙锁。

再比如,其中authorId= 3;

mysql> insert into record (title,shortName,authorId,createTime,state,totalView)  values ('hello world 666','hello-world-0',3,'2015-10-11 08:08:08',6,10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

等待的锁,

> select * from INNODB_LOCKS

******************** 1. row *********************
    lock_id: 11604:64:4:10
lock_trx_id: 11604
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `test`.`record`
 lock_index: idx_author_id
 lock_space: 64
  lock_page: 4
   lock_rec: 10
  lock_data: 4, 6
******************** 2. row *********************
    lock_id: 11603:64:4:10
lock_trx_id: 11603
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`record`
 lock_index: idx_author_id
 lock_space: 64
  lock_page: 4
   lock_rec: 10
  lock_data: 4, 6
2 rows in set

这两个还是稍微有些差别的,就是在lock_mode: X 这个只有一个锁——X锁,没有间隙锁。

我们再来执行插入,其中authorId = 5 ;

mysql> insert into record (title,shortName,authorId,createTime,state,totalView)  values ('hello world 666','hello-world-0',5,'2015-10-11 08:08:08',6,10);
Query OK, 1 row affected (0.00 sec)

看到了没,居然插入成功了,说明4,5 这个间隙(简单的说)是没有上锁的。这个间隙没有间隙锁。所以执行插入成功了。

行锁(Record Lock):锁直接加在索引记录上面。

间隙锁(Gap Lock):锁加在不存在的空闲空间,可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间。

Next-Key Lock:行锁与间隙锁组合起来用就叫做Next-Key Lock。默认情况下,InnoDB工作在可重复读隔离级别下,并且以Next-Key Lock的方式对数据行进行加锁,这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合,这样,当InnoDB扫描索引记录的时候,会首先对选中的索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。如果一个间隙被事务T1加了锁,其它事务是不能在这个间隙插入记录的。

总结:间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作,以此来达到防止幻读的发生,所以间隙锁不分什么共享锁与排它锁。另外,在上面的例子中,我们选择的是一个普通(非唯一)索引字段来测试的,这不是随便选的,因为如果InnoDB扫描的是一个主键、或是一个唯一索引的话,那InnoDB只会采用行锁方式来加锁,而不会使用Next-Key Lock的方式,也就是说不会对索引之间的间隙加锁。

=========END=========

© 著作权归作者所有

秋风醉了
粉丝 253
博文 532
码字总数 405755
作品 0
朝阳
程序员
私信 提问
加载中

评论(0)

MySQL MVCC && 事务隔离级别 && 锁

MySQL MVCC && 事务隔离级别 && 锁 InnoDB多版本并发控制——MVCC http://my.oschina.net/xinxingegeya/blog/208821 MySQLS锁X锁read lockwrite lock http://my.oschina.net/xinxingegeya/bl......

秋风醉了
2014/07/30
629
0
mysql 开发进阶篇系列 6 锁问题

一.概述   在数据库中,数据是属于共享资源,为了保证并发访问的一致性,有效性,产生了锁。接下来重点讨论mysql锁机制的特点,常见的锁问题,以及解决mysql锁问题的一些方法或建议。 相比...

花阴偷移
2018/07/23
0
0
MySQL数据库事务隔离级别(Transaction Isolation Level)

数据库隔离级别有四种,应用《高性能mysql》一书中的说明: 然后说说修改事务隔离级别的方法: 1.全局修改,修改mysql.ini配置文件,在最后加上 1 #可选参数有:READ-UNCOMMITTED, READ-COMM...

嘻哈开发者
2016/01/06
115
0
mysql 4种事务隔离级别

SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。低级别的隔离级一般支持更高的并发处理,并拥有更低的系统开销。 Read Uncommitted(...

蛋定龙
2015/10/23
354
1
事务的四大特性和隔离级别

一.什么是事务 事务是应用程序中一系列严密的操作,所有操作必须成功完成,否则在每个操作中所作的所有更改都会被撤消。 二.事务的 四个特性(ACID ) 事务具有四个特性:原子性( Atomic...

LYQ1990
2016/04/29
124
1

没有更多内容

加载失败,请刷新页面

加载更多

何时使用vs ref vs out

前几天有人问我应该使用参数关键字out而不是ref 。 虽然我(我认为)理解了ref和out关键字之间的差异( 之前已经提到过 ),最好的解释似乎是ref == in and out ,什么是一些(假设的或代码的...

javail
27分钟前
37
0
宜信如何做到既满足远程办公的短时便利性需求,又不丧失安全性

对于IT互联网企业来说远程办公并不陌生,但是疫情的突然爆发,直接大规模的使用远程办公应用,势必会带来一系列的安全问题,尤其是大量隐私数据安全问题,因为此次的疫情,大量的企业内部人员...

宜信技术学院
32分钟前
29
0
如何获得元素的渲染高度?

如何获得元素的渲染高度? 假设您有一个<div>元素,其中包含一些内容。 内部的内容将扩展<div>的高度。 当您没有明确设置高度时,如何获得“渲染的”高度。 显然,我尝试过: var h = docume...

技术盛宴
42分钟前
40
0
zookeeper宕机与dubbo直连

加入zookeeper宕机后,一段时间内consumer依然能够获取provider的服务,实际上使用了本地缓存进行通讯,这也是dubbo健壮性的一种体验。 dubbo健壮性的表现: 1.监控中心宕机,不影响使用,只...

七宝1
43分钟前
44
0
一分钟了解【X-Frame-Options设置】

含义 通过设置X-Frame-Options来控制网页能否被frame或iframe嵌入。 目的 防止出现 点击劫持 :攻击者使用一个透明的iframe,覆盖在一个网页上,然后诱使用户在网页上进行操作,此时用户将在...

crazymus
50分钟前
48
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部