Thread: use pgsql in a big project, but i found pg has some big problem on concurrency write operation, maybe a joke for myself !

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.

2010/2/2  <wyx6fox@sina.com>:
> UPDATE webpages SET hits = hits + 1 WHERE url ='some url ';
>
>  when concurrency write transaction on read committed isolation , the hits
> may result wrong .

That should work fine. All updates for the same url will be serialized.


The rest I'm pretty uncertain about what you're describing but I think
you may want to check about whether you need indexes on the other side
of your foreign key constraints. If you're deleting records that are
referred to by your foreign keys or you're updating the primary key
then you'll want this index on the table with the foreign key
constraint as well as the mandatory one on the referenced table.

--
greg