标簽
PostgreSQL , rc , rr , ssi , 隔離級别 , 性能 , 樂觀鎖
https://github.com/digoal/blog/blob/master/201901/20190118_02.md#%E8%83%8C%E6%99%AF 背景
https://www.postgresql.org/docs/11/mvcc.htmlPG支援到了最進階别的隔離級别SSI（serializable snapshot isolate）。
https://github.com/digoal/blog/blob/master/201901/20190118_02.md#%E5%87%A0%E7%A7%8D%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E7%9A%84%E7%9B%AE%E6%A0%87%E5%A6%82%E4%B8%8B 幾種隔離級别的目标如下
1、讀未送出，可以看到未送出的資料，髒讀。
2、讀已送出，隻能看到其他會話已送出的資料。
3、可重複度，隻能看到事務開啟前已送出的資料。更新（修改）事務開啟後被其他會話修改過并送出的資料時，報錯。
postgres=# create table abc(id int primary key, info text);
CREATE TABLE
postgres=# insert into abc values (1,'test');
INSERT 0 1
a
postgres=# begin transaction isolation level repeatable read ;
BEGIN
postgres=# select * from abc;
id | info
----+------
1 | test
(1 row)
b
postgres=# update abc set info='hello digoal';
UPDATE 1
commit;
postgres=# \set VERBOSITY verbose
postgres=# update abc set info ='new';
ERROR: 40001: could not serialize access due to concurrent update
LOCATION: ExecUpdate, nodeModifyTable.c:1257
4、嚴格串行，模拟串行執行，事務結束時，判斷是否有會話之間的互相依賴，如果依賴的資料被已送出的事務修改，則報錯，確定模拟嚴格串行的場景。
create table mytab (class int, value int);
insert into mytab values (1,10),(1,20),(2,100),(2,200);
class | value
-------+-------
1 | 10
1 | 20
2 | 100
2 | 200
postgres=# begin transaction isolation level serializable ;
BEGIN
SELECT SUM(value) FROM mytab WHERE class = 1;
postgres=# begin transaction isolation level serializable ;
BEGIN
postgres=# SELECT SUM(value) FROM mytab WHERE class = 2;
sum
-----
300
(1 row)
insert into mytab values (2,30);
insert into mytab values (1,30);
postgres=# commit ;
COMMIT
postgres=# \set VERBOSITY verbose
postgres=# commit;
ERROR: 40001: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.
LOCATION: PreCommit_CheckForSerializationFailure, predicate.c:4685
隔離級别越高，沖突可能性就越大，不同資料庫的實作不一樣，PG使用了巧妙的樂觀鎖實作。處理吞吐高。
https://github.com/digoal/blog/blob/master/201901/20190118_02.md#%E5%AF%B9%E6%AF%94rc-rr-ssi%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E7%9A%84%E6%80%A7%E8%83%BD 對比rc, rr, ssi隔離級别的性能
為了友善測試，使用函數封裝TPCB，測試rr和ssi
create or replace function tpcb (int,int,int,int) returns void as $$
declare
begin
UPDATE pgbench_accounts SET abalance = abalance + $4 WHERE aid = $1;
perform abalance FROM pgbench_accounts WHERE aid = $1;
UPDATE pgbench_tellers SET tbalance = tbalance + $4 WHERE tid = $3;
UPDATE pgbench_branches SET bbalance = bbalance + $4 WHERE bid = $2;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($3, $2, $1, $4, CURRENT_TIMESTAMP);
exception when SQLSTATE '40001' then -- skip 沖突
return;
end;
$$ language plpgsql strict;
測試腳本
vi tpcb.sql
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
select tpcb(:aid, :bid, :tid, :delta);
初始化資料，5億條
pgbench -i -s 5000
https://github.com/digoal/blog/blob/master/201901/20190118_02.md#rc%E6%A8%A1%E5%BC%8F rc模式
alter role all set default_transaction_isolation ='read committed';
pgbench -M prepared -v -r -P 1 -c 64 -j 64 -T 120
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 5000
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 11343739
latency average = 0.677 ms
latency stddev = 0.747 ms
tps = 94519.862919 (including connections establishing)
tps = 94528.706372 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.046 BEGIN;
0.137 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.073 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.082 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.079 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.067 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.188 END;
postgres=# select count(*) from pgbench_history ;
count
----------
11343739
(1 row)
https://github.com/digoal/blog/blob/master/201901/20190118_02.md#rr%E6%A8%A1%E5%BC%8F rr模式
alter role all set default_transaction_isolation ='repeatable read';
pgbench -M prepared -v -r -P 1 -f ./tpcb.sql -c 64 -j 64 -T 120
transaction type: ./tpcb.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 10555148
latency average = 0.728 ms
latency stddev = 0.732 ms
tps = 87949.593197 (including connections establishing)
tps = 87957.185597 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.725 select tpcb(:aid, :bid, :tid, :delta);
等待事件
select wait_event_type,wait_event,count(*) from pg_stat_activity group by 1,2 order by 1,2;
wait_event_type | wait_event | count
-----------------+----------------------+-------
Activity | AutoVacuumMain | 1
Activity | BgWriterMain | 1
Activity | CheckpointerMain | 1
Activity | LogicalLauncherMain | 1
Client | ClientRead | 4
IPC | ProcArrayGroupUpdate | 1
LWLock | buffer_content | 5
Lock | transactionid | 6
Lock | tuple | 34
| | 17
(10 rows)
正常事務：
postgres=# select count(*) from pgbench_history ;
count
--------
638001
(1 row)
在沖突率這麼高的情況下，可以保持87957的TPS，非常厲害。
https://github.com/digoal/blog/blob/master/201901/20190118_02.md#ssi%E6%A8%A1%E5%BC%8F ssi模式
alter role all set default_transaction_isolation ='serializable';
pgbench -M prepared -v -r -P 1 -f ./tpcb.sql -c 64 -j 64 -T 120
transaction type: ./tpcb.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 5166549
latency average = 1.486 ms
latency stddev = 1.098 ms
tps = 43050.086666 (including connections establishing)
tps = 43058.594768 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
1.484 select tpcb(:aid, :bid, :tid, :delta);
鎖等待
select wait_event_type,wait_event,count(*) from pg_stat_activity group by 1,2 order by 1,2;
wait_event_type | wait_event | count
-----------------+------------------------------+-------
Activity | AutoVacuumMain | 1
Activity | BgWriterHibernate | 1
Activity | CheckpointerMain | 1
Activity | LogicalLauncherMain | 1
Activity | WalWriterMain | 1
Client | ClientRead | 1
LWLock | SerializableFinishedListLock | 58
LWLock | SerializableXactHashLock | 2
Lock | transactionid | 1
Lock | tuple | 1
| | 3
(11 rows)
postgres=# select count(*) from pgbench_history ;
count
--------
411820
(1 row)
在沖突率這麼高的情況下，可以保持43058的TPS，非常厲害。
https://github.com/digoal/blog/blob/master/201901/20190118_02.md#%E6%80%A7%E8%83%BD%E5%AF%B9%E6%AF%94 性能對比
| 資料量 | 隔離級别 | TPS(吞吐能力) | QPS | 正常事務 | 沖突比例 |
|---|---|---|---|---|---|
| 5億 | rc | 94528 | 472640 | 11343739 | 0% |
| rr | 87957 | 439785 | 638001 | 93.95% | |
| ssi | 43058 | 215290 | 411820 | 92.03% |
https://github.com/digoal/blog/blob/master/201901/20190118_02.md#%E5%B0%8F%E7%BB%93 小結
在沖突率高達90%以上的情況下，PG的RR模式可以保持87957的TPS，SSI模式可以保持43058的TPS，非常厲害，使用樂觀鎖帶來的好處多多。
測試環境：32核虛拟機。(文章主要表明的是PG在高隔離級别下面的鎖管理能力強悍，相對于RC這種低隔離級别，損耗做到了非常小。)
https://github.com/digoal/blog/blob/master/201901/20190118_02.md#%E5%8F%82%E8%80%83 參考
https://www.postgresql.org/docs/11/errcodes-appendix.html https://www.jianshu.com/p/c348f68fecde https://zhuanlan.zhihu.com/p/37087894src/backend/storage/lmgr/README-SSI
https://www.postgresql.org/docs/11/runtime-config-locks.htmlpostgres=# show max_pred_locks_per_page ;
max_pred_locks_per_page
-------------------------
2
(1 row)
postgres=# show max_pred_locks_per_relation ;
max_pred_locks_per_relation
-----------------------------
-2
(1 row)
postgres=# show max_pred_locks_per_transaction ;
max_pred_locks_per_transaction
--------------------------------
64
(1 row)
References
----------
[1] http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Search for serial execution to find the relevant section.
[2] A. Fekete et al. Making Snapshot Isolation Serializable. In ACM
Transactions on Database Systems 30:2, Jun. 2005.
http://dx.doi.org/10.1145/1071610.1071615
[3] Joseph M. Hellerstein, Michael Stonebraker and James Hamilton. 2007.
Architecture of a Database System. Foundations and Trends(R) in
Databases Vol. 1, No. 2 (2007) 141-259.
http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf
Of particular interest:
* 6.1 A Note on ACID
* 6.2 A Brief Review of Serializability
* 6.3 Locking and Latching
* 6.3.1 Transaction Isolation Levels
* 6.5.3 Next-Key Locking: Physical Surrogates for Logical Properties
https://github.com/digoal/blog/blob/master/201901/20190118_02.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)