MySQL TOO BAD row's Range Lock Compare with PostgreSQL and Oracle

最新推荐文章于 2024-09-15 08:03:51 发布
最新推荐文章于 2024-09-15 08:03:51 发布
阅读量87 收藏
点赞数
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/15140
版权
MySQL的InnoDB引擎，当UPDATE一个范围的数据时，会锁住比预期更多的ROW，而Oracle和PostgreSQL没有这种现象.
来自《High Performance MySQL》一书。
测试版本:
MySQL 5.5.10
PostgreSQL 9.0.2
Oracle 10.2.0.4
举例如下:
1. MySQL (有索引的情况)
Session One:
mysql> create table tbl_user (id int,firstname varchar(32),lastname varchar(32),corp varchar(32),primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.08 sec)
mysql> begin;
mysql> insert into tbl_user values(1,'zhou','digoal','sky-mobi');
mysql> insert into tbl_user values(2,'zhou','digoal','sky-mobi');
mysql> insert into tbl_user values(3,'zhou','digoal','sky-mobi');
mysql> insert into tbl_user values(4,'zhou','digoal','sky-mobi');
mysql> insert into tbl_user values(5,'zhou','digoal','sky-mobi');
mysql> insert into tbl_user values(6,'zhou','digoal','sky-mobi');
mysql> insert into tbl_user values(7,'zhou','digoal','sky-mobi');
mysql> insert into tbl_user values(8,'zhou','digoal','sky-mobi');
mysql> insert into tbl_user values(9,'zhou','digoal','sky-mobi');
mysql> insert into tbl_user values(10,'zhou','digoal','sky-mobi');
mysql> commit;
# 测试range scan in INDEX
# 从结果上看，这个session应该只对id=2,3,4的三行持锁，实际上不是
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tbl_user where id<5 and id<>1 for update;
+----+-----------+----------+----------+
| id | firstname | lastname | corp     |
+----+-----------+----------+----------+
|  2 | zhou      | digoal   | sky-mobi |
|  3 | zhou      | digoal   | sky-mobi |
|  4 | zhou      | digoal   | sky-mobi |
+----+-----------+----------+----------+
3 rows in set (0.00 sec)
# 打开另一个SESSION来看看情况如何
Session TWO : 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tbl_user set corp='skymobi' where id=1;
Ctrl-C -- sending "KILL QUERY 4" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
# id=1的记录被锁
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update tbl_user set corp='skymobi' where id=5;
Ctrl-C -- sending "KILL QUERY 4" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
# id=5的记录被锁
mysql> 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tbl_user set corp='skymobi' where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.02 sec)

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

mysql> update tbl_user set corp='skymobi' where id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
# 比预期的多锁了两行id=1,5

# MySQL没有索引的情况，锁全表
Session One : 
mysql> create table tbl_user (id int,firstname varchar(32),lastname varchar(32),corp varchar(32)) engine=innodb;
mysql> select * from tbl_user where id<5 and id<>1 for update;
+------+-----------+----------+----------+
| id   | firstname | lastname | corp     |
+------+-----------+----------+----------+
|    2 | zhou      | digoal   | sky-mobi |
|    3 | zhou      | digoal   | sky-mobi |
|    4 | zhou      | digoal   | sky-mobi |
+------+-----------+----------+----------+
3 rows in set (0.00 sec)

Session Two : 

