MySQL与PostgreSQL的 SELECT FOR UPDATE

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/