AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2016-03-31 1655
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL DuckDB 分析主实例，集群系列 8核16GB
云原生数据库 PolarDB MySQL 版，通用型 2核8GB 50GB
简介：
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.

文章标签：
云数据库 RDS MySQL 版
云原生数据库 PolarDB
关系型数据库
PostgreSQL
MySQL
索引
Oracle
关键词：
云数据库 RDS MySQL 版postgresql
mysql PostgreSQL
PostgreSQL oracle
mysql Oracle数据库一键上云
云数据库 RDS MySQL 版oracle
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
德哥
目录
相关文章
游客vphb4ae2je2zi
|
3月前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB，多种引擎优惠上线！MySQL倚天版88元/年，SQL Server 2核4G仅299元/年，PostgreSQL 227元/年起。高可用、可弹性伸缩，安全稳定。详情见官网活动页。
游客vphb4ae2je2zi
771 152
上云就上阿狸云
|
3月前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格：MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型，包括关系型与NoSQL，主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起，支持按需付费与优惠套餐，适用于各类应用场景。
上云就上阿狸云
802 4
程序员爱钓鱼
|
6月前
|
SQL 关系型数据库 MySQL
Go语言数据库编程：使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口，支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容，涵盖实际开发中常用的技巧与注意事项，适合快速掌握Go语言数据库编程基础。
程序员爱钓鱼
494 62
上云就上阿狸云
|
3月前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎，提供高性价比、稳定安全的云数据库服务，适用于多种行业与业务场景。
上云就上阿狸云
288 0
赵渝强老师
|
7月前
|
Oracle 关系型数据库 数据库
【赵渝强老师】在PostgreSQL中访问Oracle
本文介绍了如何在PostgreSQL中使用oracle_fdw扩展访问Oracle数据库数据。首先需从Oracle官网下载三个Instance Client安装包并解压，设置Oracle环境变量。接着从GitHub下载oracle_fdw扩展，配置pg_config环境变量后编译安装。之后启动PostgreSQL服务器，在数据库中创建oracle_fdw扩展及外部数据库服务，建立用户映射。最后通过创建外部表实现对Oracle数据的访问。文末附有具体操作步骤与示例代码。
赵渝强老师
250 6
【赵渝强老师】在PostgreSQL中访问Oracle
qhhf_地道酬勤
|
存储 关系型数据库 MySQL
一个项目用5款数据库？MySQL、PostgreSQL、ClickHouse、MongoDB区别，适用场景
一个项目用5款数据库？MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
qhhf_地道酬勤
2162 0
金桔数科
|
8月前
|
Oracle 关系型数据库 MySQL
Oracle linux 8 二进制安装 MySQL 8.4企业版
Oracle linux 8 二进制安装 MySQL 8.4企业版
金桔数科
319 1
互联网课堂
|
10月前
|
SQL Oracle 关系型数据库
MySQL 和 Oracle 的区别？
本文对比了Oracle和MySQL数据库的多个方面。Oracle适用于大型数据库，支持高并发和大访问量，市场占有率为40%，安装占用空间较大，约3G；而MySQL适合中小型应用，是开源免费的，安装仅需152M。两者在主键生成、字符串处理、SQL语句、事务处理等方面存在差异。Oracle功能更为强大，尤其在企业级应用中表现突出，而MySQL则以简单易用见长。
互联网课堂
1236 7
MySQL 和 Oracle 的区别？
游客kufrkwrbkmpsa
|
9月前
|
Oracle 关系型数据库 MySQL
使用崖山YMP 迁移 Oracle/MySQL 至YashanDB 23.2 验证测试
这篇文章是作者尚雷关于使用崖山YMP迁移Oracle/MySQL至YashanDB 23.2的验证测试分享。介绍了YMP的产品信息，包括架构、版本支持等，还详细阐述了外置库部署、YMP部署、访问YMP、数据源管理、任务管理（创建任务、迁移配置、离线迁移、校验初始化、一致性校验）及MySQL迁移的全过程。
游客kufrkwrbkmpsa
241 2
蓝易云
|
11月前
|
监控 Oracle 关系型数据库
Mysql、Oracle审计日志的开启
通过上述步骤，可以在 MySQL 和 Oracle 数据库中启用和配置审计日志。这些日志对于监控数据库操作、提高安全性和满足合规性要求非常重要。确保正确配置审计参数和策略，定期查看和分析审计日志，有助于及时发现并处理潜在的安全问题。
蓝易云
679 11

热门文章

最新文章

  • 1
    PostgreSQL 批量权限 管理方法
  • 2
    PostgreSQL 聚合函数讲解 - 6 分组排序聚合
  • 3
    PostgreSQL Oracle 兼容性之 - WM_SYS.WM_CONCAT
  • 4
    德哥PG系列课程直播（第12讲）：PostgreSQL 物联网最佳实践
  • 5
    Windows下PostgreSQL数据库的安装和配置
  • 6
    PostgreSQL imgsmlr 插件安装
  • 7
    zabbix template pg_monz for PostgreSQL
  • 8
    如何使用shp2pgsql 将shp格式的GIS数据导入到PostgreSQL
  • 9
    PostgreSQL10.5安装后（Win10）环境变量配置与运行
  • 10
    《PolarDB for PostgreSQL源码与应用实战》——PolarDB for PostgreSQL用SQL做数据分析（3）
  • 1
    【紧急救援】MySQL CPU 100%！一套组合拳教你快速定位并解决！
    283
  • 2
    MySQL中日期和时间函数的使用指南
    203
  • 3
    MySQL 与 Redis 如何保证双写一致性？
    389
  • 4
    【赵渝强老师】MySQL的事务隔离级别
    275
  • 5
    【Docker】（3）学习Docker中 镜像与容器数据卷、映射关系！手把手带你安装 MySql主从同步 和 Redis三主三从集群！并且进行主从切换与扩容操作，还有分析 哈希分区 等知识点！
    435
  • 6
    MySQL源码编译安装
    300
  • 7
    MySQL二进制包安装
    286
  • 8
    MySQL包安装 -- SUSE系列（离线RPM包安装MySQL）
    386
  • 9
    MySQL包安装 -- SUSE系列（SUSE资源库安装MySQL）
    213
  • 10
    MySQL包安装 -- Debian系列（离线DEB包安装MySQL）
    879

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云MySQL云数据库产品体系介绍
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • oracle
    • 下一篇
    阿里云服务器快照备份数据怎么下载到本地保存？