PostgresSQL Lock的七条优化方案（译文）

1：不要在增加字段时直接设置默认值
2：在加锁时尽量设置超时时间
3：创建索引时使用CONCURRENTLY
4: 尽可能的在最后执行重量级的操作
5: 使用最小的锁粒度增加为表增加主键
6: 永远不要使用 VACUUM FULL
7: 避免因为顺序问题导致的死锁

原文: When Postgres blocks: 7 tips for dealing with locks

原文章是针对的PostgresSQL，但其中的部分优化规则同样适用于Mysql，只是有些语法Mysql不支持，但可以借鉴思路。

1：不要在增加字段时直接设置默认值

When you add a column to a table in production, never specify a default.

在使用add column时，postgres会在目标表上加表级锁，会阻塞所有的读和写操作。如果设置了默认值，postgres会扫描整个表，为所有的行增加该默认值，如果表的体量特别大，会导致该表长时间不可用。

错误示例

ALTER TABLE items ADD COLUMN last_update timestamptz DEFAULT now();

推荐写法

-- select, update, insert, and delete 操作都会被block
ALTER TABLE items ADD COLUMN last_update timestamptz;
-- update语句只会block部分行的读和写
UPDATE items SET last_update = now();

更好的写法

do {
  numRowsUpdated = executeUpdate(
    "UPDATE items SET last_update = ? " +
    "WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)",
    now);
} while (numRowsUpdate > 0);

这种写法每次只会block一部分行。

2：在加锁时尽量设置超时时间

每个锁在PostgreSQL中都有一个对应的队列，如果一个事务B尝试获取一个与事务A持有的锁冲突的锁，事务B会进入等待队列。如果此时，事务C开始，此时锁检测机制不仅会检测C与A的冲突，还会检测C与B的冲突，以及其他在锁队列中的事务。
这意味着即使你的DDL语句运行非常快，但它有可能会和其他事务冲突，导致长时间的锁等待。并且在该语句后面执行的事务（针对当前DDL表）也会被阻塞住。

Bad Code

ALTER TABLE items ADD COLUMN last_update timestamptz;

推荐做法

SET lock_timeout TO '2s'
ALTER TABLE items ADD COLUMN last_update timestamptz;

通过设置lock_timeout，如果DDL语句在等待相应的时间后，未能获得锁，会直接失败。优点是不会block后面的查询太久，缺点是ALTER TABLE语句会失败。你可以在DDL之前，通过查询pg_stat_activity 来查看是否有长事务在运行，

Mysql不支持该语法，但AliSQL支持：

ALTER TABLE tbl_name NOWAIT add column ...ALTER TABLE tbl_name WAIT N add column ...

MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

3：创建索引时使用`CONCURRENTLY`

Always create your indexes concurrently.

在一个大表上创建索引可能会花费几个小时的时间，常规的 CREATE INDEX 在此期间会阻塞所有的写操作. 虽然并不会阻塞SELECT类语法, 但这种情况仍然难以令人接收，可以尝试使用CREATE INDEX CONCURRENTLY语法.

Bad Code

-- 阻塞所有的写
CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops);

推荐写法

-- 只阻塞其他DDL操作
CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops);

并行创建索引并没有什么缺点。如果创建索引失败，并不会回滚，而是标记当前索引无效：unfinished (“invalid”)，如果出现这种情况，可以通过DROP INDEX CONCURRENTLY items_value_idx删掉该索引，并重试。

4: 尽可能的在最后执行重量级的操作

如果你需要运行一个语句，该语句会获取一个表级锁，尽量将该语句放在事务的最后运行。这样可以尽可能的缩短阻塞其他事务的时间。

例如，你想完整的替换一个表的内容，不要这样做:

BEGIN;
-- reads and writes blocked from here:
TRUNCATE items;
-- long-running operation:
\COPY items FROM 'newdata.csv' WITH CSV 
COMMIT;

推荐做法, 将数据读取到新表，然后删除老表，将新表重命名成老表:

BEGIN;
CREATE TABLE items_new (LIKE items INCLUDING ALL);
-- long-running operation:
\COPY items_new FROM 'newdata.csv' WITH CSV
-- reads and writes blocked from here:
DROP TABLE items;
ALTER TABLE items_new RENAME TO items;
COMMIT;

有一个问题需要注意，这种做法并不会阻塞其他事务的写操作，导致我们执行drop时，可能该表的内容已经变更了。为了避免这种情况，我们可以在事务开始时手动增加排他锁(X锁), 阻塞住其他写操作，但不会阻塞读操作。

BEGIN;
LOCK items IN EXCLUSIVE MODE;
...

有时候，手动控制锁的粒度是更高的选择。

注意，Mysql中的排他锁是for update，该操作会block其他的读和写，可以通过设置lock in shared mode共享锁，来避免block读操作。

5: 使用最小的锁粒度增加为表增加主键

如果你在创建表时忘记了增加主键，那么你可能需要在一个已创建的表上增加主键。在Postgres中，可以通过ALTER TABLE为表增加主键，但是增加主键会构建索引，这可能会持续很长时间，所有的查询都会被阻塞住。

ALTER TABLE items ADD PRIMARY KEY (id); -- 阻塞其他查询很长时间

然而，你可以通过CREATE UNIQUE INDEX CONCURRENTLY并发的执行所有的重型操作（创建索引），然后将唯一索引升级为主键，该操作执行很快。

CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); -- takes a long time, but doesn’t block queries
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk;  -- blocks queries, but only very briefly

通过将创建主键拆分成两个过程，这种做法几乎不会影响其他用户。

6: 永远不要使用 VACUUM FULL

有时postgres的使用体验可能并不是太好。虽然有时候，VACUUM FULL听起来像是你想做的事情，清楚postgres的磁盘占用空间（脏数据的），但更合适的命令应该是：

PLEASE FREEZE MY DATABASE FOR HOURS;

VACUUM FULL 重写整个库到磁盘上，可能会持续几个小时或者几天的时间，并且会阻塞住所有的查询操作。尽管有些场景需要使用VACUUM FULL, 例如一个库曾经很大，但现在很小了。你可能有其他的使用场景。

尽管你应该调整你的autovacuum设置来更好的嗯使用索引优化查询过程，你可以偶尔使用VACUUM，但绝对不要使用VACUUM FULL。

7: 避免因为顺序问题导致的死锁

如果你已经使用PostgreSQL一段时间了，你可能看到过如下错误：

ERROR:  deadlock detected
DETAIL:  Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483.
Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661.

这发生在两个并发的事务以不同的顺序获取锁。例如，一个事务执行以下命令：

BEGIN;
UPDATE items SET counter = counter + 1 WHERE key = 'hello'; -- grabs lock on hello
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- blocks waiting for world
END;

同时, 其他事务可能以不同的顺序执行相同的语句：

BEGIN
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- grabs lock on world
UPDATE items SET counter = counter + 1 WHERE key = 'hello';  -- blocks waiting for hello
END;

如果这些事务同事阻塞，很有可能他们因为等待彼此，而永远不会结束。Postgres在一段时间会检测这种情况，并且回滚其中的一个事务，让另外一个事务成功完成。当这种情况发生，你应该去检查你的应用服务中是否有这种使用场景。如果两个事务都是先修改hello，后修改word，那么第二个事务在修改hello时，就会被第一个事务阻塞住。