本文簡單介紹了PostgreSQL Locks(Table-Level)的基礎知識,原文詳見[Postgres Locking Revealed](https://engineering.nordeus.com/postgres-locking-revealed/),有所刪減和調整.

PostgreSQL把鎖分為三類,table-level, row-level and advisory locks.Table和Row級鎖可以是顯式或隱式鎖,advisory locks通常是顯式鎖.顯式鎖在顯式使用者請求時獲得,而隱式鎖則透過標準SQL命令獲得.

**Table-level Locks**
大多數的table-level locks透過內建的SQL命令獲得,也可透過LOCK命令顯式獲得.可用的table-level locks包括:
ACCESS SHARE – SELECT命令請求該型別的鎖,通常情況下只涉及查詢相關的表.
>The SELECT command acquires this lock on table(s) referenced in query. General rule is that all queries that only read table acquire this lock.

```
[local]:5432 pg12@testdb=# begin
pg12@testdb-# ;
BEGIN
Time: 0.958 ms
[local]:5432 pg12@testdb=#* select * from t_lock limit 1;
 id 
----
  1
(1 row)

Time: 6.995 ms
```

ROW SHARE – SELECT FOR UPDATE和SELECT FOR SHARE命令請求該鎖
>The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire this lock on target table (as well as ACCESS SHARE lock on all referenced tables in query).

```
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 0.941 ms
[local]:5432 pg12@testdb=#* SELECT * from t_lock FOR UPDATE limit 1;
 id 
----
  1
(1 row)

Time: 3.005 ms
```

ROW EXCLUSIVE – UPDATE, INSERT 和 DELETE命令請求該鎖.
>The UPDATE, INSERT and DELETE commands acquire this lock on target table (as well as ACCESS SHARE lock on all referenced tables in query). General rule is that all queries that modify table acquire this lock.

Time: 2.615 ms
[local]:5432 pg12@testdb=#* end;
COMMIT
Time: 1.982 ms
```

SHARE UPDATE EXCLUSIVE – VACUUM (不包括FULL選項), ANALYZE, CREATE INDEX CONCURRENTLY
>The VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, and some forms of ALTER TABLE commands acquire this lock.

Time: 3.067 ms
[local]:5432 pg12@testdb=#* end;
COMMIT
Time: 1.841 ms
```

SHARE – CREATE INDEX命令請求該鎖
>The CREATE INDEX command acquires lock on table referenced in query.

Time: 2.652 ms
[local]:5432 pg12@testdb=#* end;
COMMIT
Time: 1.966 ms
```

SHARE ROW EXCLUSIVE – 不能由命令隱式獲得,只能透過lock table命令顯式獲取
>Not acquired implicitly by any command.

Time: 2.563 ms
[local]:5432 pg12@testdb=#* end;
COMMIT
Time: 0.817 ms
```

EXCLUSIVE – 該模式只允許讀取已獲取該鎖的事務並行處理.
>This lock mode allows only reads to process in parallel with transaction that acquired this lock. It is not acquired implicitly by any command.

Time: 2.564 ms
[local]:5432 pg12@testdb=#* end;
COMMIT
Time: 1.133 ms
[local]:5432 pg12@testdb=# 
```

ACCESS EXCLUSIVE – ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, 和 VACUUM FULL明確請求該鎖.該模式為LOCK命令的預設模式.
>The ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, and VACUUM FULL commands acquire lock on table referenced in query. This mode is default mode of LOCK command.

Time: 2.625 ms
[local]:5432 pg12@testdb=#* end;
COMMIT
Time: 2.046 ms
```

特別注意的是,雖然Lock模式中含有Row字眼,但這些Locks都是table-level locks.
下面是table-level locks的衝突列表.兩個事務不能同時持有同一個表上存在衝突的鎖,同一個事務則沒有該限制.

![](http://img.blog.itpub.net/blog/2019/08/08/fbd7bd67d478b078.png?x-oss-process=style/bb)

DML&DDL的鎖型別和衝突列表
![](http://img.blog.itpub.net/blog/2019/08/14/9570d04c48f319b4.png?x-oss-process=style/bb)

```
-- session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 1.008 ms
[local]:5432 pg12@testdb=#* lock table t_lock in SHARE ROW EXCLUSIVE mode;
LOCK TABLE
Time: 3790.558 ms (00:03.791)
[local]:5432 pg12@testdb=#*

Time: 2.724 ms
[local]:5432 pg12@testdb=#* end;
COMMIT
Time: 0.837 ms
```
不存在衝突的鎖可由多個事務同時持有,當獲取某種lock後,一直持有到事務結束.如建立了savepoint後獲得lock,在回滾至savepoint時立即釋放鎖.

**參考資料**
[Postgres Locking Revealed](https://engineering.nordeus.com/postgres-locking-revealed/)

PostgreSQL DBA(71) - Locks(Table-Level)：What You Should Know

相關文章