mysql> update tbl_user set corp='skymobi' where id=5;
Ctrl-C -- sending "KILL QUERY 4" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> 
mysql> update tbl_user set corp='skymobi' where id=6;
Ctrl-C -- sending "KILL QUERY 4" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> update tbl_user set corp='skymobi' where id=1;
Ctrl-C -- sending "KILL QUERY 4" to server ...
Ctrl-C -- query aborted.
^[[AERROR 1317 (70100): Query execution was interrupted

2. PostgreSQL同样的测试，不存在多锁的情况
Session One : 
digoal=> create table tbl_user (id serial,firstname varchar(32),lastname varchar(32),corp varchar(32),primary key (id));
NOTICE:  CREATE TABLE will create implicit sequence "tbl_user_id_seq" for serial column "tbl_user.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tbl_user_pkey" for table "tbl_user"
CREATE TABLE
digoal=> insert into tbl_user select generate_series(1,1000),'zhou','digoal','sky-mobi';
INSERT 0 1000
digoal=> begin;
BEGIN
digoal=> select * from tbl_user where id<5 and id<>1 for update;
 id | firstname | lastname |   corp   
----+-----------+----------+----------
  2 | zhou      | digoal   | sky-mobi
  3 | zhou      | digoal   | sky-mobi
  4 | zhou      | digoal   | sky-mobi
(3 rows)

Session Two : 
digoal=> begin;
BEGIN
digoal=> update tbl_user set corp='skymobi' where id=2;
Cancel request sent
ERROR:  canceling statement due to user request
digoal=> rollback;
ROLLBACK
digoal=> begin;
BEGIN
digoal=> update tbl_user set corp='skymobi' where id=1;
UPDATE 1
digoal=> rollback;
ROLLBACK
digoal=> begin;
BEGIN
digoal=> update tbl_user set corp='skymobi' where id=5;
UPDATE 1
digoal=> rollback;
ROLLBACK
digoal=> 

PostgreSQL无索引测试，结果和有索引一致
digoal=> create table tbl_user (id serial,firstname varchar(32),lastname varchar(32),corp varchar(32));
NOTICE:  CREATE TABLE will create implicit sequence "tbl_user_id_seq" for serial column "tbl_user.id"
CREATE TABLE
digoal=> insert into tbl_user select generate_series(1,1000),'zhou','digoal','sky-mobi';
INSERT 0 1000
digoal=> begin;
BEGIN
digoal=> select * from tbl_user where id<5 and id<>1 for update;
 id | firstname | lastname |   corp   
----+-----------+----------+----------
  2 | zhou      | digoal   | sky-mobi
  3 | zhou      | digoal   | sky-mobi
  4 | zhou      | digoal   | sky-mobi
(3 rows)

Session Two : 
digoal=> begin;
BEGIN
digoal=> update tbl_user set corp='skymobi' where id=5;
UPDATE 1
digoal=> update tbl_user set corp='skymobi' where id=1;
UPDATE 1
digoal=> update tbl_user set corp='skymobi' where id=2;
Cancel request sent
ERROR:  canceling statement due to user request
digoal=> rollback;
ROLLBACK

3. Oracle 同样的测试，不存在多锁的情况
Session One : 
SQL> create table tbl_user (id int,firstname varchar2(32),lastname varchar2(32),corp varchar2(32) ,primary key (id));

Table created.

SQL> insert into tbl_user select rownum,'zhou','digoal','sky-mobi' from dual connect by level <=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> select * from tbl_user where id<5 and id<>1 for update;

        ID FIRSTNAME                        LASTNAME                         CORP
---------- -------------------------------- -------------------------------- --------------------------------
         2 zhou                             digoal                           sky-mobi
         3 zhou                             digoal                           sky-mobi
         4 zhou                             digoal                           sky-mobi

Session Two : 
SQL> update tbl_user set corp='skymobi' where id=1;

1 row updated.

SQL> rollback;

Rollback complete.

SQL> update tbl_user set corp='skymobi' where id=2;
update tbl_user set corp='skymobi' where id=2
       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL> update tbl_user set corp='skymobi' where id=5;

1 row updated.

SQL> rollback;

Rollback complete.

# Oracle无索引和有索引结果一致
Session One : 
SQL> create table tbl_user (id int,firstname varchar2(32),lastname varchar2(32),corp varchar2(32));

Table created.

SQL> insert into tbl_user select rownum,'zhou','digoal','sky-mobi' from dual connect by level <=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> select * from tbl_user where id<5 and id<>1 for update;

        ID FIRSTNAME                        LASTNAME                         CORP
---------- -------------------------------- -------------------------------- --------------------------------
         2 zhou                             digoal                           sky-mobi
         3 zhou                             digoal                           sky-mobi
         4 zhou                             digoal                           sky-mobi

Session Two : 
SQL> update tbl_user set corp='skymobi' where id=5;

1 row updated.

SQL> update tbl_user set corp='skymobi' where id=1;

1 row updated.

SQL> update tbl_user set corp='skymobi' where id=2;
update tbl_user set corp='skymobi' where id=2
       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation


小结 : 
1. MySQL InnoDB引擎表按照范围来获取锁时，会锁住比预期更多的ROWS(使用索引略好，不使用索引的话就是全表)。
2. PostgreSQL和Oracle不存在这种情况.
越来越觉得MySQL High不起来了
3. 后续, Mysql 锁范围放大的原因. 
PostgreSQL repeatable read, serializable和read committed 隔离级别都不会造成这种锁范围放大的情况.
原因是PostgreSQL 的多版本并发控制机制利用了tuple infomask标记, 新老版本并存, committed 状态, 事务snapshot状态等信息.
并发能力相当高.
而mysql 锁范围放大和它的锁机制有关:
主要原因是mysql利用了索引来加锁. 除了read uncommitted, 其他隔离级别都会造成锁放大. 
默认隔离级别为repeatable read; 
repeatable read: 
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.

read committed:
 
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records.

weixin_34059951
关注
mysql 退化range锁_MySQL-锁总结
weixin_35334000的博客
02-05 521
锁锁机制用于管理对共享资源的并发访问。lock和latch在数据库中，lock和Latch都称为锁，但是两者意义不同。latch称为闩锁(shuang suo)，其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在InnoDB存储引擎中，latch又分为mutex互斥锁 和 rwLock读写锁。其目的是为了保证并发线程操作临界资源的正确性。通常没有死锁的检测机制。lock的对象是事...
MySQL 锁相关的优化案例
只是甲的博客
02-09 2485
备注: MySQL 5.5 测试数据: 基于信息安全考虑，我自己创建的测试表，来模拟实际应用场景。 create table test_202101 (id int(11) not null auto_increment, name varchar(200), age int, qq varchar(200), email varchar(200), status1 int(11), status2 int(11), status3 int(11), status4 int(11), st
mysql range锁,MySQL唯一索引范围锁bug修复
weixin_42356162的博客
03-17 219
commit d1b0afd75ee669f54b70794eb6dab6c121f1f179Author: Jakub Łopuszański Date: Wed Jul 17 16:34:01 2019 +0200Bug #29508068 UNNECESSARY NEXT-KEY LOCK TAKENWhen doing a SELECT...FOR [SHARE|UPDAT...
TokuDB · 行锁(row-lock)与区间锁(range-lock)
qiuyepiaoling的专栏
02-03 831
原文：http://mysql.taobao.org/monthly/2015/04/03/ 简介 TokuDB使用LockTree(ft-index/locktree)来维护事务的锁状态(row-lock和range-lock)，LockTree的数据结构是一个Binary Tree。  本篇将通过几个“栗子”来谈谈TokuDB的row-lock和range-lock。  表
InnoDB锁机制之Gap Lock、Next-Key Lock、Record Lock解析
欧辰的专栏
05-26 1万+
MySQL InnoDB支持三种行锁定方式： l   行锁（Record Lock）:锁直接加在索引记录上面，锁住的是key。 l   间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。 l   Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。   默
MySQL vs PostgreSQL
06-09 427
MySQL vs PostgreSQL MySQL vs PostgreSQL - WikiVSMySQL vs PostgreSQL From WikiVS, the open comparison website Jump to: navigation, s...
学习笔记：The Best of MySQL Forum
weixin_30340745的博客
12-09 762
http://mysql.rjweb.org/bestof.html I have tagged many of the better forum threads. 'Better' is based on how good I thing the answer was. (<bias> I wrote many, but not all, of the better an...
Java 之父 James Gosling 最新访谈：JIT 很好，但不适合所有语言
程序员光剑
08-27 1万+
计算机编程确实是一门艺术。James Gosling，“Java 之父”, 完成了 Java 的原始设计，并实现了 Java 最初版本的编译器和虚拟机。90年代初，James Goslin...
Recommended reading list: DB2 for Linux, UNIX, and Windows database administration
风之去向
06-16 2422
IntroductionThis article provides a list of online learning resources for DB2 for Linux, UNIX ®, and Windows ®. The DB2 Information Cent
MySQL、PostgreSQL、Oracle比较及数据库基础知识
01-05
MySQL、PostgreSQL、Oracle比较； 数据库基础知识.ppt
一文带你了解三大开源关系型数据库：SQLite、MySQL和PostgreSQL
热门推荐
dvlinker的技术专栏
10-08 4万+
一文带你了解三大开源关系型数据库：SQLite、MySQL和PostgreSQL。
Postgresql与Oracle语法区别
nzbing的博客
12-27 6252
一、事务 1、基本概念:Transaction是数据库管理系统DBMS执行过程中的一个逻辑单元,是一个sql命令组成的序列.其特点在于，当事务被提交DBMS后,DBMS需要确保所有的操作被完成;如果事务中有的操作未完成,那么所有操作都将回滚,回到事务提交之前的状态 2、区别: Postgresql中的事务要手动开启,否则直接写update/insert/delete是默认自动提交的,手动开启方式如下 begin;-开启事务 update test set name = 'c' where id = 1;
SpringBoot+PostgreSQL/Oracle/MySQL连接配置
dream_heheda的博客
10-16 1699
1. SpringBoot 配置连接mysql数据库 添加依赖 <!-- mysql驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.6</version> </dependency> 在application.p
4.网络编程
weixin_45476535的博客
09-14 368
程序注册端口：1024-49151。单个协议下，端口号不能冲突。公有端口0-1023。
Oracle EBS中AR模块的财务流程概览
qq_36620997的博客
09-14 699
AR模块的财务流程概览
Oracle 12c 及以上版本补丁更新说明及下载方法
zd1320732的专栏
09-10 369
参考下面的文章，会对补丁更新的流程有一定的了解。
55 mysql 的登录认证流程
最新发布
970655147的专栏
09-15 577
这里我们来看一下 mysql 的认证的流程我们这里仅仅看 我们最常见的一个 认证的处理流程我们经常会登录的时候 碰到各种异常信息大概的流程是这样客户端和服务器建立连接之后, 服务器向客户端发送 salt然后 客户端根据 salt 将客户端传入的密码加密之后, 以及相关登录信息传递给服务器然后 服务器进行验证, 验证失败, 响应对应的错误信息给客户端服务器发送 salt 的信息如下客户端发送的认证请求如下服务器响应的认证失败信息如下。
转行软件测试工程师经验总结
2402_84109700的博客
09-12 537
数据安全官，CCRC-DSA数据安全评估师，CCRC-DCO数据合规官，CDO首席数据官, ITSS IT服务项目经理，IT服务项目工程师，ISO27001,CISP,软考,CISAW应急服务方向,CISAW渗透测试方向,软考,CCSC网络安全能力,工信部教考中心计算机网络安全相关认证办理。在测试理论方面，要熟悉常用的测试用例设计方法，理解不同测试类型的特点，掌握测试用例的基本格式，以及测试结束的标准。最终，我如愿以偿地加入了一家互联网公司，这里的办公环境优越，薪资也较之前的工作有所提升，使我心情愉悦。
MySQL、PostgreSQL与Oracle：数据库基础与比较
本文档深入探讨了数据库的基本概念，重点关注MySQL、PostgreSQL和Oracle这三种主流的关系型数据库管理系统。首先，文章介绍了数据库的基础概念，包括数据库的定义、系统特点以及其在现实世界、信息世界和数据世界中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值