---
title: postgres 併發
---
[TOC]
###### tags: `Database`

# postgres 併發

## 隔離等級

必讀官方文件說明： http://www.postgres.cn/docs/9.3/transaction-iso.html

### 重點：

1. 在 PostgreSQL 內部實際上實作的只有三種隔離等級: Read committed, Repeatable read(使用这个级别的应用必须准备好重试事务,因为可能会发生串行化失败) , Serializable。 預設為 Read committed

2. Read commited(RC) 與 Repeatable read(RR) 的具體差異：

a. RR需要處理交易重試: RC 將會在事務過程中讀取到其他會話的commit，使得在事務中兩次讀取內容不一致，其他會話的增刪改事務一樣可以繼續； RR 在事務中查詢內容皆為當前會話的事務開啟時首次query的snapshot(如果開啟rr事務，沒有query目標表，此時被其他會話改變目標資料, rr事務此時才建立快照，因此會在新的基礎上修改資料), 後續內容不會被其他會話的commit影響，如果內容被其他會話改過，異動內容時會報 `ERROR: could not serialize access due to concurrent update`， 必須重開事務。 因此使用RR isolation, 需要在代碼層面處理交易重試

b. RC特性可能導致某些交易處理異常: 如果交易都是單純的以id為where條件，即使併發仍然可能達到如預期的結果。 但是也有許多交易是帶有比較複雜的條件，或者有些表根本沒有設置id，就可能導致交易結果異常:
例如update ....where user_name = A and age = 18, 如果在併發中其他會話改變了其中任一個條件，都將使當前會話的更新失敗。 這時候 RR的嚴謹特性就可以避免類似錯誤發生

3. Serializable效能差，一般盡量不使用，但psql沒有間隙鎖，因此需要避免幻讀的場景，要透過鎖表、或者採用Serializable

psql 在Serializable採用謂詞鎖，它能够判断在它先运行的情况下，什么时候一个写操作会对一个并发事务中之前读取的结果产生影响。在PostgreSQL中，这些锁并不导致任何阻塞，并且因此不会导致死锁。 相比下鎖表對性能的影響會是更加嚴重，死鎖的機率也會上升

如要採用，且系統應用場景對效能要求時，參考官方文檔推薦的優化效能配套措施

4. serializable視圖資料的建立timing: 假設打算合計表內每筆資料的金額時，一張表正在不斷被其他會話新增資料，當一個串行級別開始事務，它尚未有其他表的視圖，在進入事務第一次對table select時建立起視圖，之後其他會話提交的資料不會對我們進行的合計結果產生影響

5. 目前測試心得是認為，一般時候使用rc+一些技巧即可應用大多場景，需要更嚴謹的避免不可重複讀、update為空對象等情境使用RR, 會對整表處理的，在事務中採用臨時變更隔離政策的方式改成serializable

### 操作
doc: http://www.postgres.cn/docs/9.3/sql-set-transaction.html

1. 查看當前事務隔離級別:
`
show transaction_isolation;
`
2. 查看預設隔離級別：
`show default_transaction_isolation;`

3. 为当前事务设置特性，对后面的事务没有影响：
`set transaction ISOLATION LEVEL [level]`

如果执行SET TRANSACTION之前没有执行START TRANSACTION 或BEGIN，那么它会显得没有效果一样，因为事务将立即结束

4. 为一个会话中随后的每个事务设置的事务特性。 在单独的事务中，可以用SET TRANSACTION覆盖这些默认事务特性:
`SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL [level]`

5. 開啟事務同時设置当前事务隔离级别:
`START TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
或：
BEGIN ISOLATION LEVEL READ UNCOMMITTED READ WRITE;`

6. 修改預設隔離級別：
`set default_transaction_isolation='repeatable read';`
需重啟docker or db

7. 查看数据库事务隔离级别 (跟 2可能一樣效果)
`SELECT name, setting FROM pg_settings WHERE name ='default_transaction_isolation';`
或
`SELECT current_setting('default_transaction_isolation');`

8. 设置全局事务隔离级别 (跟 6 可能一樣效果，需要時再測試一下即可，不是這裡重點)
方法一：修改配置文件postgresql.conf，设置默认的隔离级别，如下：
vim postgresql.conf
default_transaction_isolation = 'read committed'

重新加载配置：
pg_ctl -D /data/pg reload

方法二：`alter system set default_transaction_isolation to 'REPEATABLE READ';`
需重啟docker or db

9. 修改time out
`SET LOCAL lock_timeout = '1s'`;

## psql lock mode
ACCESS SHARE，ROW SHARE， ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE

##### 注意：PostgreSQL採用多版本併發控制（MVCC），以確保當查詢需要修改或刪除資料時，資料在高併發環境中可用且一致。 由於每個事務都使用其資料庫的副本，因此寫入或讀取操作都不會阻塞對方。 -- 因此防止超賣、餘額為負數等情境，需要搭配其他的方法

https://hevodata.com/learn/postgresql-locks/#table

## 鎖行鎖表
select for update [nowait];
select for share [nowait];
set local lock_timeout='3s'; 设置lock超时时间,0=取消
在事务处理模块(BEGIN/COMMIT)中才能生效
psql沒有間隙鎖，即使手動鎖不存在的目標範圍也不會生效

