digoal
2019-01-18
PostgreSQL , rc , rr , ssi , 隔离级别 , 性能 , 乐观锁
https://www.postgresql.org/docs/11/mvcc.html
PG支持到了最高级别的隔离级别SSI（serializable snapshot isolate）。
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;
a
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
a
postgres=# begin transaction isolation level serializable ;
BEGIN
SELECT SUM(value) FROM mytab WHERE class = 1;
b
postgres=# begin transaction isolation level serializable ;
BEGIN
postgres=# SELECT SUM(value) FROM mytab WHERE class = 2;
sum
-----
300
(1 row)
a
insert into mytab values (2,30);
b
insert into mytab values (1,30);
a
postgres=# commit ;
COMMIT
b
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
《PostgreSQL 10.0 preview 功能增强 - 串行隔离级别 预加锁阈值可控》
《PostgreSQL SERIALIZABLE ISOLATION LEVEL introduce》
隔离级别越高，冲突可能性就越大，不同数据库的实现不一样，PG使用了巧妙的乐观锁实现。处理吞吐高。
为了方便测试，使用函数封装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
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)
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，非常厉害。
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，非常厉害。
| 数据量 | 隔离级别 | TPS(吞吐能力) | QPS | 正常事务 | 冲突比例 |
|---|---|---|---|---|---|
| 5亿 | rc | 94528 | 472640 | 11343739 | 0% |
| 5亿 | rr | 87957 | 439785 | 638001 | 93.95% |
| 5亿 | ssi | 43058 | 215290 | 411820 | 92.03% |
在冲突率高达90%以上的情况下，PG的RR模式可以保持87957的TPS，SSI模式可以保持43058的TPS，非常厉害，使用乐观锁带来的好处多多。
测试环境：32核虚拟机。(文章主要表明的是PG在高隔离级别下面的锁管理能力强悍，相对于RC这种低隔离级别，损耗做到了非常小。)
https://www.postgresql.org/docs/11/mvcc.html
https://www.postgresql.org/docs/11/errcodes-appendix.html
《PostgreSQL 10.0 preview 功能增强 - 串行隔离级别 预加锁阈值可控》
《PostgreSQL SERIALIZABLE ISOLATION LEVEL introduce》
https://www.jianshu.com/p/c348f68fecde
https://zhuanlan.zhihu.com/p/37087894
src/backend/storage/lmgr/README-SSI
https://www.postgresql.org/docs/11/runtime-config-locks.html
postgres=# 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
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
