MySQL TOO BAD row's Range Lock Compare with PostgreSQL and Oracle-阿里云开发者社区

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

2016-03-31 1772

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介：

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;

http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html#isolevel_repeatable-read

http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_gap

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.

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

热门文章

最新文章

相关课程

相关电子书

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

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

热门文章

最新文章

相关课程

相关电子书

推荐镜像