#### postgres 在rc, rr模式下 select for update 有帶入where條件情況下，無論有無使用到索引，都只會鎖行，除非不帶入where。 這點跟mysql不同，算是psql在效能上的進化

#### for update鎖行，不會阻擋其他會話新增紀錄，需要鎖表或者串行
#### 连表查询加锁时，不支持单边连接形式
例如：

select u.*,r.* from db_user u left join db_role r on u.roleid=r.id for update;

支持以下形式，并锁住了两个表中关联的数据：

select u.*,r.* from db_user u, db_role r where u.roleid=r.id for update;

測試案例:https://blog.csdn.net/qq_40891009/article/details/106007658

#### 查看鎖表予解除 1
--查询是否锁表了
select oid from pg_class where relname='lockTableName';
select pid from pg_locks where relation='上面查出的oid';
--如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(上面查到的pid);

#### 查看鎖表予解除 2
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname

from pg_locks a

join pg_class b on a.relation = b.oid

where upper(b.relname) = 'TABLE_NAME';

以上为查询某表上是否存在锁的SQL语句。

查到后发现确实存在锁，如下：

locktype | database | pid | mode | relation | relname

----------+----------+-------+-----------------+----------+---------

relation | 439791 | 26752 | AccessShareLock | 2851428 |table_name

relation | 439791 | 26752 | ExclusiveLock | 2851428 |table_name

再根据上面查出来的pid去表pg_stat_activity查询一下该锁对应的SQL语句：

select usename,current_query ,query_start,procpid,client_addr from pg_stat_activity where procpid=17509;

如下：

usename | current_query | query_start | procpid | client_addr

-----------+---------------------------------------------------------------------------------------------------------------+-------------------------------+---------+----------------

gpcluster | DELETE FROM TABLE_NAME WHERE A = 1 | 2011-05-14 09:35:47.721173+08 | 17509 | 192.168.165.18

(1 row)

通过以上可以发现，就是上面的锁导致该语句一直挂在那里。然后把该锁结束掉后，应用很快跑完。

然后核查应用的代码，发现代码里面两个事务都没有提交操作。后增加提交操作后，重新跑数，很快跑完。

如果要杀死，首先向相关人员确认这个语句是否为关键进程。

杀掉方法：在PG数据库mydb服务器中，查询这个进程PID然后Kill掉。

> ps -ef|grep 17509

postgres 17509 4868 1 Nov18 ? 00:11:19 postgres: postgres mydb 192.168.165.18(56059) SELECT

postgres 30832 30800 0 15:18 pts/3 00:00:00 grep 17509

> ps -ef|grep 17509

postgres 17509 4868 1 Nov18 ? 00:11:19 postgres: postgres mydb 192.168.165.18(56059) SELECT

postgres 30838 30800 0 15:19 pts/3 00:00:00 grep 17509

> kill -9 17509

## 併發場景 - 防超賣、餘額不足或負數
#### 一般思維 select 餘額足夠後update,在併發情境下多個線程會同時通過select餘額檢核，造成餘額為不足或負數的情況產生

有多種解決方案，此處列出兩種參考:

#### 參考解法一、先update 並帶上扣款金額，然後query檢核，餘額不足則rollback並報error, 利用增刪改會lock的特性進行排隊

轉帳一千元的情境

beginTranse(开启事务)

try{
    // 修改餘額後查詢
    $update->query('update account set amount = amount - 1000 where amount>=1000 and USER_ID = 12345');
    if 其他意外情形{

        rollBack(回滚)
    }
}catch($e Exception){
    rollBack(回滚)
}

commit(提交事务)

#### 參考解法二、扣款流程使用select for update
驗證餘額是否足夠同時上鎖
餘額仍能查詢，但其他併發在驗證餘額時即會進入等待前一個交易完成並釋放鎖。

##### 補充
對於秒殺級商品，肯定不能全靠數據庫支撐，要另外設計緩存機制。 例如十件商品搶購，緩存設計100個計數，後面的全擋掉，只讓一百人進入頁面或搶購，降低db request最多到100併發

## 其他參考資料

psql 官方文檔 - 併發: http://www.postgres.cn/docs/14/mvcc.html
psql vs mysql: https://dbaplus.cn/news-11-3235-1.html

關於序列化的特別現象：
---
當A 是序列化，B是RC： A select表 -> B 插入新資料 + commit -> A 插入一筆全表合計 commit
結果是 A的合計資料不包含B新增 : 可以理解，因為A的視圖建立在首次query

當A,B都是 序列化： A select -> B select -> A insert 一筆全表合計 -> B insert 一筆全表合計 + commit -> A commit
結果是 A 報錯誤 `錯誤：由於事務之間的讀/寫依賴性，無法序列化訪問
詳細信息：原因代碼：在提交嘗試期間，在標識為樞軸時被取消。
提示：如果重試，事務可能會成功。`

為何如此

同時存在不同隔離等級的會話，讓結果變得更複雜了

可能是不同會話，但是對同一張表聲的視圖會覆蓋的，這樣就可以解釋這種狀態，而當兩個s產生不同視圖並影響彼此時，psql的謂詞鎖檢測到以後便報error； 當一個會話不是s,謂詞鎖不會檢測到被影響，因此可以產生忽略其他會話交易的結果