pgsql事務與併發控制

lonecloud發表於2018-11-19
SQL

事務與併發控制

  1. 事務的基本的概念和性質

ACID:

  • 原子性：一個事務要麼全部執行，要麼全部不執行

  • 一致性：執行事務的時候，資料庫從一個一致的狀態變更到另一個狀態

  • 隔離性： 確保在併發執行的時候，每個事務感覺不到其他事務在併發的執行

  • 永續性：一個事務完成之後，即使資料庫發生故障，他對資料庫的改變應該永久的儲存在資料庫中。

  1. 併發引起的現象

    • 髒讀：一個事務讀取了第二個事務的已經修改但是未提交的資料

    • 不可重複讀：一個事務第一次讀取資料之後，被讀取的資料被另一個已提交的事務進行了修改，事務再次讀取這些資料時候發現資料已經被另外一個事務所修改，兩次查詢不一致

    • 幻讀：一個事務的兩次結果集記錄數不一致(特殊的不可重複讀)

  2. 事務隔離級別

Pgsql 中預設的為讀已提交

  • 讀未提交：所有事務可以看到其他未提交的事務的結果

  • 讀已提交：一個事務只能看到已經提交事務對關聯資料的影響

  • 可重複讀：確保同一個事務的多個例項在併發讀取資料時候，會看到同樣的資料行

  • 可序列化：強制進行事務排序，使得其互不衝突

  1. 檢視資料庫的隔離級別操作

  • 檢視全域性事務級別

    select name,setting from pg_settings where name = 'default_transaction_isolation'

  • 修改全域性事務隔離級別

    alter system set default_transaction_islation to 'REPEATABLE READ';
    select pg_reload_conf();
    select current_setting('transaction_isolation');

  • 檢視當前會話的事務隔離級別

    show transaction_isolcation;

  • 設定當前事務的事務隔離級別

    start transaction isolation level READ UNCOMMITED;
    start TRANSACTION
        select xxxx
    END

併發控制

  1. 基於鎖的併發控制

    1. 排他鎖: 被加鎖的物件只能被持有鎖的事務的讀取和修改，其他事務無法在改物件上加其他鎖，也不能讀取和修改物件

    2. 共享鎖：被加鎖物件可以被鎖事務讀取，但是不能修改，其他事務可以在改物件上再加共享鎖

    3. 諮詢鎖(pg特有)：

  2. 基於多版本的併發控制

    pgsql預設會加入以下幾個欄位

    select xmin,xmax,cmin,cmax from test

    1. 以上四個欄位儲存了建立改行資料的事務xid，xmax儲存的是刪除改行資料的xid

    • xmin、xmax:與事務對其他事務可見性相關，用於同一個事務可見性判斷

    • cmin、cmax:分別為用於插入和刪除該原組的時候在十五中的命令序列標識

  3. 判斷事務可見性

    • 1 如果xmax沒有值，改行對其他事務是可見的

    • 如果他被設定為回滾事務xid，則改行對其他事務也是可見的

    • 如果被設定為一個正在執行時，沒有commit和rollback的事務的xid,則改行對其他事務是可見的

    • 如果他被設定為一個已提交的事務的xid，該行在這個已提交的事務之後發起的所有事務都是不可見的

  4. 使用pg_repack解決表膨脹問題

    由於pg採用MVCC讀不阻塞寫，寫不阻塞讀，實現高效能和搞吞吐量。。但是也有不足的地方。如果對錶進行大量的資料更新，則會導致其有兩個版本存在，一個為更新前，一個為更新後，導致其效能有影響。

    pg_repack是一個線上重建表和索引擴充套件。會在資料庫中建立一個和需要清理的目標表一樣的臨時表，將目標表中的資料複製到臨時表。

安裝：

yum install -y pg_repack10

建立擴充套件

create extension pg_repack

重建表

pg_repack -t table_name -j 2 -D -k -h host -u postgres -d dbname

 

相關文章