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

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

2016-03-31 1548
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 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
云数据库 RDS MySQL 版lock
mysql Oracle数据库一键上云
云数据库 RDS MySQL 版oracle
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
德哥
目录
相关文章
qhhf_地道酬勤
|
2月前
|
存储 关系型数据库 MySQL
一个项目用5款数据库？MySQL、PostgreSQL、ClickHouse、MongoDB区别，适用场景
一个项目用5款数据库？MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
qhhf_地道酬勤
414 0
叫个什么名字
|
3月前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择：MySQL、PostgreSQL 还是 NoSQL？
在微服务架构中，数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中，我们将深入探讨传统的关系型数据库（如 MySQL 和 PostgreSQL）与现代 NoSQL 数据库的优劣势，并分析在微服务架构下的最佳实践。
叫个什么名字
458 9
栈江湖
|
6天前
|
关系型数据库 MySQL 数据库
市场领先者MySQL的挑战者：PostgreSQL的崛起
PostgreSQL（简称PG）是世界上最先进的开源对象关系型数据库，起源于1986年的加州大学伯克利分校POSTGRES项目。它以其丰富的功能、强大的扩展性和数据完整性著称，支持复杂数据类型、MVCC、全文检索和地理空间数据处理等特性。尽管市场份额略低于MySQL，但PG在全球范围内广泛应用，受到Google、AWS、Microsoft等知名公司支持。常用的客户端工具包括PgAdmin、Navicat和DBeaver。
栈江湖
24 4
众所周知
|
1月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL：选择适合你的开源数据库
在众多开源数据库中，MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而，它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较，以帮助您选择更适合您需求的开源数据库。
众所周知
180 4
刘大猫.
|
1月前
|
关系型数据库 MySQL PostgreSQL
postgresql和mysql中的limit使用方法
postgresql和mysql中的limit使用方法
刘大猫.
57 1
程序员小海绵
|
3月前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别？从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
程序员小海绵
719 18
猿java
|
2月前
|
关系型数据库 MySQL Java
MySQL数据锁：Record Lock，Gap Lock 和 Next-Key Lock
本文基于 MySQL 8.0.30 版本及 InnoDB 引擎，深入解析三种行锁机制：记录锁（Record Lock）、间隙锁（Gap Lock）和临键锁（Next-key Lock）。记录锁锁定索引记录，确保事务唯一修改；间隙锁锁定索引间的间隙，防止新记录插入；临键锁结合两者，锁定范围并记录自身，有效避免幻读现象。通过具体示例展示了不同锁的作用机制及其在并发控制中的应用。
猿java
271 2
糜终
|
3月前
|
Oracle NoSQL 关系型数据库
主流数据库对比：MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比：MySQL、PostgreSQL、Oracle和Redis的优缺点分析
糜终
654 2
qhhf_地道酬勤
|
2月前
|
存储 关系型数据库 MySQL
四种数据库对比MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
四种数据库对比 MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
qhhf_地道酬勤
326 0
蓝易云
|
2月前
|
Oracle 关系型数据库 MySQL
shell获取多个oracle库mysql库所有的表
请注意，此脚本假设你有足够的权限访问所有提到的数据库。在实际部署前，请确保对脚本中的数据库凭据、主机名和端口进行适当的修改和验证。此外，处理数据库操作时，务必谨慎操作，避免因错误的脚本执行造成数据损坏或服务中断。
蓝易云
49 0

热门文章

最新文章

  • 1
    【赵渝强老师】PostgreSQL的段、区和块
  • 2
    【赵渝强老师】PostgreSQL的控制文件
  • 3
    【赵渝强老师】PostgreSQL的服务器日志文件
  • 4
    【赵渝强老师】PostgreSQL中的模式
  • 5
    【赵渝强老师】PostgreSQL的参数文件
  • 6
    【赵渝强老师】PostgreSQL的数据库
  • 7
    【赵渝强老师】PostgreSQL的WAL预写日志文件
  • 8
    【赵渝强老师】PostgreSQL的表空间
  • 9
    【赵渝强老师】PostgreSQL的运行日志文件
  • 10
    【赵渝强老师】PostgreSQL的物理存储结构
  • 1
    从ORACLE源进行批量数据迁移到GBase8a参考示例
    30
  • 2
    数据库数据恢复—ORACLE常见故障的数据恢复方案
    62
  • 3
    服务器数据恢复—华为S5300存储Oracle数据库恢复案例
    19
  • 4
    Oracle 从 DMP 文件中恢复指定表的步骤
    43
  • 5
    Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
    42
  • 6
    Centos7下图形化部署单点KFS同步工具并将Oracle增量同步到KES
    39
  • 7
    oracle数据恢复—Oracle数据库文件大小变为0kb的数据恢复案例
    26
  • 8
    Oracle Linux 9.5 正式版发布 - Oracle 提供支持 RHEL 兼容发行版
    64
  • 9
    Oracle与GreatSQL差异：更改唯一索引列
    30
  • 10
    Linux平台Oracle开机自启动设置
    118

    • 相关课程

    更多
  • PolarDB for PostgreSQL 开源人才初级认证培训课程
  • PolarDB for PostgreSQL 内核解读系列课程
  • 7天突破PolarDB for PostgreSQL 2022版
  • PolarDB for PostgreSQL 入门
  • PostgreSQL云上开发实践
  • PostgreSQL实战进阶

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • PolarDB for PostgreSQL 闪回特性体验

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • oracle
    • 下一篇
    DataWorks售前咨询