MySQL与PostgreSQL的 SELECT FOR UPDATE

时间：2022-03-04 04:35:25



MySQL的行级锁

MySQL 版本:

1
2
3
4
5
6
7
8
9

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.17-log |
+------------+
1 row in set (0.00 sec)

mysql>

创建测试表

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE `shortlink` (
`id`  bigint(20) NOT NULL AUTO_INCREMENT ,
`long_url`  varchar(6000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`create_time`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`update_time`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;

插入测试数据

1
2
3

INSERT INTO `test`.`shortlink` (`id`, `long_url`, `create_time`, `update_time`) VALUES ('1', 'long1', '2015-12-21 15:03:41', '2015-12-21 15:03:41');
INSERT INTO `test`.`shortlink` (`id`, `long_url`, `create_time`, `update_time`) VALUES ('2', 'long2', '2015-12-21 15:30:56', '2015-12-21 15:30:56');
INSERT INTO `test`.`shortlink` (`id`, `long_url`, `create_time`, `update_time`) VALUES ('3', 'long3', '2015-12-21 15:35:48', '2015-12-21 15:35:48');

测试SELECT FOR UPDATE

where 没有索引 for update

session1:

1
2
3
4
5
6
7
8
9
10
11
12

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

mysql> select * from shortlink where long_url = 'long2' for update;
+----+----------+---------------------+---------------------+
| id | long_url | create_time         | update_time         |
+----+----------+---------------------+---------------------+
|  2 | long2    | 2015-12-21 15:30:56 | 2015-12-24 17:25:27 |
+----+----------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

这时开启另一个session2，更新id=2的数据

1
2
3

mysql> update shortlink set long_url = 'new long url' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

发现它一直在等待.

又另一个session3，更新id=3的数据

1
2
3

mysql> update shortlink set long_url = 'new long url3' where id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

发现它也是一直在等待

where 有索引 for update

session1:

1
2
3
4
5
6
7
8
9
10
11
12

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

mysql> select * from shortlink where id = 2 for update;
+----+----------+---------------------+---------------------+
| id | long_url | create_time         | update_time         |
+----+----------+---------------------+---------------------+
|  2 | long2    | 2015-12-21 15:30:56 | 2015-12-24 17:25:27 |
+----+----------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

这时开启另一个session2，更新id=2的数据

1
2
3

mysql> update shortlink set long_url = 'new long url' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

这时开启另一个session3，更新id=3的数据（即非id=2的都可以)

1
2
3
4
5

mysql> update shortlink set long_url = 'new long url3' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

这时，发现，它可以立即更新.

MySQL for update 总结

  1. 只有 select for update 语句中使用了索引，才会是行级锁。否则就是全表锁.
  2. select for update 必须放在事务里才有效.

PostgreSQL 行级锁

PostgreSQL版本：

1
2
3
4
5
6
7

postgres=# select version();
                                                version
-------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
(1 row)

postgres=#

创建测试表

1
2
3

postgres=# create table shortlink (id serial primary key, long_url varchar(6000) not null, create_time timestamp not null default CURRENT_TIMESTAMP, update_time timestamp not null default CURRENT_TIMESTAMP);
CREATE TABLE
postgres=#

注意，PG里的serial类型，并不会自动加索引的.

插入测试数据

1
2
3
4
5
6
7

postgres=# insert into shortlink (id, long_url, create_time, update_time) values (1, 'long1', '2015-12-21 15:03:41', '2015-12-21 15:03:41');
INSERT 0 1
postgres=# insert into shortlink (id, long_url, create_time, update_time) values (2, 'long2', '2015-12-21 15:30:56', '2015-12-21 15:30:56');
INSERT 0 1
postgres=# insert into shortlink (id, long_url, create_time, update_time) values (3, 'long3', '2015-12-21 15:35:48', '2015-12-21 15:35:48');
INSERT 0 1
postgres=#

测试 SELECT FOR UPDATE

where 没有索引 for update

session1

1
2
3
4
5
6
7
8
9

postgres=# begin ;
BEGIN
postgres=# select * from shortlink where long_url = 'long2' for update;
 id | long_url |     create_time     |     update_time
----+----------+---------------------+---------------------
  2 | long2    | 2015-12-21 15:30:56 | 2015-12-21 15:30:56
(1 row)

postgres=#

这时开启另一个session2，更新select for update里的某条数据.

1
2
3
4
5
6

postgres=# set lock_timeout = 5000;
SET
postgres=# update shortlink set long_url = 'long_url2' where id = 2;
ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,2) in relation "shortlink"
postgres=#

注意，设置一下lock_timeout，默认是0,即无限等待.
这时，发现它是被锁住了.

又开启另一个session3，更新非select for udpate里的数据.

1
2
3

postgres=# update shortlink set long_url = 'long_url2' where id = 3;
UPDATE 1
postgres=#

可以发现，它是立即更新的.

where 有索引 for update

session1:

1
2
3
4
5
6
7
8
9

postgres=# begin ;
BEGIN
postgres=# select * from shortlink where id = 2 for update;
 id | long_url |     create_time     |     update_time
----+----------+---------------------+---------------------
  2 | long2    | 2015-12-21 15:30:56 | 2015-12-21 15:30:56
(1 row)

postgres=#

这时，开启另一个session去更新select for update中的某条数据:

1
2
3
4

postgres=# update shortlink set long_url = 'long_url222' where id = 2;
ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,2) in relation "shortlink"
postgres=#

又开启另一个session3，去更新非select for update中的某条数据:

1
2
3

postgres=# update shortlink set long_url = 'long_url333' where id = 3;
UPDATE 1
postgres=#

发现它是可以立即更新的.

PostgreSQL for update 总结

根据测试，PostgreSQL的行级锁控制得更好点.无论where有没有触发到索引，都是行级锁.

总结

MySQL的select for update： 要有索引for update才是行级锁，否则就是全表锁.
PostgreSQL的selct for update: 只会锁select for update那部分的结果.

感觉PostgreSQL靠谱点.哈哈.

参考资料

  1. InnoDb locks set

  2. PostgreSQL runtime config client


转自：
http://emacsist.github.io/2015/12/24/MySQL%E4%B8%8EPostgreSQL%E7%9A%84-SELECT-FOR-UPDATE/
标签：

相关文章