MySQL与PostgreSQL的 SELECT FOR UPDATE
Contents
MySQL的行级锁
MySQL 版本:
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.17-log |
+------------+
1 row in set (0.00 sec)
mysql>
创建测试表
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
;
插入测试数据
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:
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的数据
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的数据
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:
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的数据
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的都可以)
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 总结
- 只有 select for update 语句中使用了索引，才会是行级锁。否则就是全表锁.
- select for update 必须放在事务里才有效.
PostgreSQL 行级锁
PostgreSQL版本：
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=#
创建测试表
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类型，并不会自动加索引的.
插入测试数据
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
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里的某条数据.
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里的数据.
postgres=# update shortlink set long_url = 'long_url2' where id = 3;
UPDATE 1
postgres=#
可以发现，它是立即更新的.
where 有索引 for update
session1:
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中的某条数据:
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中的某条数据:
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靠谱点.哈哈.