hi, first, thanks u for make so good opensource db .
recently maybe half an years ago ,i begin to use pg in a big project for
insurance project, belong as the project go on ,and
i found some performance problem on concurrency write situation , then i do a
research on concurrency write strategy on postgresql ,
i found a joke ,maybe this joke concurrency strategy is the designer's pround
idea, but i think it is a joke , next let me describe the problems:
* joke 1: insert operation would use a excluse lock on reference row
by the foreign key . a big big big performance killer , i think this is a
stupid design .
* joke 2: concurrency update on same row would lead to that other transaction
must wait the earlier transaction complete , this would kill the
concurrency performance in some long time transaction situation . a
stupid design to ,
this joke design's reason is avoid confliction on read
committed isolation , such as this situation:
UPDATE webpages SET hits = hits + 1 WHERE url ='some url ';
when concurrency write transaction on read committed isolation , the hits
may result wrong .
this joke design would do seriable write , but i think any stupid
developer would not write this code like this stupid sample code , a good
code is
use a exclusive lock to do a seriable write on this same row , but the joker
think he should help us to do this , i say ,u should no kill concurrency
performance and help i do this fucking stupid sample code , i would use
a select .. for update to do this :
select 1 from lock_table where lockId='lock1' for update ;
UPDATE webpages SET hits = hits + 1 WHERE url ='some url ';
* joke 3: update 100000 rows on a table no any index , it cost 5-8 seconds ,
this is not acceptable in some bulk update situation .
at last, sorry about my angry taste.
